I read a question which deals with this but it does not address my concerns.
P11_QUESTION_ID:1594885400346999596
Commonly we have a table which has columns which almost nobody uses.
We then put this table under a view
create or replace view original_table as
select
t.f1,
t.f2,
t.detail_id,
( select td.long_text from detail td where td.detail_id = t.detail_id ) long_text
from
original_tab t
In nearly all cases original_table.long_text is not used so it is for example
select
ot.f2
from
original_table ot
join next_table nt on
nt.f1 = ot.f1
...
The users could replace original_table with original_tab but that is not an option.
Our experience is that the presence of the long_text column in the view affects the plan for the (second) query above.
For which versions of Oracle is this the case?
Why does this occur?
The scalar subquery could be run up to once per row (subquery caching may reduce this).
But this should only happen if you access it!
For example (test run on 11.2.0.4):
create table t1 as
select * from dba_objects
where object_type = 'TABLE';
create table t2 as
select * from dba_tables;
create or replace view vw as
select object_name, owner, created, object_id,
(select last_analyzed from t2
where t2.table_name = t1.object_name
and t2.owner = t1.owner ) l
from t1;
set serveroutput off
alter session set statistics_level = all;
set feed only
select * from vw;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 2zhnk50cnq4c7, child number 1
-------------------------------------
select * from vw
Plan hash value: 3472008848
-------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1632 |
|* 1 | TABLE ACCESS FULL| T2 | 1632 | 1 | 1566 |
| 2 | TABLE ACCESS FULL| T1 | 1 | 1632 | 1632 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("T2"."TABLE_NAME"=:B1 AND "T2"."OWNER"=:B2))
Note
-----
- dynamic sampling used for this statement (level=2)
set feed only
select object_name, owner from vw;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID duqmnwhv9xzz2, child number 0
-------------------------------------
select object_name, owner from vw
Plan hash value: 3688435342
-------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1632 |
| 1 | TABLE ACCESS FULL| T1 | 1 | 1632 | 1632 |
-------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement (level=2)Notice how T2 doesn't appear in the plan for the second query?
If you think your query is running the subquery - even though you haven't accessed - then please share a test case demonstrating this. This test case should look like mine above.
i.e. include:
- create tables (+ inserts if needed)
- create view
- select
- execution plan generated using dbms_xplan as shown