jump to navigation

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.

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:

  1. Index compression can actually make indexes substantially larger, not smaller
  2. 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
  3. Bitmap Indexes can be extremely useful and beneficial even if the column contains thousands of distinct values.
  4. B-Tree Indexes can be extremely useful and beneficial even if the column contains very few distinct values (as low as 1)
  5. It’s possible to make the CBO reference and use within an execution plan indexes that don’t in actual fact exist
  6. It’s possible and potentially very useful to just index some column values and not all column values within a table
  7. A REVERSE index can quite happily be used by the CBO to perform index range scans within an execution plan
  8. 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 🙂