Re: INDEX Question

[See quoted messages attached below.]

I am quite surprised by the response of ATAR SEN MITTAL to Roy Wagner's question about Oracle's use of composite indexes. My understanding is that Oracle would indeed use his hypothetical index when the query specifies 'where Column1 = "something";' but not if the predicate references Column2 and not Column1. I don't have any examples at hand that demonstrate this, but I'm fairly sure because our data warehouse application would perform much worse than it does were it not the case.

Oracle (and all database systems I know) will never choose to use a composite index unless at least the high-order column was specified in the query with a condition that is "indexable". (For example, LNAME='Jones" can be evaluated using the appropriate index, whereas LNAME^='Jones' cannot.) This is a rather simplistic example, but consider the typical phonebook. If you want to find Bill Smith's number, you can easily locate it by searching for "Smith, Bill". However, if you're not sure of Bill's last name, your phonebook (at least all I've seen) does not provide an access path to all entries where the first name is "Bill". In the same manner, if you have an index with the columns LNAME and FNAME (in that order}, Oracle will not use that index to process a query specifying WHERE FNAME="Bill".

Please, if I'm wrong about this, let me know...

                   Dave McLaughlin
                   Information Technology Division
                   University of Michigan

asmittal_at_ix.netcom.com (ATAR SEN MITTAL ) wrote:
>In <412e7o$7i5_at_newsbf02.news.aol.com> roywagner_at_aol.com (Roy Wagner)
>writes:
>>
>>If you set up an INDEX as follows:
>>
>>create index TABLE on TABLE(Column1,Column2);
>>
>>does this set up the index as a concatenated index or as separate
 indexes
>>for Column1 and Column2?
>>
>>If it creates a composite index as the book I have indicates, do I
 have to
>>set up separate indexes on Column1 (or Column2) if I want "indexed"
 access
>>on only that column; .ie. select * from TABLE where Column1 =
>>"something";.
>>
>>Thanks for clarifying this for me!
>>
>>- Roy Wagner -
>>"When driving my Miata, I always get to where I'm going before I want
 to
>>stop driving it."
>
>Hi !
>The composite index is used only if the query has a condition based on
>all the columns of the index. Otherwise the index is not used. So to
>use the index on column1 = something you have to create an index on
>column1 separately.
>
>Fm : Upinder Aggarwal
Received on Tue Aug 22 1995 - 00:00:00 CEST