Yes exactly.
"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@xxxxxxxxxxxxx>
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
--
//www.freelists.org/webpage/oracle-l
