469,347 Members | 1,874 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,347 developers. It's quick & easy.

Re: Oracle not using Index

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
Jun 27 '08 #1
1 2981

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

Jun 27 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

7 posts views Thread by vnl | last post: by
125 posts views Thread by Rhino | last post: by
8 posts views Thread by Alfonso Esteban Gonzalez Sencion | last post: by
56 posts views Thread by Ashish Patankar | last post: by
1 post views Thread by Server Applications | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.