Tutorials to .com

Tutorials to .com » Database » Oracle » Oracle database mandatory Index

Oracle database mandatory Index

Print View , by: iSee ,Total views: 15 ,Word Count: 1236 ,Date: Fri, 21 Aug 2009 Time: 3:58 AM

When the where clause of a column using the function, unless the use of this simple index of the technology mandatory, otherwise oracle optimizer can not use the index in the query.

Under normal circumstances, if not used in WHERE clause, such as UPPER, REPLACE or SUBSTRD function, etc., we can not establish a specific list of specified conditions. However, if the use of these functions, there will be a problem: these functions will hinder the list of Oracle optimizer to use index, and therefore the use of the index compared to the situation, query takes more time.

Fortunately, if the function in the use of these column contains character data, such an approach can be modified query in order to achieve the mandatory use of the index, to more effectively run the inquiry. This article describes the technology involved and describes the two typical cases in how to achieve.

Mixed-case situation

Function in the discussion to amend the list as a result of the content, how to enforce the use of the index before, let us first see why Oracle optimizer in this case can not use the index. Assume that we want to search for a case that contains a mixture of data, such as in Table 1 in the NAME column ADDRESS table. Because the data is entered by the user, we can not use capital letters have been replaced by a unified data. In order to find a john for each address, we use UPPER clause contains the query. As follows:

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

Run this query in the former, if we run the command "set autotrace on", will be following the results of the implementation process includes:

ADDRESS -------------------- cleveland 1 row selected. Execution Plan -------------------- SELECT STATEMENT TABLE access FULL ADDRESS

Can see that in this case, Oracle optimizer ADDRESS table made of a full scan, and did not use the NAME column of the index. This is because the index is based on the data out to establish the actual value, and UPPER functions convert characters at National Cheng Kung University has written, that is, modify the value, so the query can not use the index of this column. Optimizer can not be compared with the index of "JOHN", there is no index entry corresponding to "JOHN" - only "john".

Fortunately, in this case, if you want to enforce the use of the index, there is a simple way: as long as the increase in the WHERE clause in one or more specific conditions, the index value for testing and reduce the need to scan the line , but did not modify the original conditions in the SQL code. As an example the following query:

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

The use of this query (has been set up AUTOTRACE), 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

Now, the optimizer for the WHERE clause AND link the two statements a statement of each scan to determine the scope of ---- the second statement does not refer to function, and thus the use of the index. After scanning in the two areas, the results will be combined.

In this example, if the database has hundreds of lines can be used to expand the following WHERE clause to further reduce the scan 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');

Obtained the same results with the previous, but its implementation is as follows, indicating that there are four scanning range.

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

If the attempt to further improve the query speed, we can in a specific "name like" the conditions specified in 3 or more characters. However, this would make a very heavy WHERE clause. Case because of the need for all possible combinations of characters-joh, Joh, jOh, joH and so on. In addition, designate one or two characters have been enough to accelerate the speed of the query.

Now let us look at, when we invoke a different function, how to use this basic technology.

The case of the use of REPLACE

As the name of capital input is not always to the same phone number will also appear in many formats: such as 123-456-7890, 123 456 7890, (123) 456-7890 and so on.

If the search PHONE_NUMBER listed as the above-mentioned number, you may need to use the REPLACE function to ensure that a unified format. If the column contains only PHONE_NUMBER spaces, hyphens and numbers, where clause can be as follows:

WHERE replace (replace (phone_number, '-'), '') ='1234567890 '

WHERE clause was used twice by the REPLACE function to remove spaces and hyphens to ensure that the phone number is a simple string figures. However, the function to prevent the optimizer use the index in the list. Therefore, we modified the following WHERE clause, to enforce the index.

WHERE replace (replace (phone_number, '-'), '') ='1234567890 'AND phone_number like'123%'

If we know that the data may contain parentheses, WHERE clause will be a little bit more complex. We can increase the REPLACE function (remove the parentheses, hyphens and spaces), as follows to increase the expansion of the conditions:

WHERE replace (replace (replace (replace (phone_number, '-'), ''), '('), ')') ='1234567890 'AND (phone number like' 123% 'or phone_number like' (123% ' ) '

The case highlighted the WHERE clause cleverly chosen the importance of the conditions, and these conditions will not change the results. Your choice should be based on the column is fully aware of the existence of the type of information. In this example, we need to know the existence of several PHONE_NUMBER data in different formats, so that we can modify the WHERE clause will not affect the search results.

The right conditions

When you encounter after the data modifications CHARACTER function contains the WHERE clause out, we should consider how to increase the use of one or two specific conditions, forcing the optimizer to use index. Appropriate to choose a specific set of conditions can reduce the scanning line, and the mandatory use of the index will not affect the query results ---- but improved query execution speed.

(Editor: Heiner rivers qlmal11268@hotmail.com TEL: (010) -68476606-8007)




Oracle Tutorial Articles


Can't Find What You're Looking For?


Rating: Not yet rated

Comments

No comments posted.