Re: Oracle not using Index

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 - 03:44:00 CEST