Re: Reverse Key Index Performance

If this is Oracle 8.1, it is possible for the optimizer to reject even a primary key index as too expensive once it has been reversed. Did you check the execution path (and I/O characteristics if necessary) to see if the index was still being used.

I haven't been able to emulate the problem in 9.0 yet.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 04 May 2002 15:03

|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: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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