Reverse Key Index Performance

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sat, 04 May 2002 06:23:18 -0800
Message-ID: <F001.00457FEA.20020504062318@fatcity.com>

Listers,

Has anyone done extensive benchmarking of unique key index lookups comparing reverse key and b-tree? For the sake of brevity, I am leaving out a lot of details at this point. Just simply had a case where doing a million unique key lookups using a reverse key index would run for hour(s). Change to b-tree, 6 minutes. Build again as reverse key, same TS, "fresh" index, runs for hours again. I've got lots of ideas but I want to keep this short.

Query was correlated sub-query (replication query for the <> "I"). Sure, there are issues with RKI's not packing as much in and being bigger, you lose any benefit of optimal clustering, the range scan issue, etc. And for rows physically located together, I know I will have to access more index blocks to get those rows versus a b-tree with good clustering of data. And how much overhead is needed for reversing the value used to do the lookup? And my test case was hardly a controlled environment where I could rule out or control all other factors. And I know of some things that could very well have skewed the testing.

Anyway, I'm curious if anyone else has done some benchmarking on this. I would be curious about the results and comparing notes.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

--

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). Received on Sat May 04 2002 - 09:23:18 CDT