Indexing in Oracle and forcing the optimizer to use

When a WHERE clause uses a function for a column, the Oracle optimizer cannot use the index in the query unless the simple technique is used to force the index. In general, you cannot establish a specific condition on a specified column if you do not use functions such as upper, REPLACE, or SUBSTRD in the WHERE clause. However, if these functions are used, a problem arises: These functions prevent the Oracle Optimizer from using indexes on columns, so the query takes more time than the index.

Fortunately, if you include character data in these columns that use functions, you can modify the query statement in such a way that it is mandatory to use the index and run the query more efficiently. This article describes the techniques involved and explains how to implement them in two typical scenarios.

Case Mix

Before we discuss how to force the use of the index because the function modifies the contents of the column, let's first see why the Oracle Optimizer cannot use the index in this case. Suppose we want to search for data that contains a mixed case, such as the Name column of the Address table. Because the data is entered by the user, we cannot use data that has been unified to uppercase. To find every address named John, we use a query statement that contains the upper clause. As shown below:

Sql> Select Address the where upper (name) like ' JOHN ';

Before running this query statement, if we run the command "set Autotrace on", we get the following results, which include the execution process:

Address
Cleveland
1 row selected.
Execution Plan
SELECT STATEMENT
TABLE ACCESS Full Address

As you can see, in this case, the Oracle Optimizer makes a complete scan of the Address table without using the index of the name column. This is because the index is based on the actual value of the data in the column, and the upper function has converted the characters to uppercase, that is, the values have been modified, so the query cannot use the index of the column. The optimizer cannot compare "John" with an index entry, and no index entry corresponds to "John"-Only "John".

Thankfully, if you want to enforce the use of indexes in this situation, there is an easy way to do this: simply add one or more specific conditions in the WHERE clause to test the index values and reduce the rows that need to be scanned, but this does not modify the conditions in the original SQL encoding. Take the following query statement as an example:

Sql> Select Address the where upper (name) like ' jo% ' and (name
Like ' j% ' or name like ' j% ');

Using this query statement (set Autotrace), you get the following results:

Address
Cleveland
1 row selected.
Execution Plan
SELECT STATEMENT
Concatenation
TABLE ACCESS by INDEX ROWID address
INDEX RANGE SCAN address_i
TABLE ACCESS by INDEX ROWID address
INDEX RANGE SCAN address_i

The optimizer now scans the ranges determined by each of the two statements in the WHERE clause----The second statement does not have a reference function and thus uses the index. After a two-range scan, the results are merged.

In this example, if the database has hundreds or thousands of rows, you can expand the WHERE clause in the following ways to further narrow the scan:

Select Address where
Upper (name) like ' JOHN ' and (name like ' jo% '
or name like ' jo% ' or ' name ' Jo ';