SQL statement performance adjustment principles

[Author: Shi XiaoYu]

I. Question proposal
At the early stage of application system development, due to the relatively small amount of data in the development database, the performance of SQL statement writing is not good for querying SQL statements and writing complex views, however, after the application system is submitted to the actual application, as the data in the database increases, the system response speed becomes one of the most important problems to be solved by the system. An important aspect of system optimization is the optimization of SQL statements. 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.

In most cases, Oracle uses indexes to traverse tables faster. The optimizer 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.

Ii. Notes for writing SQL statements
The following describes the issues that need to be paid attention to when writing the WHERE clause of some 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.

1. Is null and is not null
Null cannot be used 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.

2. Join Columns

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.

The following is an SQL statement that uses join queries,

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, the system optimizer does not use the index created based on last_name.

When the following SQL statement is used, the Oracle system can use an index created based on last_name.

Select * from employee
Where
First_name = 'beill' and last_name = 'cliton ';

What should we do in the following situations? If a variable (name) stores the name of the employee Bill Cliton, how can we avoid full traversal and use indexes in this case? You can use a function to separate the surname and name in the variable name. However, note that this function cannot be used in the index column. The following is an SQL query script:

Select * from employee
Where
First_name = substr ('& name', 1, instr (' & name', '')-1)
And
Last_name = substr ('& name', instr (' & name', '') + 1)

3. Like statement with wildcard (%)

The above example shows this situation. Currently, You need to query the persons whose names contain Cliton in the employee table. The following SQL statement can be used:

Select * from employee where last_name like '% Cliton % ';

Here, because the wildcard (%) appears at the beginning of the search term, 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, the index is used:

Select * from employee where last_name like 'C % ';

4. Order by statement

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.

Check the order by statement carefully to find out non-index items or expressions, which will reduce 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. Not

When querying, we often use some logical expressions in the WHERE clause, such as greater than, less than, equal to, and not equal to. We can also use and (and), or (OR) and not (not ). Not can be used to reverse all logical operators. The following is an example of a not clause:

... Where not (status = 'valid ')

If you want to use not, brackets should be added before the phrase to be reversed, and the not operator should be added before the phrase. Not operator is included in another logical operator, which is not equal to (<>) operator. In other words, even if the not word is not explicitly added to the query WHERE clause, not is still in the operator. See the following example:

... Where status <> 'invalid ';

Let's look at the example below:

Select * from employee where salary <> 3000;

You can rewrite this query to not using not:

Select * from employee where salary <3000 or salary> 3000;

Although the results of these two queries are the same, the second query scheme is faster than the first query scheme. The second query allows Oracle to use indexes for salary columns, while the first query does not.

6. In and exists

Sometimes a column is compared with a series of values. The simplest way is to use subqueries in the where clause. Subqueries in two formats can be used in the WHERE clause.

The first format is to use the in OPERATOR:

... Where column in (select * from... where ...);

The second format is to use the exist OPERATOR:

... Where exists (select 'x' from... where ...);

I believe that most people will use the first format because it is easier to write. In fact, the second format is far more efficient than the first one. In Oracle, almost all in operator subqueries can be rewritten to subqueries using exists.

In the second format, the subquery starts with 'select. Use the exists clause to query the data extracted from the table without a pipe. It only displays the WHERE clause. In this way, the optimizer does not have to traverse the entire table, but only performs the work based on the index (Here we assume that the column used in the where statement has an index ). Compared with the in clause, exists uses connected subqueries, which is more difficult to construct than in subqueries.

By using exist, the Oracle system first checks the primary query, and then runs the subquery until it finds the first match, which saves time. When executing an in subquery, the Oracle system first executes the subquery and stores the obtained result list in a temporary table with an index. Before executing a subquery, the system suspends the primary query. After the subquery is executed, it is stored in the temporary table and then executes the primary query. This is why exists is faster than in queries.

At the same time, do not exists should be used as much as possible to replace not in, although both use not (the index cannot be used to reduce the speed), not exists is more efficient than not in query.