Re: All bitmap indexes not being used in a query where they should be

Ignore the num_rows and clustering factor, though you could try making the leaf-blocks very large.

The other thing is to use the set_column_stats to change the DISTINCT and DENSITY
values on user_tab_columns - (set density = 1/num_distinct).

The easiest thing to do is a little pl/sql does:

declare

    list of outputs for GET_column_stats; begin

    dbms_stats.get_column_stats;
    change num_distinct_var, density_var     dbms_stats.set_column_stats;
end;
/

Then you should see the change.

The cost will be roughly:

    blevel + leaf_blocks/num_distinct + 1 summed across all the indexes selected.

I've had a look at the trace - the query which is the list of columns is behaving predictably. The one which is the count(*) is completely bizarre - it gives a cost of 27, which is roughly 6 x the cost of using the cheapest index (it's a bug I've come across before - the cost for the execution path chosen should be closer to 360).
Even so, I don't think the optimizer should have managed to consider more than the
two it did - so I think there's another bug (or algorithm) coming into play for index-only queries. I would pass this one to Oracle as a good example of the optimizer clearly going badly wrong.

-- 
Regards

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

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


Next public appearances:
 Jan 29th 2004 UKOUG Unix SIG -  v$ and x$
 March 2004 Hotsos Symposium - The Burden of Proof
 March 2004 Charlotte NC OUG - CBO Tutorial
 April 2004 Iceland


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February


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


"Steve B" <BigBoote66_at_hotmail.com> wrote in message
news:67bcf80a.0401261001.7315e658_at_posting.google.com...


> I sent you a trace file this weekend, from my account above.

> Meanwhile I'm trying to flail away at this myself.  However, my

> attempts to use the dbms_stats.set_index_stats procedure don't seem to

> be yielding much fruit.  I've tried dialing down the DISTINCT_KEYS,

> CLUSTERING_FACTOR and NUM_ROWS values on my index using the procedure,

> but no matter what I do, the trace file still shows that the optimizer

> thinks the NDV (number of distinct values, I presume) for the greedy

> indexes is still in the thousands.  Is there some other procedure I

> have to do to knock these stats into the optimizers brain, or am I

> modifying the wrong values?

>

> -Steve

>