What If I Create a Bad Oracle Index?

In the query to the PRODUCT table, I have a COMPANY_NO column. Since this company’s expansion has not occurred, all rows in the table have a COMPANY_NO = 1. What if I am a beginner and I have heard that indexes are good and have decided to index the COMPANY_NO column? Consider the following example which selects only certain columns from the PLAN_TABLE after executing the query.

The cost-based optimizer will analyze the index as bad and suppress it. The table must be reanalyzed after the index is created for the cost-based optimizer to make an informed choice. The index created on COMPANY_NO is correctly suppressed by Oracle internally (since it would access the entire table and index):

 

 

 

 

 

 

 

You can force an originally suppressed index to be used (bad choice), as follows:

 

 

 

 

 

 

 

 

 

 

Indexes can also be suppressed when they cause poorer performance by using the FULL hint:

 

 

 

 

 

 

 

 

Next, consider a similar example in an 11gR2 database on a faster server with a 25M row table where I am summing all rows together. Oracle is once again smart enough to do a full table scan since I am summing the entire table. A full table scan only scans the table, but if I force an index (as in the second example), it has to read many more blocks (almost 50 percent more), scanning both the table and the index (resulting in a query that is almost four times slower).

 

 

 

 

 

 

 

 

 

 

 

Now let’s try scanning the index and then go to the table (bad idea):

 

 

 

 

 

 

 

 

 

 

 

TIP

Bad indexes (indexing the wrong columns) can cause as much trouble as forgetting to use indexes on the correct columns. While Oracle’s cost-based optimizer generally suppresses poor indexes, problems can still develop when a bad index is used at the same time as a good index.