Re: INDEX Question

roywagner_at_aol.com (Roy Wagner) wrote:
>And another similar question:
>
>Does the select statement that follows mean that it does the concatenation
>automatically to use the index above? Since it is a composite index, does
>that mean the I would need to create a separate index if I want to access
>the Name (or Skill) column separately and efficiently (ie. indexed)?
>Thanks.
>
>select Column1, Column2, Column3
> from TABLE
>where Column1 = 'THIS'
> and Column2 = 'THAT';
>
If you have created the index on (column1, column2) then the above SQL will take full advantage of the index. I don't know the internal structure of the index to know if concatenation is really the right term, but in effect yes, that is what Oracle will do automatically for you.

Again, If you include values for both column1 and column2, Oracle will take full advantage of the composite index. If you only knew that column1='THIS' and didn't know anything about column2 your query will still use the index to find all rows matching your criteria for column1 because that is the first column listed in the create index statement and hence the primary index key. If you only had a value for column2 you would not use the index at all.

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