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

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 "JOHN" with the index item. 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%');

AUTOTRACE has been set for this query statement. The following results are displayed:

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' or name like 'jO' );

The results are the same as before. However, the execution process is as follows, indicating that there are four scan ranges.

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             TABLE ACCESS BY INDEX ROWID ADDRESS                 INDEX RANGE SCAN ADDRESS_I             TABLE ACCESS BY INDEX ROWID ADDRESS                 INDEX RANGE SCAN ADDRESS_I

To further increase the query speed, we can specify three or more characters in the specific "name like" condition. However, this will make the WHERE clause very heavy. All possible combinations of uppercase and lowercase characters-joh, Joh, jOh, joH, and so on. In addition, specifying one or two characters can speed up the query.

Now let's take a look at how to use this basic technology when we reference different functions.