SQL optimization principles and techniques

Speeding up SQL queries is a very important technique, and it's easy to speed up SQL queries in the following ways:
I. References to Indexes

1, the index can speed up the retrieval speed of data, speed up the link between table and table, improve performance, so when the massive data processing, considering the large amount of information, the table should be indexed, including the primary key to establish clustered index, the aggregation index is established in the daily journal. There are many advantages to indexing, but for the establishment of indexes, it is necessary to consider the actual situation, instead of establishing an index for each column, such as the grouping of large tables, sorting and other fields, to establish the corresponding index, and should also consider establishing a conforming index. There are many bad aspects to adding indexes, first, it takes time to create indexes and maintain indexes, and increases as the amount of data increases; second, the index occupies the physical space, and the clustered index is much more space. Finally, when the table is added, deleted, and modified, the index should also be maintained dynamically. So the reference to the index has several principles:

(1) When inserting data is more than 10 of the data in the table, the first need to delete the index of the table to improve the efficiency of data insertion, and then re-index;

(2) Avoid using functions or calculations on an indexed column, in the WHERE clause, if the index is part of a function, the optimizer will no longer use the index and will use a full table scan, for example:

 select  *   table  where  hh*  10  >  Span style= "color: #800000; Font-weight:bold; " >1000 ; //  inefficient  select  *  from  table  where  hh1000  / 10 ; Span style= "color: #808080;" >// more efficient 

(3) Try to avoid using not,!= and <> on the index columns, the index can only tell what is in the table, not what is not in the table, when the database encounters the above several symbols, the index is no longer used, the full table scan

(4) Do not process the index in the search, such as trim,to_date, type conversion, etc., which will destroy the index

(5) Avoid using is null or is not NULL on an indexed column. Avoid using any nullable columns on the index columns, which will prevent you from using this index. Because null values do not exist in the index column, the index cannot be used when a null comparison is made to the index in the WHERE clause.

(6) In the index column, the use of >= instead of, for example:

Select *  from Table where hh>ten; // Low Efficiency Select *  from Table where hh>=10.0000001// relatively efficient

II. Optimization of SQL statements

Describes the optimization techniques for several SQL statements:

(1) The Order of links in the WHERE clause

Oracle parses the WHERE clause in a bottom-up order, so that links between tables must be written before other where conditions, and those conditions that can filter through a large number of records must be written at the end of the where sentence, for example:

Select *  from TableewhereH> -                        andD='001'                   and  -<(Select Count(*)                                 from Table                                where Count=E.Count);//Low EfficiencySelect *  from Tableewhere   -<(Select Count(*)                            from Table                           where Count=E.Count);  andH> -                    andD='001';//More efficient

(2) When deleting the whole table, use truncate instead of delete. Because truncate is DDL is not DML (truncate can only be used when deleting a full table), for example:

Truncate Table Account ; Delete  from Account ; 1000 times times faster

(3) Use commit as much as possible, and use commit for every delete,insert,update in the program, so that the system will be significantly more efficient because of the resources freed by the commit.

(4) using exists instead of in, can improve the efficiency of the query. For example:

SELECT *  from AccountWHEREAc_code not inch (              SELECTCODE fromGOODSWHERENum='001')//Low EfficiencySELECT *  from AccountWHERENotEXISTS(SELECTCODE fromGOODSWHERECODE=Account . Ac_code andNum='001')//More efficient

(5) Use GROUP by

You can filter out unwanted statements before group by

(6) Avoiding the use of the HAVING clause

The HAVING clause only filters the result set after retrieving all records, which involves sorting, counting, and so on, if you can limit the number of records through the WHERE clause, you can reduce the overhead.with whereClause substitution havingClauseIf you can get through whereclause to limit the number of records,That would reduce the cost of that. (Non-OracleIN) on, where, havingThese three clauses can be added conditionally, onIs the first to execute, where second, Having Finally, because On fast, because it filtered data after Sum On can use Rushmore technology, and having can not, in the speed of the latter to be slow if you want to relate to the calculated field, it means that the value of this field is indeterminate before calculation, according to the workflow of the previous write, Where

(7) Conditional use of Union-all instead of union for improved efficiency

(8) In the SQL statements of all queries, pay particular attention to reducing queries to tables, such as:

(9) Avoid use in select sentence *

When you want to list all columns in a select sentence, using * is a convenient, but inefficient approach, because in Oracle, it is time-consuming to query the data dictionary to convert * to all column names in turn

(ten) 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.

(11) A like statement with a wildcard (%)

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% ';

Select the most efficient table name order ( valid only in the rule-based optimizer):

The ORACLE parser processes the table names in the FROM clause in a right-to-left order, and the FROM clause is written in the final table (the underlying table, driving tables) will be processed first, in the case where the FROM clause contains more than one table , you must select the table with the lowest number of records as the base table. If you have more than 3 tables connected to the query, you need to select the crosstab (intersection table) as the underlying table, which refers to the table that is referenced by the other table.

< Span lang= "ZH-CN" > (+)   integration simple, no associated database access:  
If you have a few simple database query statements, You can integrate them into a single query (

< Span lang= "ZH-CN" > delete duplicate records:  
because rowID) example :&NBSP;
delete  from  emp e  where  e.rowid > (SELECT MIN (X.rowid)  
from  emp x  WHERE&N Bsp X.emp_no = e.emp_no);  

< Span lang= "ZH-CN" > < Span lang= "ZH-CN" > (15) :

< Span lang= "ZH-CN" > < Span lang= "ZH-CN" >,  Use the alias of the table and prefix the alias to each Column so You can reduce the parsing time and reduce the syntax errors caused by Column

< Span lang= "ZH-CN" > < Span lang= "ZH-CN" > (+) sql statement in uppercase Because Oracle Minimize the use of connectors "+" connection string!  

< Span lang= "ZH-CN" > < Span lang= "ZH-CN" > (18)   always uses the first column of an index:  
only in its first column (leading column) Where When a clause is referenced, the Optimizer chooses to use the index.   This is also a simple and important rule when referencing only the second column of an index,

Three, the function of the use of skills

Although the use of functions is sometimes arbitrary, performance is reduced, but good functions can sometimes improve performance and readability. Such as:

Some other methods can refer to the http://www.cnblogs.com/ziyiFly/archive/2008/12/24/1361380.html

SQL optimization principles and techniques