Re: Oracle not using Index

**Jim Kennedy** · Jun 27 '08, 04:59 PM

Re: Oracle not using Index

No, it is not always better to do a full table scan via an index vs just
doing it. If I do it via the index then I have to scan 2 things (the index
and the table) and that is more IO than just scanning 1 thing(the table).
Jim
--
"Steffen Stellwag" <ststellwag@aol .comwrote in message
news:15d2966b.0 307250250.1c4c9 8e@posting.goog le.com...

Truely is often better to scan a table in full passing by an index ,
but if you can force the optimizer to use an index via a hint for
testing and comparing the results.
>
But the index in the above example is not used , because the hint is
malformed,
if tables in a Select statment are named by aliases you have to
specify the alias name in the hint statment , not the table name
/*+ INDEX (ICWOIMP PK_ICWOIMP) */ change to /*+ INDEX (A PK_ICWOIMP)
*/
>
>
>
>
"Jim Kennedy" <kennedy-down_with_spamm ers@no_spam.com cast.netwrote in

message news:<AX%Ta.124 377$sY2.56301@r wcrnsc51.ops.as p.att.net>...

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@yaho o.comwrote in message
news:4a1c57c2.0 307240345.3c53f 50b@posting.goo gle.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.R ECEIPTHDRID
AND D.DOCLINKREFNUM = A.CANNO
AND D.DOCLINKNUM = A.WONO
********/
>
Execution Plan :
-----------------------------------------------------
SELECT STATEMENT Optimizer=CHOOS E (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_H DR' (UNIQUE)
1 TABLE ACCESS (BY INDEX ROWID) OF 'ICADDDRESSDTLS ' (Cost=
3 Card=12018 Bytes=144216)
>
8 INDEX (RANGE SCAN) OF 'INDX_ICADDRESS DTLS_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.R ECEIPTHDRID
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

Re: Oracle not using Index

Re: Oracle not using Index

Comment