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.trackback
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 🙂
Richard Foote's Oracle Blog 
What an excellent spin on the blog tagging thing.
LikeLike
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.”
LikeLike
Howard Rogers will be absolutely delighted !
LikeLike
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 😉
LikeLike
Richard,
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! 🙂
robert
LikeLike
[…] 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 […]
LikeLike
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 🙂
LikeLike
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 …
LikeLike
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.
LikeLike
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 !!
LikeLike
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 🙂
LikeLike
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
LikeLike
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.
LikeLike
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!!!
Regards,
Karthick.
http://www.karthickarp.blogspot.com/
LikeLike
I should have read your “Index Scan or Full Table Scan: The “Magic” Number (Magic Dance)” before writing my previous post.
Regards,
Karthick.
http://www.karthickarp.blogspot.com/
LikeLike
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.
LikeLike
“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.
LikeLike
Hi Gary
I never said the examples had to be complex 🙂
There are others as well which of course will all be backed up 😉
LikeLike
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.
Let
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 )
Thanks,
Rohit
LikeLike
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.
LikeLike