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

-----Original Message-----
Sent: Monday, May 06, 2002 5:08 PM
To: Multiple recipients of list ORACLE-L

Waleed,

Thanks for doing a test. There could be cases where we don't benefit from buffering, and, where we will have to visit a lot more blocks, but surely not enough to account for the difference I was seeing.

As far as the plans, the raw 10046 traces show them as being the same, and, there are no parallel query operations. You might have picked this up from one of the other emails on this topic, and particularly where I noted the severe waits on db file sequential reads when using the reverse key. Would tend to indicate I/O issues (or why am I requiring so many I/O's). Anyway, switching back and forth between the two in the same tablespace and seeing the difference could simply have been pure luck in the way things got laid down physically each time. And that's the big question since with the striping that is used, and currently nothing mapping things out back to or looking inside the EMC's, things could have been very different without my knowing.

Thanks for taking the time to run a test. FWIW, the original intent for RKI's from what I have been told is that at one time their intention was to have 8 simultaneous processes operating on different rows from the staging table using the MOD function and different divisors. That was scrapped some time ago and only a single process is used. So what they were trying to avoid with the RKI's is no longer an issue (and there are other approaches to RKI's to avoid the problem that RKI's are intended to cure). So, the RKI's on the two tables have been permanently converted to b-tree.

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 Khedr,
> Waleed
> Sent: Monday, May 06, 2002 8:13 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Reverse Key Index Performance
>
>
> Hi Larry,
>
> I did some testing on RKI after seeing your post. It's not any different
> that normal indexes for unique lookups.
>
> I'm sure you have some other issue like change in execution plan or even a
> small difference like using/not using Oracle PQO.
>
> Regards,
>
> Waleed

-- 
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: MacGregor, Ian A.
  INET: ian_at_SLAC.Stanford.EDU

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