Re: index columns

"Rules of thumb" are dangerous things with Oracle. But when it comes to indexes and I have no compelling reason to do otherwise, I usually place columns with the broadest appeal to the greatest variety of queries at the leading edge. This can go a long way toward avoiding situations where you end up with 12 indexes mostly with different permutations of the same 5 columns.

On Fri, Apr 17, 2015 at 9:56 PM, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:

> 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-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l