| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: index not being used
Hi Hrishy
Just try with /*+INDEX (ICWOIMP,PK_ICWOIMP) */
^^ ^^
No space Comma
I am not sure.
hrishy writes:
> 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: hrishys_at_yahoo.co.uk
>
> 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: ListGuru_at_fatcity.com (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).
>
>
Saminathan Seerangan
 |
 |