Re: How Oracle use an INDEX?

"Nadeem Kafi" <kafis_at_super.net.pk> wrote in message news:f1698545.0108152134.5bc036b2_at_posting.google.com...
> Hello,
>
> Thanks both of you for giving time here.
>
> BTW, I am using 8.1.6 on W2K Pro. And I am not using any explicit
> hints therefore explain plan shows the optimizer = "CHOOSE".
>
> My only concern is the placement of columns in the where clause
> when using concatenated indexes. Suppose I have an index (a,b,c)
> then do I write
>
> where a = 1, b= 2, c= 3
>
> OR
>
> where c= 3, b = 2, a = 1
>
> i.e Do I start my where clause with the first index column or do
> I place this column in the very end and place the last index column
> as the first where clause column. The same will apply to order by
> clause as well, I think.
>
> Finally, how do I know that Oracle is using all/what columns of a
> multi-column index?
>
> TIA and Best Regards,
> NK.

The where clause shouldn't matter.

As to your second question (and I currently have an example at hand at my customer)

consider a composite index(a,b,c)
I have an equality for a (a= constant) and *c* (c=constant)
What will the optimizer do?
As column b doesn't occur in the where clause it will perform a *range scan* on *a only*!!! In my case this can be verified by checking the explain plan cardinality for this step against the real cardinality.

So to know which columns Oracle is using requires - the where clause
- mapping that against the index definition If there is a 'gap' somewhere, only the leading portion of the index will be used.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Thu Aug 16 2001 - 13:13:38 CDT