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:
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).
