RE: Add more index or not ?

The reverse key indexes are to reduce contention for inserts on PK index blocks in OLTP systems with high transaction rates.

Sequential generated PK values will go into different index blocks.

At least, that's my understanding of it.

Jared

On Thu, 27 Apr 2000 George.Brennan_at_warnermusic.com wrote:

> 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
> > jkstill_at_teleport.com - private
> >
> >
> >
> >
>
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Thu Apr 27 2000 - 13:35:34 CDT