RE: Reverse Key Index Performance

Hello List,

I can see when you have multiple rows are being read every single time and when these blocks are in different leaf nodes performance could be horrible with REVERSE KEYS. However, in a pure OLTP application, where the rows are singleton selects I have noticed good performance and more so when we have large number of sequential inserts happening into tables. Reversing the surrogate key used as primary keys.

My reference is in an OPS environment. I will provide more details on this in the next couple of days (if anyone is interested) with more stats. Actually our performance with REVERSE keys is much better compared with natural keys. However we are using this only in selective tables where the SELECTS are singleton all the time.

Regards,

Murali Vallath

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tue, 07 May 2002 16:28:30 -0800

Larry,

You can find out this easily by formatting a report with tkprof and comparing the numbers.
Also did you check the height if the index in both cases and the clustering factor?

Regards,

Waleed

-----Original Message-----
Sent: Tuesday, May 07, 2002 8:01 PM
To: Multiple recipients of list ORACLE-L

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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Khedr, Waleed
   INET: Waleed.Khedr_at_FMR.COM

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





Murali Vallath
Oracle Certified DBA
http://www8.ewebcity.com/muralivallath/
http://www.summerksyus.com/


_________________________________________________________________
Send and receive Hotmail on your mobile device: http://mobile.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Murali Vallath
  INET: murali_vallath_at_hotmail.com

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