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 <[EMAIL PROTECTED]> 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 |[EMAIL PROTECTED] |214.954.1781 | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
