Oracle not using Index

From: Mahesh Hardikar <hardikarm_at_yahoo.com>
Date: 24 Jul 2003 12:39:53 -0700
Message-ID: <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 Thu Jul 24 2003 - 21:39:53 CEST