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

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

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<busao4$n45$1_at_hercules.btinternet.com>...
> On the 8.1.6 system, do the following:
>
> alter system flush shared_pool;
>
> alter session set events '10053 trace name context forever';
>
> execute the two queries
> (the select of columns and the select of count(*)
>
> exit
>
> Then email me the trace file (zipped might be a good idea.
>
> I can't guarantee that I will find time to look at it
> closely, but there might be some further clues in there
> about what you could do.
>
>
> --
> 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.0401231500.2bbffce0_at_posting.google.com...
> > Thanks for your input. Your idea about manually mucking with the
> > index stats has me intrigued. Following your lead, responses in-line
> > below:
> >
Received on Mon Jan 26 2004 - 12:01:47 CST