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 sensitivityBefore 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, for example, the NAME column of the ADDRESS table in table 1. 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 |
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 '); |
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 |
To further increase the query speed, we can specify three or more characters in the specified "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.
Use of REPLACEJust as the name is not always in uppercase, the phone number may appear in many formats, such as 123-456-7890,123 456 7890, (123) 456-7890, and so on. If you search for the above number in the column named PHONE_NUMBER, you may need to use the REPLACE function to ensure a unified format. If the PHONE_NUMBER column contains only spaces, hyphens, and numbers, the where clause can be as follows:
| WHERE replace (phone_number, '-'), '') = '123' |
The WHERE clause uses the REPLACE function twice to remove the hyphens and spaces, ensuring that the phone number is a simple numeric string. However, this function prevents the optimizer from using indexes in the column. Therefore, modify the WHERE clause as follows to enforce the index.
| WHERE replace (phone_number, '-'), '') = '123' |
AND phone_number like '200' if we know that the data may contain parentheses, the WHERE clause will be slightly more complex. We can add the REPLACE function (remove parentheses, hyphens, and spaces) and expand the added conditions as follows:
| WHERE replace (phone_number ,'-'),''),'('),')') = '000000' AND (phone number like '000000' or phone_number like '(1234567890 ')' |
This example emphasizes the importance of cleverly selecting WHERE clause conditions, and these conditions do not change the query results. Your selection should be based on the information types that fully understand the column. In this example, 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.
Correct ConditionsWhen you encounter a WHERE clause that contains CHARACTER data to modify function columns, you should consider how to use one or two specific conditions to force the optimizer to use indexes. Selecting a set of specific conditions appropriately reduces the number of rows scanned, and the use of indexes forcibly does not affect the query results-but increases the query execution speed.
