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!
Messenger http://uk.messenger.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?hrishy?=
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
