Writing high-performance SQL

Objective: One of the most important aspects of system optimization isoptimization of SQL statements. For massive data, inferiorSQL statements and high-qualityThe speed difference between SQL statements can be up to hundreds of times, visible for a system is not simple to achieve its function can be, but to write high-qualitySQL statements to improve the usability of the system.
In the early stage of application development, because the database data is relatively small, the querySQL statements, the writing of complex views, will not be realized at firstThe performance of the SQL statement is good or bad, but if the application system is submitted to the actual application, with the increase of data in the database, the system response speed becomes one of the most important problems that the system needs to solve at present. One of the most important aspects of system optimization isoptimization of SQL statements. For massive data, inferiorSQL statements and high-qualityThe speed difference between SQL statements can be up to hundreds of times, visible for a system is not simple to achieve its function can be, but to write high-qualitySQL statements to improve the usability 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 theof the SQL statementWritten in the WHERE clauseSQL code is unreasonable, it will cause the optimizer to delete the index and use a full table scan, generally thisSQL statements, which are called inferiorSQL statement. in writingSQL statements, we should be aware of the principles by which the optimizer removes the index, which helps to write high-performanceSQL statement.
Here are someof the SQL statementA detailed description of the issues to be noted in the WHERE clause writing. In theseIn the WHERE clause, even if some columns are indexed, the poorSQL, the system is running theSQL statements cannot use this index, and full table scanning is also used, which results in a significant reduction in response speed.
　　 1. is NULL andis not NULL
Can't useNull as an index, any containingNull-valued columns will not be included in the index. Even if the index has more than one column, as long as there is a column in the column that containsNULL, the column is excluded from the index. This means that if a column has a null value, even indexing the column does not improve performance.
Any inUsed in the WHERE clauseIs null orThe IS-NOT NULL statement optimizer is not allowed to use indexes.http://hovertree.com/menu/oracle/
　　 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 there is a staff table (Employee), for a worker's surname and name are divided into two columns (First_Name andlast_name), now to inquire about a named bill. Clinton (Bill Cliton) 's staff.
Here is a query that uses joinsSQL statement, the above statement can fully query whether there is aBill Cliton This employee, but it's important to note that the system optimizerThe index created by last_name is not used.
When using the followingSQL statements to write,Oracle systems can be used based onThe index created by the last_name.
How do you deal with this situation? If a variable (Name) is stored in theBill Cliton The name of the employee, how can we avoid the whole traversal and use the index for this situation? You can use a function to set the variableThe name is separated from the first name, but it is important to note that this function is not available on the index column. Below isSQL query script:
　　 3. With wildcard characters (%) ofLike statement
This is also the case with the above example. The current requirement is such that the query name in the staff table containsCliton of the people. You can use the following querySQL statements:
Here because of the wildcard character (%) appears at the beginning of the search term, soOracle systems do not useThe index of the last_name. 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.
　　 4. Order by statement
　　 The ORDER by statement determinesHow Oracle sorts 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 inA non-indexed entry for an Order by statement, or a computed expression, will slow down the query.
Double check.ORDER BY statements to find non-indexed items or expressions that degrade performance. The way to solve this problem is to rewriteThe order BY statement uses the index, or you can establish another index for the column you are using, and you should absolutely avoidAn expression is used in the ORDER BY clause.
　　 5. Not
When we query, we oftenThe WHERE clause uses some logical expressions, such as greater than, less than, equal to, and not equal, and so on, you can also use theand (and),or (or) andNot (non).Not can be used to negate any logical operation symbol.
If you want to useNot, you should precede the phrase with parentheses and precede the phrase with aNot operator.The NOT operator is included in another logical operator, which is the not equal to () operator. In other words, even if you are not queryingExplicit join in the WHERE clauseNot word,Not is still in the operator.
For this query, you can rewrite it to not use theNot:
Although the results of these two queries are the same, the second query scenario is faster than the first query scenario. The second type of query allowsOracle'sThe salary column uses an index, and the first query cannot use the index.
　　 6. In andEXISTS
Sometimes a column is compared to a series of values. The simplest way is toA subquery is used in the WHERE clause. InTwo forms of subqueries can be used in the WHERE clause.
The first format is the use ofIn operator; The second format is the use of theThe exist operator.
I believe most people will use the first format because it is easier to write, whereas the second format is much more efficient than the first. InOracle can use almost all of theThe IN operator clause query is overwritten with theA subquery for the exists.
in the second format, the subquery begins with the exists clauses no matter what data the subquery extracts from the table it only looks at the where clause. This way the optimizer does not have to traverse the entire table and can do its work only by indexing (assuming that the column used in the where statement has an index). In contrast to in clauses, exists uses connected subqueries, which are more difficult to construct than in subqueries.  
by using exist,oracle the system will first check the main query, The subquery is then run until it finds the first match, which saves time. oracle system executes the subquery first, and the resulting list is stored in an indexed temporary table when the in subquery is executed. Before executing a subquery, the system first suspends the primary query, and executes the subquery until it has been executed, and then performs the main query after the query is held in the temporary table. This is why using exists is usually faster than using in.  
should also use not exists as much as possible instead of not in, although both use not (cannot use index to slow down), not exists is more efficient than not in query.

< Span lang= "en-US" > < Span lang= "en-US" > < Span lang= "en-US" > recommended: http://www.cnblogs.com/roucheng/p/3504463.html

Writing high-performance SQL