Q

Redundant composite indexes

I have just taken over a database and I notice that there are redundant composite indexes for various tables. Isn't it possible to get rid of INDEX_1, since the columns indexed in INDEX_1 are part of INDEX_2 as well?

I have a question regarding composite indexes. I have just taken over a database and I notice that there are redundant composite indexes for various tables. By redundant I mean, for example, that TABLE T1 has INDEX_1 on columns (col1, col2, col3), and there is a second index INDEX_2 on columns (col1, col2, col3, col4). I would like to know why two indexes of this kind are required? In such a case isn't it possible to get rid of INDEX_1, since the columns indexed in INDEX_1 are part of INDEX_2 as well and are in fact in the same order, so automatically INDEX_2 can be used for all queries on col1, col2, col3 and col4. Please let me know if I am missing anything.
You are correct. INDEX_1 is redundant and should be dropped.
This was first published in January 2006

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close