Re: Oracle not using Index

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
Received on Fri Jul 25 2003 - 12:50:22 CEST