Big Tables, Sorts and Indexes Solution (Right On Mother) September 19, 2011Posted by Richard Foote in CBO, Oracle Indexes, Quiz.
My, what a clever lot we have reading this blog 🙂
Indeed, most of the work has already been done for me as we already have a couple of excellent demos in the list of comments.
The answer is Yes, the CBO will consider using the index and Yes, there may be a number of scenarios when the index will actually be selected by the CBO as it’s the genuinely cheaper option.
At the end of the day, it comes down to whether performing a Full Index Scan, one little block at a time but with the data coming out pre-sorted is cheaper than performing a multiblock Full Table Scan AND a subsequent sort of the data. It of course depends on various factors such as:
- The most crucial of all, the Clustering Factor of the index. If the data is very well clustered according to the index, then the cost of reading the entire table via the index can be vastly reduced and the index access path becomes viable. A poor (or average) CF, and using the index is just too expensive. Radoslav Golian has an excellent example in the comments on when an index with an excellent CF is chosen by the CBO.
- The compactness of the table. If the table is badly fragmented with perhaps lots of (say) deleted rows, then this increases the cost of a FTS and makes such a path less appealing to the CBO. Martin Preiss has an excellent example of this in the comments as well.
- The cost of the sort. Sorts are really expensive, especially if Oracle is forced to go to disk and even more so if it has to perform a multi pass sort, so the more costly the sort, the more likely the index is the cheaper option.
An important point for the index to be considered is that it must have a NOT NULL constraint on the column(s), else the index is ignored as the CBO can’t guarantee all rows can referenced within the index.
The moral of this story is this. There is no selectivity by which an index is not considered by the CBO. An index can potentially select 100% of all rows, if doing so is the cheapest option available to the CBO.
I’ve discussed using an index to select 100% of all data before if anyone is interested.
New question coming soon !!