Go SQL optimization Principles

first, the question of the proposed

In the early stage of application system development, due to less development database data, the query SQL statement, complex view of the writing of the performance of the SQL statement is not good or bad, but if the application system submitted to the actual application, as the data in the database increases, The response speed of the system is one of the most important problems that the system needs to solve at present. An important aspect of system optimization is the optimization of SQL statements. For the massive data, the speed difference between the inferior SQL statement and the high-quality SQL statement can reach hundreds of times, it can be seen that a system is not simply able to achieve its function, but to write high-quality SQL statements, improve the availability of the system.

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

　　Second, the SQL statement writing attention issues

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 there are indexes on some columns, because poor SQL is written, the system cannot use the index while running the SQL statement, and it also uses a full table scan, which results in a very slow response.

1. Is null and is not NULL

You cannot use NULL as an index, and any column that contains null values will not be included in the index. Even if the index has more than one column, the column is excluded from the index as long as there is a column in the column that 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 columns

For a joined column, the optimizer does 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), for a worker's surname and name in two columns (First_Name and last_name), now to query a Bill Clinton Cliton.

Here 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 is important to note that the System optimizer does not use an index created based on last_name.

When written in this SQL statement, the Oracle system can take an index created based on last_name.

where first_name ='beill' and last_name ='cliton';

. A like statement with wildcard characters (%)

This is also the case with the above example. The current demand is such that the workers ' table should be queried for the person whose name contains Cliton. You can use the following query SQL statement:

SELECT * from the employee where last_name like '%cliton%';

This is because the wildcard character (%) appears at the beginning of the search term, so the Oracle system does not use the last_name index. In many cases it may not be possible to avoid this, but be sure to be in the bottom of your mind, so using a wildcard will slow down the query. However, when wildcards appear elsewhere in a string, the optimizer can take advantage of the index. The indexes are used in the following query:

SELECT * from the 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 special restrictions on the columns to be sorted, or it can be added to a column (like joins or additions). Any non-indexed item in the ORDER BY statement, or a computed expression, will slow down the query.

Double-check the order BY statement to find non-indexed items or expressions that degrade performance. The solution to this problem is to rewrite the order BY statement to use the index, or you can establish another index for the column you are using, and you should absolutely avoid using an expression in the ORDER BY clause.

Http://www.cnblogs.com/ziyiFly/archive/2008/12/24/1361380.html

Go SQL optimization Principles