RE: Reverse Key Index Performance

Ian,

I agree with where you are going with this, and this was touched on a bit earlier. For example, a simple case where 5 rows, sequentially numbered on the PK, are updated. As these rows are read from the MLOG$ table, there's a good chance all index entries could be in the same leaf block. So, the first index lookup requires the physical read, and the next 4 are gotten from the buffer since the block has been copied in. Now, with a reverse key, you could very well need to go get five different index blocks. So, it's pretty easy to imagine cases where you have to read a lot more blocks with the reverse key. And, many of these blocks, which might contain 2 or more matching index entries, could be flushed out and have to be re-read. But, with really random updates in the MLOG$, you could see cases where you approach having to read the same number of blocks either way.

I'm talking to one of the production DBA's about finding the space to do redo these tests in a more controlled environment. Can't really do it during regular hours, though, since they aren't paying me (or him) to do things to satisfy our curiosity. But it would be interesting to setup best and worse case scenarios and compare the differences.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of MacGregor,
> Ian A.
> Sent: Monday, May 06, 2002 11:48 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Reverse Key Index Performance
>
>
> I have never used RKI's nor have I read up on them before this
> posting; this is pure conjecture.
> As I recall this was happening in a correlated subquery. Is it
> possible that using a normal forward-key index the indexed
> could be stepped through sequentially , whereas using the reverse
> key meant that each index lookup required a different block to be read.
>
> Ian MacGregor
> Stanford Linear Accelerator Center
> ian_at_slac.stanford.edu

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).