RE: Add more index or not ?

Interesting,

Is this the justification for the reverse key index, to improve distribution and selectivity?

George
> -----Original Message-----
> From: "Jared Still" <jkstill_at_bcbso.com> [mailto:jkstill_at_bcbso.com]
> Sent: Thursday, April 27, 2000 3:22 PM
> To: George Brennan_at_IT@WMINTL19
> Cc: smtp_at_inl001@servers["Multiple recipients of list ORACLE-L"
> <ORACLE-L_at_fatcity.com>]
> Subject: RE: Add more index or not ?
>
>
> On Thu, 27 Apr 2000 George.Brennan_at_warnermusic.com wrote:
>
> >
> > Looks like a (badly) contrived question to me.
> >
> > Strictly speaking there will are likely to be more index
> blocks for case 2 due to the increased span of each key.
> >
>
> But not always.
>
> I learned a valuable lesson about this several years ago.
>
> We had a large table that required an index that was not
> terribly selective. Even so, this index was necessary
> for some queries. A full table scan would blow our
> 9 second end-to-end online response time out of the water.
>
> When trying to create this index, I would continually
> run out of space. The index was much larger than I
> predicted it would be.
>
> A contractor at the site had run into this before, and
> suggested adding the PK as another column on the index.
> ( The PK was a generated integer )
>
> This didn't make a lot of sense to me at the time, but
> I tried it and guess what, it worked.
>
> The resulting index was significantly smaller than the
> one that was created without the PK column.
>
> This was due to the way btree works; the index with the
> PK in it was broader and shallower than the one without it,
> and took a lot less space. ( I think I go that right :)
>
> Smaller index, faster reads.
>
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
Received on Thu Apr 27 2000 - 13:19:28 CDT