Re: Oracle not using Index

[Steffen Stellwag]

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_spammers@no_spam.comcast.netwrote in message news:<AX********************@rwcrnsc51.ops.asp.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" <ha*******@yahoo.comwrote in message
news:4a**************************@posting.google.c om...

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

[Jim Kennedy]

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" <st********@aol.comwrote in message
news:15*************************@posting.google.co m...

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_spammers@no_spam.comcast.netwrote in

message news:<AX********************@rwcrnsc51.ops.asp.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" <ha*******@yahoo.comwrote in message
news:4a**************************@posting.google.c om...

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