Re: Oracle not using Index

ststellwag_at_aol.com (Steffen Stellwag) wrote in message news:<15d2966b.0307250250.1c4c98e_at_posting.google.com>...
> Truely is often better to scan a table in full passing by an index ,
> but if you can force the optimizer to use an index via a hint for
> testing and comparing the results.
>
> But the index in the above example is not used , because the hint is
> malformed,
> if tables in a Select statment are named by aliases you have to
> specify the alias name in the hint statment , not the table name
> /*+ INDEX (ICWOIMP PK_ICWOIMP) */ change to /*+ INDEX (A PK_ICWOIMP)
> */
>
>
>
>
> "Jim Kennedy" <kennedy-down_with_spammers_at_no_spam.comcast.net> wrote in message news:<AX%Ta.124377$sY2.56301_at_rwcrnsc51.ops.asp.att.net>...
> > A hint is a hint not a requirement. A full table scan might actually be
> > faster than using an index which is what the optimizer might be thinking.(eg
> > if the whole table or most of it is going to be retrieved then a full table
> > scan would be faster than using an index)
> > Jim
> >
> > "Mahesh Hardikar" <hardikarm_at_yahoo.com> wrote in message
> > news:4a1c57c2.0307240345.3c53f50b_at_posting.google.com...
> > > Hi ,
> > >
> > > Oracle 8.1.7.0.0 on HP-UX 11.0
> > >
> > > We have following query .
> > > /************
> > > SELECT
> > > 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
> > > ********/
> > >
> > > Execution Plan :
> > > -----------------------------------------------------
> > > SELECT STATEMENT Optimizer=CHOOSE (Cost=1178 Card=1 Bytes=12
> > > 1)
> > >
> > > 0 NESTED LOOPS (Cost=1178 Card=1 Bytes=121)
> > > 1 NESTED LOOPS (Cost=1175 Card=1 Bytes=109)
> > > 2 HASH JOIN (Cost=1174 Card=1 Bytes=81)
> > > 3 TABLE ACCESS (FULL) OF 'AFAS_RCPT_DTLS' (Cost=70 Car
> > > d=11603 Bytes=440914)
> > >
> > > 3 TABLE ACCESS (FULL) OF 'ICWOIMP' (Cost=830 Card=3733
> > > 9 Bytes=1605577)
> > >
> > > 2 TABLE ACCESS (BY INDEX ROWID) OF 'AFAS_RCPT_HDR' (Cost
> > > =1 Card=8343 Bytes=233604)
> > >
> > > 6 INDEX (UNIQUE SCAN) OF 'PK_AFAS_RCPT_HDR' (UNIQUE)
> > > 1 TABLE ACCESS (BY INDEX ROWID) OF 'ICADDDRESSDTLS' (Cost=
> > > 3 Card=12018 Bytes=144216)
> > >
> > > 8 INDEX (RANGE SCAN) OF 'INDX_ICADDRESSDTLS_WOKEY' (NON-
> > > UNIQUE) (Cost=2 Card=12018)
> > >
> > > 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
> > >
> > > 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.
> > >
> > > Regards,
> > > Mahesh Hardikar

Mahesh, First several Oracle experts are of the opinion that hints are directives to the CBO and that the CBO will always follow the hints if they are valid, and if it can. The join method chosen can prevent a hint from applying. In your case a hash join was chosen so the hint does not apply. Also little rules such (at least with version 7.0 - 7.3) as a USE_NL hint requires an ORDERED hint in order to take affect come into play. This fact was not in the official documentation manuals though it was documented in a support white paper.

Rewrite the query, if necessary, so that the tables in the from clause are in the desired order then add the ORDERED USE_NL hints to get a nested loop join. Your index will probably then be used. If not you can add an INDEX(label idx) hint, but also be aware that the CBO plan is sometimes better that what we think.

HTH -- Mark D Powell -- Received on Fri Jul 25 2003 - 15:28:08 CEST