index not being used

From: hrishy <hrishys_at_yahoo.co.uk>
Date: Fri, 25 Jul 2003 06:32:14 +0100 (BST)
Message-Id: <26007.339513@fatcity.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.

can anybody tell me why my hinet is being ignored

---

Want to chat instantly with your online friends? Get the FREE Yahoo! Received on Fri Jul 25 2003 - 00:32:14 CDT