Re: Index rebuild, coalesce or reserve key

"wangbin" <binw_at_ozemail.com.au> wrote in message news:c14855ae.0312092143.444820ee_at_posting.google.com...

[snip]

> Any other issue with reserve key?
>

That's a "REVERSE" key index. It's not true to say they 'cannot' participate in a range scan, merely that a predicate that asks for a range of values will end up having to scan (probably) the entire base of the index, and the optimiser may well decide that that's just silly, and do a full table scan instead. On the other hand, it may not. It depends on your SQL statements, inevitably.

The 'index efficiency' is also a bit of an iffy one, since you have to define what you mean by index efficiency. They will block split in a way that non-reversed indexes on monotonically incrementing sequence numbers don't, and therefore space usage efficiency will be lower, for sure. But that's just a space thing, until you start scanning most of the index, when it does become an I/O issue.

The clustering factor will indeed be poorer than with its non-reversed cousin. And that could indeed be a show-stopper. But without explain plans, you'll never know for sure.

Presumably, you don't have the partitioning option? Because hash partitioning an index is a much better way of achieving what the reverse key index is supposed to achieve.

In your case, and assuming no partitioning option, I would have thought the coalesce option would have been the best, in principle. But you say there are bugs you are encountering... though you don't say what version of Oracle you are using, nor which O/S, so it's difficult to know for sure.

Regards
HJR Received on Wed Dec 10 2003 - 03:50:30 CST