Create an index in Oracle and force the optimizer to use it

Welcome to the Oracle community forum and interact with 2 million technical staff. When the WHERE clause uses a function for a column, unless this simple technology is used to force the index, otherwise, the Oracle optimizer cannot use indexes in queries. Generally, if the WHERE clause does not use functions such as UPPER, REPLACE, or SUBSTRD

Welcome to the Oracle community forum and interact with 2 million technical staff> Go To The WHERE clause to use a function for a column, unless you use this simple technology to force the index, otherwise, the Oracle optimizer cannot use indexes in queries. Generally, if the WHERE clause does not use functions such as UPPER, REPLACE, or SUBSTRD

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

When a WHERE clause uses a function for a column, the Oracle optimizer cannot use the index in the query unless this simple technology is used to force the index.

Generally, if you do not use functions such as UPPER, REPLACE, or substrd in the where clause, you cannot create specific conditions for the specified column. However, if these functions are used, there will be a problem: these functions will prevent the Oracle optimizer from using the index on the column, So compared with the index, the query takes more time.

Fortunately, if the columns using the function contain complex data, you can use this method to modify the query statement to use the index forcibly and run the query more effectively. This article introduces the involved technologies and explains how to implement them in two typical cases.

Case sensitivity

Before discussing how to force an index because the function modifies the column content, Let's first look at why the Oracle optimizer cannot use an index in this case. Suppose we want to search for data that contains a mix of upper and lower cases, such as the NAME column of the ADDRESS table. Because the data is input by users, we cannot use data that has been unified into uppercase. To locate every address named john, we use a query statement containing the UPPER clause. As follows:

SQL> select address from address where upper (name) like 'john ';

Before running this query statement, if we run the "set autotrace on" command, we will get the following results, including the execution process:

ADDRESS
Cleveland
1 row selected.
Execution Plan
SELECT STATEMENT
TABLE ACCESS FULL ADDRESS

In this case, the Oracle optimizer performs a complete scan of the ADDRESS table without using the NAME column index. This is because the index is created based on the actual values of the data in the column, and the UPPER function has converted the characters to uppercase, that is, these values are modified. Therefore, the index of this column cannot be used for this query. The optimizer cannot compare with the index item "JOHN". No index item corresponds to "JOHN"-only "john ".

Fortunately, if you want to force an index in this case, you can simply add one or more specific conditions to the WHERE clause to test the index value, and reduce the rows to be scanned, but this does not modify the conditions in the original SQL code. The following query statement is used as an example:

SQL> select address from address where upper (name) like 'jo % 'AND (name
Like 'J % 'or name like 'J % ');

You can use this query statement (AUTOTRACE is set) to obtain 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 scans the range determined by each statement in the AND join statements in the WHERE clause-the second statement does not reference the function, so the index is used. After scanning in two ranges, merge the running results.

In this example, if the database has hundreds of thousands of rows, you can use the following method to expand the WHERE clause to further narrow the scanning range:

Select address from address where
Upper (name) like 'john' AND (name like 'jo %'
Or name like 'jo % 'or name like 'jo ');

[1] [2]