Skewed, yes. Unbalanced, no.
Jared On Saturday 31 May 2003 00:34, [EMAIL PROTECTED] wrote: > Assume an index on employee number. The number is assigned sequentially, > and as such, the rightmost index leaf block would always be used. A > possible hot block. A reverse key index can avoid this. Also, assume when > an employee retires or quits, the record is deleted. But the space freed > within the index leaf block will never be used (unless of course, all > entries from that leaf block are deleted). A reverse key index can help > you avoid these "holes" or otherwise skewed indexes, and help the index > become more "balanced", but has the pitfall that is mentioned. > > Raj > > > > [EMAIL PROTECTED] > disys.com To: Multiple recipients of > list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: > [EMAIL PROTECTED] Subject: RE: use of reverse > key index,cost based optimizer om > > > 05/30/2003 > 10:44 PM > Please respond > to ORACLE-L > > > > > > > Dennis, > > My understanding of B*tree is that it is always balanced. Monotonically > increasing > keys will create a right hand index, but nonetheless balanced. > > If wrong, I'm sure to be corrected. :) > > Also, I don't believe the reverse key index will help queries any. I'm > guessing that under > normal circumstances it would increase the number of index blocks that > needed to be > cached. > > In the case of a range scan, it would definitely not perform as well, and > increase the likelihood > of a FFS or FTS, depending on the queries normally used in a system. > > The primary purpose of these was to reduce block pings on OPS IIRC, which > would also reduce > block contention on inserts as you said. > > > Jared > > > > > > > > > DENNIS WILLIAMS <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 05/30/2003 12:09 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > cc: > Subject: RE: use of reverse key index,cost based optimizer > > > helpdesk > I don't see where anyone responded. If you look up reverse key index in > the documentation, it says something about if you have a column where most > of the values have leading values that are close. Reverse key will help > the > btree of the index be more balanced. That helps on queries. And on inserts > you aren't continually hitting the same block, but spreading the inserts. > Oracle has two SQL optimizers, rule-based and cost based. The cost > based > is more sophisticated. You first populate statistics on your tables. When > creating an execution plan for your SQL the CBO will consider those > statistics. Does that answer your questions? > > Dennis Williams > DBA, 80%OCP, 100% DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > > -----Original Message----- > Sent: Friday, May 30, 2003 1:25 AM > To: Multiple recipients of list ORACLE-L > > > > > > > hai gurus > > please tell use of using reverse key index > and what exactly cost based optimizer > thanks in advance > manjunath -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
