Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Stored outline not being used -- UNNEST hint?

Stored outline not being used -- UNNEST hint?

From: Binh Pham <binhpham15_at_hotmail.com>
Date: Tue, 02 Jan 2007 18:31:38 +0000
Message-ID: <BAY103-F2D912D015EB9E996834E2D2BA0@phx.gbl>

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 Received on Tue Jan 02 2007 - 12:31:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US