"Go" How to optimize SQL statements

============ Recommended ============

csdn:http://blog.csdn.net/e3002/article/details/1817941

= = = How to optimize SQL statements = =(1) Select the most efficient table name order (valid only in the rule-based optimizer):    Oracle's parser processes the table name from the FROM clause in a right-to-left order, written in the last table in the FROM clause (base table  driving Table) will be processed first, and in the case of multiple tables in the FROM clause, 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,  then you need to select the Crosstab table (intersection table) as the base table,  the crosstab refers to the connection order in the table .  (2)  where clause that is referenced by the other table. :    Oracle uses a bottom-up sequential parsing where clause, according to this principle, the connection between tables must be written before other where conditions,  those conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.   (3) Avoid using '  *  ' in  select clauses:    Oracle,  ' * '   convert ' * ' to all column names in the process of parsing,  This work is done by querying the data dictionary,  This means that it will take more time   (4) to reduce the number of accesses to the database:    Oracle performs a lot of work internally:  parsing SQL statements,  Estimating the utilization of indexes,  binding variables  ,  read data blocks;  (5) Reset pro*c parameters in Sql*plus, sql*forms, and ArraySize,  You can increase the amount of data retrieved per database access &nbsp, and the recommended value for 200  (6) is to use the DECODE function to reduce processing time:     Use the Decode function to avoid duplicate scans of identical records or duplicate connections on the same table .  (7) consolidated simple, no associated database access:     If you have a few simple database query statements, You can integrate them into a single query (even if they're not)   (8) Delete duplicate records:     Most efficient method of deleting duplicate records   (  because ROWID is used) Example: DELETE from EMP E where E.rowid > (SELECT MIN (x.rowid) from EMP X WHERE x.emp_no = e.emp_no);  (9) Replace truncate with Delete:  &nbs p;  When you delete a record in a table, in general,  rollback segment (rollback segments)   to hold information that can be restored .  if you don't have a COMMIT transaction, Oracle restores the data to the state before it was deleted (to be exact, before the delete command was restored)   and when the truncate is applied, the,  rollback segment no longer holds any recoverable information. When the command runs, the data cannot be restored. So very few resources are called, The execution time will also be very short. (Translator Press: truncate only in Delete full table applies, truncate is DDL not DML)   (10) Use commit:     as much as possible, use commit as much as you can in the program,   The performance of the program is improved, and the demand is reduced by the resources freed by commit: The resource freed by commit:a.  the information used to recover data on the rollback segment .b.  the lock C obtained by the program statement. Redo the space D in log buffer . Oracle manages internal spending in 3 of these resources   (11) replaces the HAVING clause with a WHERE clause:     avoid having a HAVING clause, having  The result set is filtered only after all records have been retrieved .  this process requires sorting, totals, and so on .  if you can limit the number of records through the WHERE clause, you can reduce this overhead. (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 statistic, if the conditions to be filtered are not related to the fields to be computed, then the results are the same, just where you can use RUSHMOre technology, and having no, in the speed of the latter to be slow if you want to relate to the calculated field, it means that the value of the field is indeterminate before calculation, according to the workflow of the previous write, where the action time is done before the calculation, and having is the function after the calculation, 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 conditions to play the right role, first of all to understand when this condition should play a role, and then decided to put it there   (12) Reduce the query on the table:     in the SQL statement that contains the subquery, Pay particular attention to reducing queries on tables. Example: SELECT tab_name from TABLES where (tab_name,db_ver) = (Selecttab_name,db_ver from tab_columns where Versi On = 604)   (13) Improve SQL efficiency with intrinsic functions .:     complex SQL often sacrifices execution efficiency .  the ability to master the above application function to solve the problem is very meaningful in practical work.   (14) using table aliases (alias):     when you concatenate multiple tables in an SQL statement,  use the alias of the table and prefix the alias to each column. In this way, You can reduce the parsing time and reduce the syntax errors caused by column ambiguity .  (15) Replace in with exists, replace not in:     with not exists, and in many base table-based queries, In order to satisfy one condition, it is often necessary to join another table. In this case,  using EXISTS (or not EXISTS) will typically improve the efficiency of the query .  in the subquery, the NOT IN clause will perform an internal sort and merge .  In either case, not in is the least effective   (because it performs a full table traversal of the table in the subquery) .  to avoid using not, we can change it to an outer join (Outer Joins) or not EXISTS. Example: (efficient) SELECT * from EMP(base table) where EMPNO > 0 and EXISTS (SELECT ' X ' from DEPT WHERE DEPT. DEPTNO = EMP. DEPTNO and LOC = ' Melb ') (inefficient) SELECT * from EMP (base table) WHERE EMPNO > 0 and DEPTNO in (Selectdeptno from DEPT WHERE LOC = ' M ELB ')   (16) Identify SQL statements for ' inefficient execution ':     although there are many graphical tools for SQL optimization, But writing your own SQL tool to solve the problem is always the best approach: SELECT executions, disk_reads, Buffer_gets, ROUND ((buffer_gets-disk_reads)/buffer_gets , 2) Hit_radio, ROUND (disk_reads/executions,2) Reads_per_run,sql_text from V$sqlarea WHERE executions>0 and BUFFER_ GETS > 0 and (buffer_gets-disk_reads)/buffer_gets < 0.8 ORDER by 4 desc;  (17) Improve efficiency with indexes:     An index is a conceptual part of a table used to improve the efficiency of retrieving data, and Oracle uses a complex self-balancing b-tree structure .  Typically, querying data by index is faster than full table scanning .  when Oracle finds the best path to execute queries and UPDATE statements The Oracle Optimizer uses index .  to also increase efficiency by using indexes when joining multiple tables .  Another advantage of using indexes is that it provides uniqueness validation of the primary key (primary key): Those long or long raw data types,  You can index almost all of the columns .  typically,  using indexes in large tables is particularly effective .  of course, you will also find that when you scan a small table, the use of indexes can also improve efficiency.   Although the use of indexes can improve the efficiency of query, but we must also pay attention to its cost .  indexes need space to store, also need regular maintenance, &NBSP; The,  index itself will be modified whenever there is a record in the table or the index column is modified .  This means that each record's insert, DELETE, Update will pay 4 more, 5  disk I/O .  Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. It is necessary to periodically refactor the index.: ALTER index <INDEXNAME> REBUILD <TABLESPACENAME>  (18) Replace with exists distinct:      when you submit a query that contains one-to-many table information (such as departmental and employee tables), avoid using distinct.  in the SELECT clause to generally consider replacing with exist, exists  make the query faster, Because the RDBMS core module will return the result immediately after the condition of the subquery is satisfied .  example: (inefficient): SELECT DISTINCT dept_no,dept_name from DEPT D, EMP ewhere d.dept_no = e.de Pt_no (Efficient): Select Dept_no,dept_name from DEPTD where EXISTS (select ' X ' from EMP E WHERE e.dept_no = d.dept_no);  (1 9)  sql statements are capitalized; Because Oracle always parses the SQL statements first, the lowercase letters are converted to uppercase   (20) in the Java code as little as possible with the connector "+" connection string!

• 21) Avoid using not on index columns typically,
  We want to avoid using not on indexed columns, and not to have the same effect as using functions on indexed columns. When Oracle "encounters" not, he stops using the index instead of performing a full-table scan.
  
  (22) Avoid using calculations on indexed columns.
  Where clause, if the index column is part of a function. The optimizer will use a full table scan without using an index. Example: Inefficient:

SELECT ... From DEPT WHERE SAL * A> 25000; Efficient: SELECT ... From DEPT WHERE SAL > 25000/ A; (23) Replace > with >=
Efficient:
SELECT * from EMP WHERE DEPTNO >=4 Inefficient:
SELECT * from EMP WHERE DEPTNO >3 The difference is that the former DBMS will jump directly to the first record dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3.

(24) Replace or with union (for indexed columns)
In general, replacing or in a WHERE clause with Union will have a good effect. Using or on an indexed column causes a full table scan. Note that the above rules are valid only for multiple indexed columns. If a column is not indexed, the query efficiency may be reduced because you did not select or. In the following example, indexes are built on both loc_id and region. Efficient: Select loc_id, Loc_desc, region from location WHERE loc_id = Ten UNION SELECT loc_id, Loc_desc, region from Locatio  N WHERE region = "MELBOURNE" inefficient: Select loc_id, Loc_desc, region from location WHERE loc_id = ten OR region = "MELBOURNE" If you persist in using or, you need to return the least logged index column to the front.

(25) Replace or with in
This is a simple and easy-to-remember rule, but the actual execution effect has to be tested, and under Oracle8i, the execution path seems to be the same.
Inefficient: SELECT .... From location WHERE loc_id = ten or loc_id = or loc_id = 30 Efficient Select ... From location WHERE loc_in in (10,20,30);

(26) Avoid using is null and is not NULL on an indexed column
To avoid using any nullable columns in the index, Oracle will not be able to use the index. For single-column indexes, this record will not exist in the index if the column contains null values.　For composite indexes, if each column is empty, the same record does not exist in the index. If at least one column is not empty, the record exists in the index. For example, if a uniqueness index is established on column A and column B of a table, and the table has a value of a, a and a record of (123,null), Oracle will not accept the next record (insert) with the same A, B value (123,null). However, if all the index columns are empty, Oracle will assume that the entire key value is empty and null is not equal to NULL. So you can insert 1000 records with the same key value, of course they are empty! Because null values do not exist in the index column, a null comparison of indexed columns in the WHERE clause causes Oracle to deactivate the index.
Inefficient: (index invalidation) SELECT ... From DEPARTMENT WHERE dept_code are not NULL; Efficient: (index valid) SELECT ... From DEPARTMENT WHERE Dept_code >=0;

(27) 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

(28) Replace union with Union-all (if possible):
When the SQL statement requires a union of two query result sets, the two result sets are merged in a union-all manner and then sorted before the final result is output. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will therefore be improved. It is important to note that the UNION all will output the same record in the two result set repeatedly. So you still have to analyze the feasibility of using union all from the business requirements. The UNION will sort the result set, which will use the memory of the sort_area_size. The optimization of this memory is also very important. The following SQL can be used to query the consumption of sorts
Low efficiency:

·

From Debit_transactions

From debit_transactions WHERE tran_date = ' 31-dec-95 '

·

(29) Where to replace order by:
The ORDER by clause uses the index only under two strict conditions.
All columns in an order by must be in the same index and remain in the order in which they are arranged in the index.
All columns in the ORDER by must be defined as non-empty.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.
For example: Table Dept contains the following:
Dept_code PK not NULL
Dept_desc not NULL
Dept_type NULL
Inefficient: (Index not used) Select Dept_code from DEPT ORDER by Dept_type Efficient: (using index) Select Dept_code from DEPT WHERE dept_type > 0

(30) Avoid changing the type of indexed columns:
Oracle automatically makes simple type conversions to columns when comparing data of different data types.
Suppose Empno is an indexed column of a numeric type. SELECT ... From EMP WHERE EMPNO = ' 123 ' Actually, after the Oracle type conversion, the statement is converted to: SELECT ... From EMP where EMPNO = To_number (' 123 ') Fortunately, the type conversion did not occur on the index column, and the use of the index was not changed. Now, suppose Emp_type is an indexed column of a character type. SELECT ... From EMP WHERE Emp_type = 123 This statement is converted by Oracle to: SELECT ... From EMP Whereto_number (emp_type) =123 This index will not be used because of the type conversions that occur internally! To avoid the implicit type conversion of your SQL by Oracle, it is best to explicitly express the type conversions. Note When you compare characters to numeric values, Oracle takes precedence in converting numeric types to character type 31) to Beware of WHERE clauses:
The WHERE clause in some SELECT statements does not use an index. Here are some examples. In the following example, (1) '! = ' will not use the index. Remember, the index can only tell you what exists in the table, not what does not exist in the table. (2) ' | | ' is a character join function. As with other functions, the index is deactivated. (3) ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated. (4) The same index columns cannot be compared to each other, which will enable full table scanning.
A. If the number of records in a table that has more than 30% data is retrieved. Using indexes will not be significantly more efficient.
B. In certain situations, using an index may be slower than a full table scan, but this is the same order of magnitude difference. In general, the use of indexes than the full table scan to block several times or even thousands of times!
(33) Avoid using resource-intensive operations:
SQL statements with Distinct,union,minus,intersect,order by will start the SQL engine to perform the resource-intensive sorting (sort) function. Distinct requires a sort operation, while the others need to perform at least two sorting. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database is well-sort_area_size, using union, minus, intersect can also be considered, after all, they are very readable
(34) Optimize GROUP by:
Increase the efficiency of the group BY statement by filtering out unwanted records before group by. The following two queries return the same results but the second one is significantly faster. • Low efficiency:

· SELECT JOB, AVG (SAL)

· From EMP GROUP job has job = ' president ' OR job = ' MANAGER ' efficient:

· SELECT JOB, AVG (SAL)

· From EMP

· WHERE JOB = ' President '

· OR job = ' MANAGER ' GROUP job

·

Let's take a look at Oracle's execution process

Analyze how an SQL statement works inside the orcle.
A, the user issued a SQL request, open the cursor;
B, the SQL statement parsing, execution plan, data dictionary and other information into the shared pool in memory;
C, from the data file to read the relevant data block into the data buffer;
D, do the corresponding operation, if modified, first add row-level lock, after confirmation, the change before and after the record contents into the Redo log buffer;
E, returns the result to the user, closes the cursor.
NOTE: The SQL statement is case sensitive, and the same statement, if case
, Oracle needs to analyze the execution two times, after each sentence must end with ";".

"Go" How to optimize SQL statements