Geek Answers Handbook

Understanding multiple overlapping indexes in oracle

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 deleted. We need to get this right, since this is a relatively large table that will constantly clog.

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

+4
source share
1 answer

As with most database issues, it depends :-)

In fact, it cannot be removed under any circumstances. If you have a query that uses only code in the where clause, it is possible that using subject_x1 will still be superior to subject_x2 just by the fact that less index data needs to be read.

As an extreme example, suppose code is char(2) and status is char (4094) `. If you have a request:

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

This is likely to lead to checking only the index, since there is absolutely no reason to go to the table. If you use subject_x1 and you read in 4K blocks from your index, you can enter several thousand codes with each reading.

Using subject_x2 , each read only gives you one code (and perhaps the state is wasted). This is a huge performance difference.

However, and I can’t stress this enough, I don’t know!

Profile your queries (and updates) using both indexes to see what works best and do it with representative data. If you find that subject_x1 gives better performance for some queries, leave it. This will affect the update and insert speed and storage requirements, but you will find that it usually does not matter:

  • the vast majority of databases are read much more often than written; and
  • most database complaints are related to speed, not storage.
+12
source

More articles:


All Articles