Hi,
If you will ALWAYS specify the three columns like:
Code:
where country='USA' and
state='New York' and
city='New York'
then it will make practically no difference which order the columns are in the index.
HOWEVER, in versions before 9, if your condition sometimes is only on some subset of columns like:
Code:
where city='New York'
only the TEST(city, state, country) index can be used - i.e. the one where you are selecting on the first column in the index.
Providing either order allows the index to be used, Tom Kyte argues for putting the LEAST discriminating column first as it allows efficient key compression and thus smaller indexes. Not sure I would bother about that! I would put first the column most likely to be specified without any others in a condition.
Last edited by DaPi; 02-13-2003 at 07:29 AM.
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman
Reply With Quote