My Oracle Support Banner

Push Predicate Optimization Not Happening For SQL with View (Doc ID 443362.1)

Last updated on APRIL 24, 2020

Applies to:

Oracle Database Cloud Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 10.2.0.3 [Release 10.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

Join predicates are not pushed down into certain views thus causing FULL SCANS
on the views' underlying tables. Join predicate pushing worked fine under 10.1,
however, does NOT work as it used to under 10.2.


If  a query that outer joins a table to a  view. Push predicate
optimization is not happening thus causing suboptimal execution.

If  you  replace the view name with the actual view text as an inline view
in the query then everything works fine.


View Defintion:
 create or replace view View1
 as
 SELECT table1.table1_id table1_id,
 xle_firsttable1.legal_entity_id legal_entity_id,
 xmlelement("table1InternalID", table1.table1_id) payer
 FROM XLE_ENTITY_PROFILES xle_firsttable1,
 hz_parties table1
 WHERE xle_firsttable1.table1_id = table1.table1_id
 .

 Case #1 when push predicate does not happen:

 SELECT
 ins.payment_instruction_id
 FROM
 View1 xml_payer
 ,table2 ins
 WHERE ins.legal_entity_id = xml_payer.legal_entity_id(+)
 and ins.PAYMENT_INSTRUCTION_ID =33220
 .
 QUERY_PLAN
 ------------------------------------------------------------------------------

 SELECT STATEMENT Cost=6, Rows=1
 NESTED LOOPS OUTER Cost=6, Rows=1
 TABLE ACCESS BY INDEX ROWID table2 Cost=1, Rows=1
 INDEX UNIQUE SCAN table2_IDX1 Cost=0, Rows=1
 VIEW View1 Cost=5, Rows=1
^^^^^^^^^^^^^^^^^^^^^^^^^^
 NESTED LOOPS Cost=5, Rows=286
 TABLE ACCESS FULL XLE_ENTITY_PROFILES Cost=5, Rows=286
 INDEX UNIQUE SCAN HZ_PARTIES_IDX1 Cost=0, Rows=1
 .
 .
 Case #2: Push predicate happens:

 SELECT
 ins.payment_instruction_id
 FROM
 (SELECT table1.table1_id table1_id,
 xle_firsttable1.legal_entity_id legal_entity_id,
 xmlelement("table1InternalID", table1.table1_id) payer
 FROM XLE_ENTITY_PROFILES xle_firsttable1,
 hz_parties table1
 WHERE xle_firsttable1.table1_id = table1.table1_id
 ) xml_payer
 ,table2 ins
 WHERE ins.legal_entity_id = xml_payer.legal_entity_id(+)
 and ins.PAYMENT_INSTRUCTION_ID =33220
 QUERY_PLAN
 ------------------------------------------------------------------------------

 SELECT STATEMENT Cost=6, Rows=1
 NESTED LOOPS OUTER Cost=6, Rows=1
 TABLE ACCESS BY INDEX ROWID TABLE2 Cost=1, Rows=1
 INDEX UNIQUE SCAN TABLE2_IDX1 Cost=0, Rows=1
 VIEW PUSHED PREDICATE Cost=5, Rows=1
^^^^^^^^^^^^^^^^^^^^^^^^^
 NESTED LOOPS Cost=5, Rows=1
 TABLE ACCESS FULL XLE_ENTITY_PROFILES Cost=5, Rows=1
 INDEX UNIQUE SCAN HZ_PARTIES_IDX1 Cost=0, Rows=1
 .

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.