Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Importance of Column Order in Composite Index

Re: Importance of Column Order in Composite Index

From: Nuno Souto <nsouto_at_optushome.com.au.nospam>
Date: Wed, 31 Oct 2001 10:03:36 GMT
Message-ID: <3bdfca54.2484163@news>


In a valiant and sublime effort,Howard J. Rogers frowned, dipped a thumb in soot and doodled:

>Indexing as X, Y, Z might actually be preferable, because with the low
>selectivity columns first, you can use index key compression, and get much
>more compact indexes (and hence much lower I/O) than you might otherwise
>manage. That's assuming you have at least 8i, of course.

I'd go along with that too. The only case is where the vast majority of SQL predicates use only Y and Z or Y alone, in which case the index won't be picked up unless the predicates are tweaked. Then again, enter 9i and all that changes.

>
>The old myth about the most selective column needing to be first is just
>that -a myth. Hasn't been true for ages.

I don't even know where the heck that one came from! I've NOT ONCE had good results with it and I've been using ORACLE since V4!

Must have been another one of those "benchmarks" done without the slightest control and quoted out of context, so widespread a few years ago amidst the "cognoscenti". Those things have done more damage to proper tuning and use of ORACLE than the entire competition put together...

>
>9i has skip scanning of indexes which means searches for non-leading keys
>can be done efficiently, so that shouldn't be a major issues there.

Hale-luia!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!


>
>Just my first thoughts. I may have seconds, though.
>

Me 2. Nothing like a good feed! ;-)

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Wed Oct 31 2001 - 04:03:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US