RE: index columns
Date: Sat, 18 Apr 2015 07:56:56 +0000
Message-ID: <CE70217733273F49A8A162EE074F64D928288D92_at_EXMBX01.thus.corp>
You're both wrong - although there are some advantages to "most selective first" if you're still running Oracle 5.
As others have pointed out, order does matter, and the optimum order is dependent on the ways in which the index can be beneficial to the application. This highlights an important feature of index design - you shouldn't be considering an index in isolation, you need to consider the entire set of indexes for the table as a unified set (which relates to Mark Brinsmead's second email).
All other things being equal: if you have N columns in an index and M predicates then the predicates that Oracle can use as ACCESS predicates (i.e. the ones that define the total number of leaf BLOCKS that will have to be visited) are the equality predicates 7on the leading columns of the index up to (and then including) the column with the first range-based predicate, or up to the first "missing" column in the predicate list.
If the first N columns in the index are always used in equality predicates then it almost always makes sense to order them least selective (i.e. most repetitive) first as to allow for maximum compression on the index. One particular case where I would be very careful to check the impact of this strategy is where the first column was a varchar2() column with a highly repetitive leading edge (e.g. an 6 digit number that had been to_char()'ed with leading zeros to a 20 character column) and not many distinct values.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Orlando L [oralrnr_at_gmail.com] Sent: 17 April 2015 23:02
To: oracle-l_at_freelists.org
Subject: index columns
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
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Apr 18 2015 - 09:56:56 CEST