Re: INDEX Question

roywagner_at_aol.com (Roy Wagner) wrote:
>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";.
>

Oracle will create one index, based on the concatenation of column1 + column2. If you want to find rows where column1 = x, this will work fine. If you want to find rows where column2 = y you will need to scan the table or create a second index.

It's like the phone book. It's indexed on lastname, firstname. If you know the lastname and firstname you are most efficient. If you know the lastname only, you can still use the phone book but you will scan some range of rows that meet your where criteria. If you know the firstname only you are out of luck.

Beth Received on Fri Aug 18 1995 - 00:00:00 CEST