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 * FROM table where hh*10>1000;//inefficient select * FROM table where hh>1000/10;//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>10;//inefficient 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 Table e      where h>500 and                       d= ' 001 ' and                  25< (select COUNT (*) from                                table                                where count=e . count);  Inefficient select * FROM Table e      where  25< (select COUNT (*) from                           table                           where count=e.count);                       and  h>500 and                   d= ' 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; 1000 times times faster than delete from account;

(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 account    WHERE Ac_code not in     (              select CODE from              GOODS              WHERE num= ' 001 ')       //Inefficient SELECT * FROM account    where not EXISTS       (SELECT CODE from          GOODS          where Code=account. Ac_code           and num= ' 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. Replace the HAVING clause with a WHERE clause, which reduces the overhead if the number of records can be restricted by a WHERE clause. (Non-Oracle) on, where, have the three clauses that can be added conditionally, on is the first execution, where the second, having the last, because on is the non-qualifying records filtered before the statistics, it can reduce the intermediate operation to process the data, It should be said that the speed is the fastest, where should also be faster than having to, because it filters the data before the sum, in two table joins only use on, so in a table, the left where and have compared. In the case of this single-table query statistics, if the conditions to be filtered do not involve the fields to be calculated, then they will be the same result, but where you can use the Rushmore technology, and have not, at the speed of the latter slow if you want to relate to the calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow of the previous write, where the action time is done before the calculation, and having is calculated after the function, so in this case, the results will be different. On a multi-table join query, on has an earlier effect than where. The system first synthesizes a temporary table based on the conditions of the joins between the tables, then the where is filtered, then calculated, and then filtered by having. Thus, to filter the conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put there.

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

(12) 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, and in the case where the FROM clause contains more than one table, you must select the table with the fewest number of record bars as the underlying 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.

(13) Integration of simple, unrelated database access:
If you have a few simple database query statements, you can integrate them into a single query (even if they are not related)

(14) Delete duplicate records:
The most efficient method of deleting duplicate records (because of the use of rowID) Example:
DELETE from EMP E WHERE e.rowid > (SELECT MIN (X.ROWID)
From EMP X WHERE x.emp_no = e.emp_no);

(15) using the alias of the table:

When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix the alias to each column. This reduces the time to parse and reduces the syntax errors caused by column ambiguity.

SQL statements are capitalized, because Oracle always parses the SQL statements first, converting lowercase letters to uppercase and then executing
(17) Use the connector "+" connection string sparingly in Java code!

(18) Always use the first column of an index:
If the index is built on more than one column, the optimizer chooses to use the index only if its first column (leading column) is referenced by a WHERE clause. This is also a simple and important rule, when referencing only the second column of an index, the optimizer uses a full table scan and ignores the 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