Hi,
LiveSQL link:
https://livesql.oracle.com/apex/livesql/s/hjml6z0yg45qznob5sebg53vk I have the big table with an index on ID:
create table tst1 as select level id, mod(level, 10) code from dual connect by level < 1000000;
create index tst1_id_ix on tst1(id);
begin
dbms_stats.gather_table_stats(user, 'tst1');
end;
/
And create a union all view on it:
create or replace view tst1_vw as
select
id,
code,
'5' code_ex
from
tst1
where
code = '5'
union all
select
id,
code,
'6'
from
tst1
where
code = '6'
Then I have some queries against the view:
Query1:
select /*+gather_plan_statistics */ * from tst1_vw where id = 600;
EXPLAINED SQL STATEMENT:
------------------------
select /*+gather_plan_statistics */ * from tst1_vw where id = 600
Plan hash value: 3078038340
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | | 8 (100)| 0 |
| 1 | VIEW | TST1_VW | 4 | 2 | 8 (0)| 0 |
| 2 | UNION-ALL | | 4 | | | 0 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| TST1 | 4 | 1 | 4 (0)| 0 |
|* 4 | INDEX RANGE SCAN | TST1_ID_IX | 4 | 1 | 3 (0)| 4 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TST1 | 4 | 1 | 4 (0)| 0 |
|* 6 | INDEX RANGE SCAN | TST1_ID_IX | 4 | 1 | 3 (0)| 4 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("CODE"=5)
4 - access("ID"=600)
5 - filter("CODE"=6)
6 - access("ID"=600)
Query 2:
select /*+gather_plan_statistics */ * from tst1_vw where id = (select id from tst1 where id = 600 )
EXPLAINED SQL STATEMENT:
------------------------
select /*+gather_plan_statistics */ * from tst1_vw where id = (select
id from tst1 where id = 600 )
Plan hash value: 3422447864
----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | | 1016 (100)| 0 |
|* 1 | VIEW | TST1_VW | 3 | 200K| 1013 (3)| 0 |
| 2 | UNION-ALL | | 3 | | | 600K|
|* 3 | TABLE ACCESS FULL| TST1 | 3 | 100K| 506 (3)| 300K|
|* 4 | TABLE ACCESS FULL| TST1 | 3 | 100K| 506 (3)| 300K|
|* 5 | INDEX RANGE SCAN | TST1_ID_IX | 3 | 1 | 3 (0)| 3 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=)
3 - filter("CODE"=5)
4 - filter("CODE"=6)
5 - access("ID"=600)
Query 3:
select /*+gather_plan_statistics */ * from tst1_vw t1 join (select id from tst1 where id = 600 ) t2 on t1.id = t2.id
EXPLAINED SQL STATEMENT:
------------------------
select /*+gather_plan_statistics */ * from tst1_vw t1 join (select id
from tst1 where id = 600 ) t2 on t1.id = t2.id
Plan hash value: 1785083774
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | | 11 (100)| 0 |
|* 1 | HASH JOIN | | 2 | 2 | 11 (0)| 0 |
|* 2 | INDEX RANGE SCAN | TST1_ID_IX | 2 | 1 | 3 (0)| 2 |
| 3 | VIEW | TST1_VW | 2 | 2 | 8 (0)| 0 |
| 4 | UNION-ALL | | 2 | | | 0 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TST1 | 2 | 1 | 4 (0)| 0 |
|* 6 | INDEX RANGE SCAN | TST1_ID_IX | 2 | 1 | 3 (0)| 2 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| TST1 | 2 | 1 | 4 (0)| 0 |
|* 8 | INDEX RANGE SCAN | TST1_ID_IX | 2 | 1 | 3 (0)| 2 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="ID")
2 - access("ID"=600)
5 - filter("CODE"=5)
6 - access("ID"=600)
7 - filter("CODE"=6)
8 - access("ID"=600)
Observations:Query 1 and 3 push predicate into a view and cost of a plan is low.
Query 2 seems to not push predicate into a view and cost of a plan is very big.
Question: Is it possible to make query 2 to push predicate into a view without using hints? If yes, then how.
Thank you.
No. The optimizer doesn't currently consider pushing predicates into the view for query 2. You can see this in the 10053 trace:
Check Basic Validity for Non-Union View for query block SET$1 (#3)
JPPD: JPPD bypassed: View is a set query block.
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=12080, alloc=32712), compile(in-use=181304, alloc=197888), execution(in-use=6992, alloc=8088)
kkqctdrvTD-end:
call(in-use=12080, alloc=32712), compile(in-use=181944, alloc=197888), execution(in-use=6992, alloc=8088)
JPPD: Applying transformation directives
JPPD: Checking validity of push-down in query block SEL$1 (#1)
JPPD: No valid views found to push predicate into.