Understanding situation with multiple overlapping indexes in oracle?

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 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.

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 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.

2 +1 Excellent answer. – Rob van Wijk Aug 18 '10 at 8:34 3 +1 for stressing the importance of benchmarks – APC Aug 18 '10 at 9:00.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.