RE: Add more index or not ?

Yeah, I guess it depends on how you look at it. I'm coming more from a data warehouse perspective these days so the selectivity thing is much more important and the conention thing a side effect. :-)

That's only until transaction guys ask me of course ;-)

George

> -----Original Message-----
> From: "Jared Still" <jkstill_at_bcbso.com> [mailto:jkstill_at_bcbso.com]
> Sent: Thursday, April 27, 2000 7:36 PM
> To: George Brennan_at_IT@WMINTL19
> Cc: smtp_at_inl001@servers[<ORACLE-L_at_fatcity.com>]
> Subject: 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 Fri Apr 28 2000 - 03:54:41 CDT