Michael,
Your select query has no where clause and the select clause includes
columns not in the index, therefore Oracle must go to the table... every
row in the table. Are you sure your dummy query is exactly the same type
of syntax as the real one? It would be assumed that a full table scan is
faster than many single row reads so Oracle would choose a FTS. How do you
know it's faster to use the index? Have you tested this factoring in
things such as caching, etc.
If you insist on using the index... Is the table and index analyzed? Have
you compared the cost of the two explain plans? What row size are we
dealing with? What row count? It might be easier if we knew the real
table, the real query and some record counts.
Having said that, you could be cheeky and drop the order by clause if you
force it to use the index. The index will effectively sort the results,
saving the sort operation totally. I don't recommend this since it's not
the "right way" to sort data and it's not guaranteed to work (since hints
can be ignored) but I've seen it used to great effect when an ordered
subset is required - it's like saying "scan the index and stop after x
rows" instead of "scan all rows, order then and then give me the first
ten". But, that's not what you asked so I'll shut up now.
Regards,
Mark.
"Johnson, Michael "
<Michael.Johnson@oln-af To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>
mc.af.mil> cc:
Sent by: Subject: CBO not using the index
even though it is faster when hinted
[EMAIL PROTECTED] ....
24/01/2003 13:28
Please respond to
ORACLE-L
Anyone have any problems with the CBO not using
a index when you know it is faster by forcing a
hint ?
I have set the following ...
Solaris
Oracle Version 8.1.7.4
block size = 8
DB_FILE_MULTIBLOCK_READ_COUNT = 8
mode = Choose
also using Tim Gormans 90 and 50 values for the other optimizer parms.
Select col1, col2, col3, blah1, blah2 from table order by col1, col2, col3;
Concatenate index on col1, col2, col3.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johnson, Michael
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).
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Privileged/Confidential information may be contained in this message.
If you are not the addressee indicated in this message
(or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
by reply e-mail or by telephone on (61 3) 9612-6999.
Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
that do not relate to the official business of
Transurban City Link Ltd
shall be understood as neither given nor endorsed by it.
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mark Richard
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).
