Stored outline not being used -- UNNEST hint?

Anynone knows if Oracle stored outline does not work with UNNEST hint is used?  I have a SQL which is like this:

SELECT /*+ index (msi MTL_SYSTEM_ITEMS_B_U1) index (ohqmv MTL_ONHAND_QUANTITIES_N4) */ AU.USER_ID ,
....
AND EXISTS ( SELECT 1 FROM CSM_INV_LOC_ASS_ACC ILAACC1, CSP_INV_LOC_ASSIGNMENTS ILA1, CSP_SEC_INVENTORIES CSI WHERE ILAACC1.USER_ID = AU.USER_ID AND ILAACC1.CSP_INV_LOC_ASSIGNMENT_ID = ....

We changed it to this below and casted the stored ouline for it:

SELECT /*+ FIRST_ROWS */ AU.USER_ID ,
....
AND EXISTS ( SELECT /*+ UNNEST */ 1 FROM CSM_INV_LOC_ASS_ACC ILAACC1, CSP_INV_LOC_ASSIGNMENTS ILA1, CSP_SEC_INVENTORIES CSI WHERE ILAACC1.USER_ID = AU.USER_ID AND ILAACC1.CSP_INV_LOC_ASSIGNMENT_ID = ....

Somehow this particular stored outline is not used by Oracle.  We are on 9206 version.  I think I read somewhere before that certain Hints would invalidate that stored oulines and may be UNNEST is one of those hints.

Thanks for any help.


 



Experience the magic of the holidays. Talk to Santa on Messenger. -- http://www.freelists.org/webpage/oracle-l

Other related posts: