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.
