Re: CBO not using the index ... Here is what I did

Michael,

Somewhere along the line, I mis-interpreted your note and thought you were saying that CBO was ignoring the hint - hence the irrelevant comments.

RBO uses the index for this query because that's one of the rules.

The CBO under ALL_ROWS optimisation is going to compare figures (in your case) like:

    leaf_blocks * optimizer_index_caching +     clustering_factor * optimizer_index_cost_adj.

and

    table_blocks / 6.5 +
    cost of sort

Since clustering_factor bottoms out at table_blocks, your optimizer_index_cost_adj would probably have to be less than 15 before you had much chance of using the index.

You state elsewhere that the timing difference is fractions of a second against seven seconds for "thousands of rows". I wonder if this is the giveaway - I would be a little surprised if your client can actually acquire thousands of rows in a fraction of a second, although it might acquire the first few in that time. If this is the case, you might like to find out how long it really takes to acquire all the rows using the index - this might make the scan and sort path seem more reasonable..

If you switch the session's optimizer_mode to FIRST_ROWS, you will find that the optimizer will use the index - there is a heuristic hard-coded in to FIRST_ROWS (introduce in 8.1.6 I think) that always uses an index to avoid a sort. It is possible the FIRST_ROWS is actually the nominally correct optimizer mode for this bit of the application.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March
____USA_(FL)_May

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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