Re: CBO not using the index even though it is faster when hinted ....

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_at_oln-af       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    mc.af.mil>                    cc:                                                                       
                    Sent by:                      Subject:     CBO not using the index even though it is faster when hinted 
                    root_at_fatcity.com               ....                                                                     
                                                                                                                            
                                                                                                                            
                    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: Michael.Johnson_at_oln-afmc.af.mil

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).






<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---->>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

   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: mrichard_at_transurban.com.au

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).