Skip to Main Content
  • Questions
  • pushing predicate into union-all view

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, giedrius.

Asked: November 14, 2018 - 11:50 am UTC

Last updated: November 16, 2018 - 4:19 pm UTC

Version: 12.2.0.1.0 - 64bit Production

Viewed 1000+ times

You Asked

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.

and Chris said...

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.


Is this answer out of date? If it is, please let us know via a Comment

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.