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 MixBefore 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 in table 1. 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 Inde X 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 from address where upper (name) like ' JOHN ' and (name like ' jo% ' or name like ' jo% ' or ' Ame like ' JO '); |
The resulting results are the same as before, but their execution is shown as follows, indicating that there are 4 scanning ranges.
| Execution plan select statement CONCATENATION TABLE ACCESS by INDEX ROWID address INDEX RANGE SCAN ADDR ess_i table ACCESS by INDEX ROWID address &nb Sp INDEX RANGE SCAN address_i table acce SS by INDEX ROWID address INDEX RANGE SCAN address_i ; table ACCESS by INDEX ROWID address &n Bsp index RANGE SCAN address_i |
If you try to further improve the query speed, we can specify 3 or more characters in a specific "name like" condition. However, doing so will make the WHERE clause unwieldy. Because it requires all possible combinations of uppercase and lowercase characters-joh, Joh,joh,joh, and so on. In addition, specifying one or two characters is sufficient to speed up the query. Now let's see how we can use this basic technique when we're referencing different functions.
Use the Replace conditionJust as names do not always enter uppercase, phone numbers appear in many formats: 123-456-7890, 123 456 7890, (123) 456-7890, and so on. If you search for the above number in column name Phone_number, you may need to use function replace to guarantee a uniform format. If you include only spaces, hyphens, and numbers in the Phone_number column, the WHERE clause looks like this:
| WHERE Replace (replace (phone_number, '-'), ') = ' 1234567890 ' |
The WHERE clause uses the REPLACE function two times to remove hyphens and spaces, ensuring that the phone number is a simple numeric string. However, the function prevents the optimizer from using the index in the column. Therefore, we modify the WHERE clause as follows to force the index to be executed.
| WHERE Replace (replace (phone_number, '-'), ') = ' 1234567890 ' |
and phone_number like ' 123% ' if we know that the data may contain parentheses, the WHERE clause is slightly more complex. We can add the Replace function (remove parentheses, hyphens, and spaces) and expand the added condition as shown below:
| WHERE Replace (replace (Phone_number, '-), '), ' ('), ') ' = ' 1234567890 ' and (phone number Like ' 123% ' or phone_number like ' (123% ') ' |
This example emphasizes the importance of cleverly selecting a WHERE clause condition, and these conditions do not change the query results. Your choice should be based on a full understanding of the type of information that exists in the column. In this case, we need to know that there are several different formats in the Phone_number data so that we can modify the WHERE clause without affecting the query results.
the right conditionsLater, when you encounter a WHERE clause containing the character data modification function column, consider how to use the add one or two specific conditions to force the optimizer to use the index. Selecting a specific set of criteria appropriately reduces the scan rows, and enforcing the index does not affect the query results----but increases the execution speed of the query.
