Re: index columns
From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Fri, 17 Apr 2015 21:56:36 -0400
Message-ID: <5531B9D4.5030307_at_yahoo.com>
On 04/17/2015 06:02 PM, Orlando L wrote:
> All
>
> My colleague and I got into a discussion about indexes. I feel that
> putting the most selective column first while creating multi column
> indexes is the correct approach, followed by second most selective
> column as the second column in the index and so on. My colleague feels
> that the order does not matter. Can someone clarify.
>
> OL
Date: Fri, 17 Apr 2015 21:56:36 -0400
Message-ID: <5531B9D4.5030307_at_yahoo.com>
On 04/17/2015 06:02 PM, Orlando L wrote:
> All
>
> My colleague and I got into a discussion about indexes. I feel that
> putting the most selective column first while creating multi column
> indexes is the correct approach, followed by second most selective
> column as the second column in the index and so on. My colleague feels
> that the order does not matter. Can someone clarify.
>
> OL
Hi Orlando,
Since Oracle can use leading index column to perform a range scan, I
tend to put the column which is most frequently used in the expressions
like COL_NAME=<value> first because it enables me to resolve the largest
amount of the queries using index. However, it all depends on the
consequences of the range scan based on the first column. It may not be
a good idea at all, depending on the table. Sorry, I cannot give you an
exact answer.
Regards,
-- Mladen Gogala Oracle DBA http://mgogala.freehostia.com -- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 18 2015 - 03:56:36 CEST