Re: index not being used

Hi!

> This plan shows that ICWOIMP is accessed FULL .
> Actually this table
> has a Primary Key on WOKEY & this is used in JOIN
> condition . WHy is
> it not using that index

But you also have two joins between ICWOIMP (A) and AFAS_RCPT_DTLS (D) tables in your query: CBO finds that it's better to hash join those two, and it's better to use multiblock reads from table to get the data for join either because of cost or because you do not have indexes / other predicates on those columns.

And since you already have read whole ICWOIMP table due CBO decided join order, there's no point of reading any more data from it, thus no index is required and the hint is ignored. Instead the hash joined result set is taken and is joined to rest of tables using nested loops.

You have to hint join orders if you definitely want to get join using WOKEY happen first.

Tanel.

>
> I tried to force this index
>
> SELECT /*+ INDEX (ICWOIMP PK_ICWOIMP) */
> A.CANNO "INVOICE NO",
> A.CANDATE "INVOICE DATE",
> --B.NAME "CUSTOMER NAME",
> A.CANAMT "REVENUE AMOUNT",
> C.RCPTDOCNO "RECEIPT NO.",
> D.RECEIPTAMOUNT "RECEIPT AMOUNT",
> C.RCPTDATE "RECEIPT DATE"
> FROM
> ICWOIMP A,
> ICADDDRESSDTLS B,
> AFAS_RCPT_HDR C,
> AFAS_RCPT_DTLS D
> WHERE
> A.WOKEY = B.WOKEY
> AND D.RECEIPTHDRID = C.RECEIPTHDRID
> AND ADDTYPE ='SHPR'
> --AFAS_RCPT_DTLS.RECEIPTHDRID =
> AFAS_RCPT_HDR.RECEIPTHDRID
> AND D.DOCLINKREFNUM = A.CANNO
> AND D.DOCLINKNUM = A.WONO
>
> But still with this , execution plan remained the
> same.
> AM I missing something ? Can Oracle ignore the hint
> although provided
> ?
>
> P.S. Statistics are Up-To-Date for all tables.
>
> can anybody tell me why my hinet is being ignored
>
> ________________________________________________________________________
> Want to chat instantly with your online friends? Get the FREE Yahoo!
> Messenger http://uk.messenger.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: =?iso-8859-1?q?hrishy?=
> INET: hrishys_at_yahoo.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Fri Jul 25 2003 - 03:33:43 CDT