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 🙂


1. dombrooks - January 10, 2008

What an excellent spin on the blog tagging thing.


2. girlgeek - January 10, 2008

I am waiting breathlessly to hear more information on each of your 8 points, particularly #5 “It’s possible to make the CBO reference and use within an execution plan indexes that don’t in actual fact exist.”


3. Andy C - January 10, 2008

Howard Rogers will be absolutely delighted !


4. Richard Foote - January 11, 2008

Girlgeek, oh please not breathless, anything but breathless. I’m not sure I can cope with the pressure and responsibilities !!

Each point will have it’s own Blog entry I promise, starting of course with #5 😉


5. Robert - January 11, 2008


thanks for your eight line poem!

Actually I have done #6 (thanks to Tom Kyte’s excellent “Effective Oracle by Design”) which had dramatic effects in one instance (query time1h => 1s, index size small although table large) and earned me some surprised faces. Hehe.

After #5 the next most weird entry (to me) was #7. But I have to admit that I only read about RI so far and never used one of them (consciously).

Keep up the suspense! 🙂



6. Log Buffer #79: A Carnival of the Vanities for DBAs « H.Tonguç YILMAZ Oracle Blog - January 11, 2008

[…] and when all bloggers were so close to a divide into two one hero, Richard Foote, came with a different approach to tagging and in my opinion he saved the idea. If you are already tagged, you may take a side as […]


7. Richard Foote - January 11, 2008

Hi Robert

For the most original sneak of a David Bowie reference I’ve seen this week, I’ll write about #7 next in the coming days 🙂


8. Richard Foote - January 11, 2008

H.Tonguc, I’m not a hero, I’m just a really naughty boy …

Thanks for you comments though.

BTW, I notice on your Blog you’re from Turkey. Oracle University are trying to get me to do another of my index seminars in Turkey, some time around July /August.

It’s a small world …


9. H.Tonguç Yılmaz - January 11, 2008

Richard that’s good new really 🙂

I wish you come and hope to meet you in İstanbul, until that time I will be following your blog, especially waiting for bitmap join and application domain indexes.

Best regards.


10. Richard Foote - January 11, 2008

H. Tonguc, and they thought I wouldn’t be able to come up with enough material if I focused primarily on indexes …

See you hopefully in Instanbul !!


11. H.Tonguç Yılmaz - January 11, 2008

Who is “they” Richard, please let me talk with them?

I will support you for this seminar happening to the end, we have a group; http://www.oracleturk.org and being a group always helps 🙂


12. H.Tonguç Yılmaz - January 14, 2008

Richard I created a survey http://tonguc.wordpress.com/2008/01/13/survey-which-oracle-expert-do-you-want-to-see-in-istanbul/

and distributed it to Turkish Oracle Users group(~1000 subscribers) yesterday, and until now you are doing well 🙂 http://apex.oracle.com/pls/otn/f?p=32068:3

After a week or two I will be discussing these outputs with Oracle Turkey Education office.

Best regards.
tonguc.yilmaz at gmail.com


13. Ontario Emperor - January 14, 2008

I suspect that by the time we’re all done, the number “eight” will be as overused as the suffix “gate” (and yes, Justin Kestelyn has used the suffix “gate” in relation to the current unpleasantness. Novel take on the idea, and at least you didn’t send eight queries out at the end of your post.


14. Karthick - May 14, 2008

It’s hard to believe this on.

“An index can potentially be the most efficient and effective way to retrieve anything between 0% and 100% of data from a table”

I don’t think this statement is completely correct as I have seen and also proved to myself the great principle of tom which is

“Full scans are not evil, indexes are not good”

For 100% of data why should I go for an index scan when I can go for a full table scan? And I am sure the CBO will also do the same. Ok if you are getting only the value of an indexed column then I can understand an Index fast full scan is the one to choose. Otherwise it should be full table scan.

Especially these words “most efficient and effective” must be removed for the 8th point I believe.

Do correct me if I am wrong!!!




15. Karthick - May 14, 2008

I should have read your “Index Scan or Full Table Scan: The “Magic” Number (Magic Dance)” before writing my previous post.




16. Richard Foote - May 14, 2008

Hi Karthick

Yes, the Magic Number post answers the first part of the question.

In the coming days I’ll show you how an index reading all the data can be more efficient and effective than a FTS. And it doesn’t necessarily need to be via a Fast Full Index Scan, although of course that’s one possibility.


17. Gary - May 14, 2008

“I’ll show you how an index reading all the data can be more efficient and effective than a FTS”
Well the EASY answer is if a table has a lot of empty blocks under the HWM. I’m guessing that you have either more complex examples, and/or you’re putting together some nice numbers to back it all up with.


18. Richard Foote - May 15, 2008

Hi Gary

I never said the examples had to be complex 🙂

There are others as well which of course will all be backed up 😉


19. Rohit - February 5, 2011

Hi Richard,

One thing I know but don’t quite know the reasoning behind is why a predicate in a query must exactly match the index definition.


S and T be two datatypes such that for all S and T there exist f_to_s(T) and f_to_t(S) and these functions are intrinsic to Oracle.

Index IX_T is on col X of datatype T and we have the following query

select X from table where x = f_to_s ( a_t )

The above results in a full scan. What I don’t follow is what prevents Oracle from invoking f_to_t(a_s) on the passed value ? ( e.g. would be date and timestamp, varchars and numbers )



Richard Foote - February 18, 2011

Hi Rohit

It’s because the functions aren’t really intrinsic to Oracle and the CBO has no direct or easy way of knowing that such functions are logically compatible.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: