It's rather more subtle than the manuals suggest.
Reverse indexes used against sequence-generated columns:
- usually have a higher clustering factor than non-reverse
indexes, and are therefore more likely to be discarded
for join purposes by the optimizer.
- degenerate quite rapidly because the index entry insert
is randomly scattered around the leaf blocks, which
affects the position of the leaf block split. For ascending
sequences, the index will be close to 100% packed.
The extra space requirement typically has a performance
side-effect because of the impact on the buffer. NB you
will not see this difference if you simply rebuild an existing
index as a reverse index - it is a run-time effect. A damage
limitation exercise is to rebuild the index with a small PCTFREE
on a regular basis.
- can participate in range-scans with the restriction that
the index is a multi-column index, and the sequence
is not the last column, as queries that do an equality
test on the leading column(s) can use the range scan method
against the rest of the columns.
--
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
Richard Kuhler wrote in message ...
>The Oracle documentation suggests possibly using 'reverse key indexes'
>to prevent serialization with monotonically growing values. I see that
>you can't perform range scans with them but are there other
>disadvantages to using them? Anyone have any experience using them for
>primary keys on large tables (>10 million rows)? Are there other
>factors that may influence occasionally rebuilding the index still?
>
>
>Thanks,
>Richard
>
Received on Thu Apr 25 2002 - 02:45:14 CDT
