Oracle: Index odd bits

Sometimes you hear a real expert (a.k.a. Tom Kyte) say something like, "Why is my index not being used? We are using a B-Tree index and the predicate does not use the leading edge of the index."

What's up with that?

Think about a library card catalog based on the book's name. You want to look up a book by Lee Iacocca. Which card will have that? Maybe the last one, they are ordered by the book name not the author. The same goes for our index above. It is ordered on, and only on, the first column (that is the leading edge). Oracle has no way of knowing what the value of the second column is, the index is organized on the first column. Let's say the index has 1,024 data blocks of 8k, or 8M. If you select by the first colum, the "leading edge" Oracle may be able to get the data by just reading 4 or 5 blocks. On average, if Oracle uses the index to find an item not based on how the index is organized (not on the leading edge) it will have to read half the blocks, int this case, 512 blocks, put them into memory, and maybe drive out LRU blocks that might have been useful.

Once you think about how an index actually works, the idea that Oracle might not use it starts to make a lot more sense.

Other reasons that Oracle might not use your index:

SELECT COUNT (*) FROM mytable; - if the column, that the leading edge of the index is based, on can have nulls, the count of the index does not equal the rows in the table; Oracle does not index nulls.

Next:
SELECT empno
FROM emp
WHERE UPPER(proname) = 'JONES';

Unless index is based on that function, the index will need to apply any function to every row. The index is no help in that case ('JONES', and 'jones' are not in the same place in an index. Oracle can not look at upper('james') and know if that = 'JONES or not, it has to apply the function, so reading the index is no help at all.
Next:
A final way to mess up the use of an index is to try to save some space. In the old days when storage cost money, a lot of numbers, that were never going to have math used them, were stored in character columns. So you might say,
CREATE TABLE t (x char(1) constraint primary key); -- note: this creates index
INSERT INTO t values ('5');
SELECT * FROM t WHERE x = 5;
Your select is for a number where the indexed column is a character. You would have to appy a function to the query to get the answer you want, thus the index shown will not be used. To Oracle your query is like this:
SELECT * FROM t WHERE TO_NUMBER(x) = 5;

In fact, if you use explain plan, you will ACTUALLY SEE

Predicate information
_________________________
filter (TO_NUMBER("X") = 5)

Last Two Reasons for Ignoring Your Index:
Oracle knows from your running dbms_stats that your query is going to return a large percent of the table, so it ignores the index.
Oracle knows nothing because you did not run dms_stats so it ignores the index.




NEXT: inner and outer tables on joins; what's up with that?