SQL optimization series (1) _ SQL Optimization-where clause Optimization

SQL optimization series (1) _ SQL Optimization-where clause optimization 1. Why is it necessary to optimize SQL statements in an important aspect of system optimization. For massive data, the speed difference between inferior SQL statements and high-quality SQL statements can reach hundreds of times. It can be seen that a system can not simply implement its functions, instead, we need to write high-quality SQL statements to improve system availability. 2. Poor SQL statement www.2cto.com in most cases, Oracle uses indexes to traverse tables faster. The optimizer mainly improves performance based on the defined indexes. However, if the SQL code written in the where clause of the SQL statement is unreasonable, the optimizer will delete the index and use full table scan, in general, such SQL statements are so-called inferior SQL statements. When writing SQL statements, we should be clear about the optimizer's principles for deleting indexes, which helps to write high-performance SQL statements. 3. problems that may occur in the where clause. The following describes the issues that need to be paid attention to in the where clause of SQL statements. In these where clauses, even if some columns have indexes, the system cannot use these indexes when running the SQL statement because of poor SQL writing. The full table scan is also used, this greatly reduces the response speed. 3.1 The is null and is not null statements www.2cto.com cannot use null as an index. Any column containing null values will NOT be included in the index. Even if there are multiple columns in the index, as long as one of these columns contains null, this column will be excluded from the index. That is to say, if a column has a null value, even if the column is indexed, the performance will not be improved. Any statement optimizer that uses is null or is not null in the where clause cannot use indexes. 3.2 for joined columns, the optimizer does not use indexes even if the last joined value is a static value. Let's take a look at an example. Suppose there is a employee table (employee). For a employee's surname and name are divided into two columns for storage (FIRST_NAME and LAST_NAME), we want to query a table named Bill. bill Cliton employees. SQL 1: [SQL] select * from employss where first_name | ''| last_name = 'beill Cliton '; The preceding statement can be used to check whether the employee Bill Cliton exists, however, you must note that the system optimizer does not use indexes created based on last_name. SQL 2: [SQL] where first_name = 'beill' and last_name = 'cliton'; when the following SQL statement is used, the Oracle system can use an index created based on last_name. 3. The like statement with a wildcard (%) is similar to the preceding example. Currently, You need to query the persons whose names contain cliton in the employee table. You can use the following query SQL statement: SQL 1: [SQL] select * from employee where last_name like '% cliton %'. The wildcard (%) appears at the beginning of the search term, therefore, the Oracle system does not use the last_name index. This situation may not be avoided in many cases, but it must be well understood. Using wildcard characters will reduce the query speed. However, when a wildcard appears at another position of a string, the optimizer can use the index. In the following query, SQL 2: [SQL] select * from employee where last_name like 'C % '; 4. the Order by statement determines how Oracle sorts the returned query results. The Order by statement has no special restrictions on the columns to be sorted. You can also add functions to the columns (such as joining or appending ). Any non-index item or computed expression in the Order by statement will reduce the query speed. Solution: Check the order by statement carefully to find out non-index items or expressions, which will reduce the performance. To solve this problem, rewrite the order by statement to use the index. You can also create another index for the column you are using. Avoid using an expression in the order by clause. 5. SQL 1: [SQL] select * from employee where salary <> 3000; SQL 2: [SQL] select * from employee where salary <3000 or salary> 3000; although the two queries have the same results, the second query solution is faster than the first query solution. The second query allows Oracle to use indexes for salary columns, while the first query does not. Reference: 1. SQL Optimization Principles http://www.bkjia.com/database/201303/192993.html