Common Problems with Indexes

Indexes

There are several types of indexes. The oldest and most popular type of Oracle indexing is a standard b-tree index, which excels at servicing simple queries. Another useful type is the function-based index. In all likelihood, the indexes you are dealing with will be b-tree indexes, or you might have a few function-based indexes. Indexes are great when they work as you’d expect them to, but things can go wrong.
Note: indexes cause a performance hit in DML operations, and should only exist when they’re actually useful!

1. Common Problems with Indexes

The following describes a few scenarios where Oracle may not behave as you would expect.

1.1. Case A

You’re executing a query, but Oracle seems to be choosing some odd execution path and you’re sure there’s a better way to do it (i.e., use your index). Your statistics may be out-of-date or not accurate enough. That is, Oracle thinks using the index is the wrong way to go. See below section on generating statistics.

1.2. Case B

You’re doing SELECT COUNT(*) FROM T — and you have an index on T, but you notice that Oracle is doing a full table scan, rather than using the index. This is most likely because the column being indexed is nullable, meaning the index count could be less than the true row count, so it has to go the the table to get the true count. If the column was non-nullable then Oracle would have used the index instead — this is one of the reasons why you should only allow null values in a column when there is a good case for it. See below for a way to take advantage of nullable columns.

1.3. Case C

You have an index on X and you’re doing SELECT * FROM T WHERE f(X) = VALUE. You find the index is not being used; this is because of the use of the function. This may be a candidate for a function index instead.

1.4. Case D

You have a table T with a compound index (i.e., an index based on more than one column) on columns (X,Y). You do SELECT * FROM T WHERE Y = 5. This index will not be used since the query predicate did not involve column X. However, the index would be used for a query like SELECT X, Y FROM T WHERE Y = 10, as Oracle realises it does not need to go to the table to get X and Y as the values are in the index itself.

1.5. Case E

Suppose you have a table with primary key X and nullable column Y. Suppose there are 100 rows in it, where X ranges from 0 to 100. Doing SELECT COUNT(Y) FROM T WHERE X < 50 will result in a full table scan rather than an index access. Why? Because Oracle realised it would be retrieving 50% of the rows. If it used the index it would have to read an index block and process each of the rows on it. Then for every other row it would have do a database block get to get the row data. So it is more efficient to just read in the database block and find the 50 rows we’re interested in.
Consider the same query, and the same table, but with many more rows, and X ranging from 0 to 5000, say. In this case the index would be used, as it would be faster than a full table scan.

1.6. Case F

You have a column which has highly-skewed data (i.e., 98% of the values in this column are X, while the other 2% are Y and Z). This type of a column will gain little benefit from an index when querying for X, and Oracle will not use it. Histograms, described in a later section, are extra column statistics which greatly aid Oracle in making decisions on columns like these.

2. Nullable Columns & Indexes

B-tree indexes will not store completely null entries, so, typically, nullable columns will make any indexes on the column(s) slightly less effective — but it can also be used to an advantage.

Say you have a table with a column that takes exactly two values. Say the values are skewed; 90% of rows take one value, the other 10% take another. This can be indexed efficiently to gain access to the minority rows. The solution is to use a null for the majority rows and whatever other value you want for the minority rows. Say you have a JOB table with a nullable column called TASK_PENDING (which is a timestamp). When the a row is inserted, this column is given a timestamp. Now whatever processes the JOB table can just do a SELECT * FROM JOB WHERE TASK_PENDING IS NOT NULL. When it processes the tasks it can mark them as null, effectively removing it from the index. Therefore the index remains very small and efficient.

Seee more: http://www.credmond.net/oracle/tuning/