SQL Statement Performance Tuning principle

Performance | Statement One, the problem of the proposed
In the early stage of the application system development, because the development database data is few, to query the SQL statement, the complex view compilation and so on cannot realize the SQL statement various writing performance good or bad, but if the application system submits the actual application, as the data in the database increases, The response speed of the system becomes one of the most important problems that the system needs to solve at present. A very important aspect of system optimization is the optimization of SQL statements. For massive data, poor SQL statements and high-quality SQL statements between the speed difference can be hundreds of times, visible for a system is not simply to achieve its function can be, but to write high-quality SQL statements, improve system availability.

In most cases, Oracle uses indexes to traverse tables more quickly, and the optimizer mainly improves performance based on defined indexes. However, if the SQL code written in the WHERE clause of the SQL statement is unreasonable, it causes the optimizer to delete the index and use a full table scan, which is generally the so-called poor SQL statement. When writing SQL statements, we should be aware of the principles by which the optimizer deletes indexes, which helps to write high-performance SQL statements.

Two, the SQL statement writes notes the question
The following is a detailed description of the issues that need to be noted in writing the WHERE clause of some SQL statements. In these where clauses, even if some columns are indexed, the system cannot use the index while running the SQL statement because of poor SQL writing, which results in a significant decrease in response speed.

1 is null and is not NULL
cannot be indexed with NULL, and any column that contains null values will not be included in the index. Even if there are multiple columns in the index, the column is excluded from the index as long as one of the columns contains null. This means that if a column has a null value, even indexing the column does not improve performance.

Any statement optimizer that uses is null or is not NULL in the WHERE clause is not allowed to use the index.

2. Join column

For columns with joins, the optimizer will not use the index even if the last join value is a static value. Let's take a look at an example, assuming that there is a staff table (employee), which is divided into two columns for the surname and name of a worker (First_Name and Last_Name), and is now inquiring about a worker named Bill Cliton.

The following is an SQL statement that takes a join query.

SELECT * FROM Employss
where
first_name| | ' | | last_name = ' Beill Cliton ';

The above statement can be used to find out if there is a bill Cliton this employee, but it should be noted that the system optimizer is not using the index created on last_name.

When the following SQL statement is written, the Oracle system can take an index based on last_name.

Select * FROM Employee
where
first_name = ' Beill ' and last_name = ' Cliton ';

How do you deal with the situation below? If the name of Bill Cliton is stored in a variable (name), how do we avoid the entire traversal and use the index? You can use a function to separate the last name from the name in the variable name, but one thing to note is that this function does not work on an indexed column. Here is the 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 character (%)

This is also seen in the example above. The current demand is such that the list of employees should be queried in the name of the person containing the Cliton. You can use the following query SQL statement:

SELECT * FROM employee where last_name like '%cliton% ';

Because the wildcard character (%) appears at the top of the search word, the Oracle system does not use the last_name index. This may not be avoided in many cases, but it must be in the bottom of the mind that using wildcards like this can slow down the query. However, when wildcards appear in other positions in the string, the optimizer can take advantage of the index. The indexes are used in the following query:

SELECT * FROM employee where last_name like ' c% ';

4. ORDER BY statement

The order BY statement determines how Oracle will sort the returned query results. The ORDER BY statement has no particular restrictions on the columns to be sorted, or you can add functions to a column (such as joins or additions). Any non-indexed entry or a calculated expression in an order BY statement will slow down the query.

Careful examination of the order BY statement to find non indexed entries or expressions can degrade performance. The solution to this problem is to rewrite the order BY statement to use the index, or you can create another index for the column you are using, and you should absolutely avoid using an expression in the ORDER BY clause.

5. Not

We often use some logical expressions in the WHERE clause when querying, such as greater than, less than, equal to, not equal to, and so on, and you can also use and (with), or or, and not (non). Not can be used to reverse any logical operation symbol. The following is an example of a NOT clause:

. where not (status = ' VALID ')

If you want to use not, precede the reversed phrase with parentheses and precede the phrase with the NOT operator. The not operator is contained in another logical operator, which is not equal to the (<>) operator. In other words, if the not word is not explicitly added to the query where clause, not still in the operator, see the following example:

. Where status <> ' INVALID ';

Let's look at the following example:

SELECT * FROM Employee where salary<>3000;

For this query, you can override not to use not:

SELECT * FROM employee where salary<3000 or salary>3000;

Although the results of both queries are the same, the second query scenario is faster than the first query scenario. The second query allows Oracle to use indexes on salary columns, while the first query cannot use indexes.

6. In and Exists

Sometimes a column is compared to a series of values. The easiest way to do this is to use subqueries in the WHERE clause. You can use subqueries in the WHERE clause in both formats.

The first format is the use of the in operator:

... where column in (SELECT * from ... where ...);

The second format is the use of the exist operator:

... where exists (select ' X ' from ... where ...);

I'm sure most people will use the first format because it's easier to write, but actually the second format is far more efficient than the first. In Oracle, almost all in-operation subcode queries can be rewritten to use exists subqueries.

In the second format, the subquery starts with ' SELECT ' X '. Use the EXISTS clause to view only the WHERE clause, regardless of what data the subquery extracts from the table. The optimizer does not have to traverse the entire table and work on the index only (assuming that the columns used in the WHERE statement have indexes). In contrast to the in clause, exists is more difficult to construct than in subquery by using a concatenated subquery.

By using the Exist,oracle system, the main query is checked first, and then the subquery is run until it finds the first match, which saves time. When an Oracle system executes an in subquery, it executes the subquery first and stores the resulting list in an indexed temporary table. Before executing the subquery, the system suspends the main query before executing the subquery, and then executes the main query after it is stored in the temporary table. This is why the use of exists is faster than in-usual queries.

You should also use not exists as much as possible instead of not in, although both are using not (cannot use the index to reduce the speed), not exists is more efficient than the not in query.