Re: use of reverse key index,cost based optimizer

Skewed, yes.

Unbalanced, no.

Jared

On Saturday 31 May 2003 00:34, Rajesh.Rao_at_jpmchase.com 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
>
>
>
> Jared.Still_at_ra
> disys.com To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com> Sent by: cc:
> root_at_fatcity.c 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 <DWILLIAMS_at_LIFETOUCH.COM>
> Sent by: root_at_fatcity.com
> 05/30/2003 12:09 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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.
> dwilliams_at_lifetouch.com
>
>
> -----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: jkstill_at_cybcon.com

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: ListGuru_at_fatcity.com (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).