Understanding situation with multiple overlapping indexes in oracle

Question

Given the following indexes for an Oracle database:

CREATE INDEX subject_x1 ON subject (code);
CREATE INDEX subject_x2 ON subject (code, status);

Is it true that the first index is redundant, and can be removed. We need to get this right as this is a relatively large sized table that is going to be constantly hammered.

Any oracle documentation explaining or confirming this would be very helpful.

Answer 1

As with most database questions, it depends :-)

In fact, it shouldn't be removed in all circumstances. If you have a query that uses code alone in the where clause, it's possible that the use of subject_x1 will still outperform subject_x2 just by virtue of the fact that less index data will need to be read in.

An extreme example, let's say that code is a char(2) and status is a char(4094)`. If you have the query:

select code from tbl where code > 'dd' and code < 'gg';

That's most likely to result in an index-only scan since there's absolutely no reason to go to the table. If you use subject_x1 and you read in 4K blocks from your index, you can bring in a couple of thousand codes with every read.

By using subject_x2, each read only gives you one code (and possibly a wasted status). That's a huge difference in performance.

However, and I cannot stress this enough, measure, don't guess!

Profile your queries (and updates) using both indexes to see which performs better, and do it with representative data. If you find that subject_x1 gives you better performance for some queries, leave it in. It will affect the update/insert speed and storage requirements but you'll find that won't generally matter since:

• the vast majority of databases are read far more often than written; and
• most complaints about databases have to do with speed rather than storage space.