Friday, May 14, 2004
A one line comment in this somewhat entertaining 'discussion' on usenet, led me into doing some investigation into how Oracle physically stores descending indexes - those created like
Descending indexes store bit inverted data padded with an extra byte in ascending order.
Descending indexes also store null values but the optimizer doesn't know about this.
Descending indexes won't take advantage of 90/10 splits in the same way as ordinary indexes - unless of course your pattern of inserts is also reversed.
For all of these reasons descending indexes will end up larger, perhaps significantly, than their ascending cousins.
0 Comments
create index idx_desc on tab(col1 desc). It turned out to be an interesting journey. In summary
For all of these reasons descending indexes will end up larger, perhaps significantly, than their ascending cousins.
0 Comments:
Post a Comment
