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

Last updated on SEPTEMBER 15, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.0 to 10.2.0.3
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 mo_ext_fd_payer
 as
 SELECT party.party_id party_id,
 xle_firstparty.legal_entity_id legal_entity_id,
 xmlelement("PartyInternalID", party.party_id) payer
 FROM XLE_ENTITY_PROFILES xle_firstparty,
 hz_parties party
 WHERE xle_firstparty.party_id = party.party_id
 .

 Case #1 when push predicate does not happen:

 SELECT
 ins.payment_instruction_id
 FROM
 mo_ext_fd_payer xml_payer
 ,iby_pay_instructions_all 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 IBY_PAY_INSTRUCTIONS_ALL Cost=1, Rows=1
 INDEX UNIQUE SCAN IBY_PAY_INSTRUCTIONS_ALL_IDX1 Cost=0, Rows=1
 VIEW MO_EXT_FD_PAYER 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 party.party_id party_id,
 xle_firstparty.legal_entity_id legal_entity_id,
 xmlelement("PartyInternalID", party.party_id) payer
 FROM XLE_ENTITY_PROFILES xle_firstparty,
 hz_parties party
 WHERE xle_firstparty.party_id = party.party_id
 ) xml_payer
 ,iby_pay_instructions_all 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 IBY_PAY_INSTRUCTIONS_ALL Cost=1, Rows=1
 INDEX UNIQUE SCAN IBY_PAY_INSTRUCTIONS_ALL_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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms