8 Things You May Not Know About Indexes January 10, 2008
Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Richard's Musings.20 comments
I wasn’t going to make a post today, being out for most of the evening, but I’ve just been “tagged” by Niall Litchfield.
There’s a somewhat bizarre wave washing the Blogshere at the moment whereby everyone is being asked to reveal 8 things about themselves that not many people know. It all sounds like a bit of fun, although as a craze, it does seem to have gone a bit berserk.
Now I’m a pretty private sort of person so this isn’t quite my thing. However, in the spirit of it all, I’ve decided to bend the rules a tad and give it an “Oracle Index” theme.
So here are 8 things (from the top of my head) that you may not know about Oracle Indexes:
-
Index compression can actually make indexes substantially larger, not smaller
-
It’s not true you can’t index NULL values. A single column or a set of columns containing nothing but NULLs can easily be indexed
-
Bitmap Indexes can be extremely useful and beneficial even if the column contains thousands of distinct values.
-
B-Tree Indexes can be extremely useful and beneficial even if the column contains very few distinct values (as low as 1)
-
It’s possible to make the CBO reference and use within an execution plan indexes that don’t in actual fact exist
-
It’s possible and potentially very useful to just index some column values and not all column values within a table
-
A REVERSE index can quite happily be used by the CBO to perform index range scans within an execution plan
-
An index can potentially be the most efficient and effective way to retrieve anything between 0% and 100% of data from a table
Now if there’s anything in the list you indeed didn’t know, don’t worry, that’s what this Blog is for :)
