Re: Optimzer use for an index

On Fri, 20 Apr 2001 13:51:49 -0400, "Syltrem" <syltrem_at_videotron.ca> wrote:

>Answers embedded below
>Thanks for bringing some light into this!

This is a weird, but very interesting one. My guesses are as follows:
- The query selects the major part of the table - the order by can *not* be prevented by using the index  (index on columns a,b,c order by on a, *c*, so one missing column) - it is still more advantageous (given db_file_multiblock_read_count) to run a full table scan instead of using the index. Why the same line of reasoning isn't followed in the second scenario, I really don't know.
Maybe I have been not clear enough in my initial reply with respect to using indexes.
What I was trying to say: if you have a clause on all columns of an index, the optimizer will use all columns. It is not going to suppress a column out of itself. If you would have been using a different column, the path would have been different. So more questions:
- you are not using by any chance using some weird low value for the compatible parameter, suppressing new CBO functions? - in itself your analyze is correct, but I usually take more buckets for the histogram, say 10. Could you try to add size 10 to the analyze command
- and now for the final firecracker (c/o of Jonathan Lewis, Practical Oracle 8i)
could you issue
drop the second index
alter session set events '10053 trace name context forever, level 1' /* this will dump the optimizers reasoning in an ordinary trace file */
reissue the statement
recreate the second index
reissue the statement
post the results?

Regards,

Sybrand Bakker, Oracle DBA

Sybrand Bakker, Oracle DBA Received on Fri Apr 20 2001 - 13:33:31 CDT