Oracle SQL Statement Performance optimization (34 optimization method) _oracle

Many students on the optimization of SQL seems to know very little, recently summed up the following 34 articles for reference only.

(1) 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 Right-to-left order, the last table (driving table), which is written in the FROM clause, is processed first, and in the case where multiple tables are included in the FROM clause, you must select the table with the least number of records as the underlying table. If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.

(2) The order of joins in the WHERE clause. ：

Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

(3) Avoid the use of ' * ' in the SELECT clause:

Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent

(4) Reduce the number of accesses to the database:

Oracle has done a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, etc.

(5) Reset the arraysize parameters in Sql*plus, sql*forms and pro*c to increase the amount of retrieved data per database access, the recommended value is 200

(6) Use the Decode function to reduce processing time:

Use the Decode function to avoid repeatedly scanning the same record or repeating the same table.

(7) Simple integration, no associated database access:

If you have a few simple database query statements, you can integrate them into a single query (even if there is no relationship between them).

(8) Delete duplicate records:

The most efficient way to delete duplicate records (because of the ROWID) example:

DELETE from emp E where E.rowid > (SELECT MIN (x.rowid) from 
emp X where x.emp_no = E.emp_no);

(9) Replace Delete with truncate:

When you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state before it was deleted (accurately, before the deletion was performed) and when the truncate is applied, the rollback segment no longer holds any recoverable information. When the command is run, The data cannot be recovered. Therefore, very few resources are invoked and the execution time is short. (Translator: Truncate only applies when deleting full table, truncate is DDL not DML)

(10) Use commit as much as possible:

Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit:
Resources released by commit:

A. The information used to recover data on the rollback segment.

B. Locks obtained by program statements

C. Space in the Redo log buffer

D. Oracle to manage the internal costs of the 3 resources mentioned above

(11) Replace the HAVING clause with the WHERE clause:

Avoid the HAVING clause, which will filter the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead. On, where, and having these three clauses that can be conditionally, on is the first execution, where is the second, having the last, because on is the first to filter the records that do not meet the criteria before statistics, it can reduce the intermediate operations to deal with the data, It is supposed to be the fastest, where it should be faster than having, because it filters the data before the sum is used on the two table joins, so there is a table where there is a comparison with having. In the case of this single table query statistic, if the condition to be filtered does not involve the calculation of fields, then their result is the same, where the Rushmore technique is used, and the having is not, the slower the latter is slow if it involves a calculated field, it means that before the calculation, The value of this field is indeterminate, according to the workflow in the previous article, where the action time is done before the calculation, and the having is only after the calculation, so in this case the results will be different. On a multiple table join query, on has an earlier effect than where. First, the system is based on the join conditions between the tables, a number of tables into a temporary table, and then filtered by the where, and then calculated, after the calculation by having to filter. Therefore, to filter the conditions to play a correct role, first of all to understand how this condition should work, and then decide to put it there

(12) Reduce the query to the table:

In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table. Example:

   Select Tab_name from the TABLES where (tab_name,db_ver) = (select
tab_name,db_ver from tab_columns where VERSION = 604)

(13) Improve SQL efficiency through internal functions:

Complex SQL often sacrifices execution efficiency. It is very meaningful to master the application of the above function to solve the problem in practical work.

(14) Using table aliases (alias):

When you connect multiple tables in an SQL statement, use the alias of the table and prefix the alias with each column. This allows you to reduce parsing time and reduce syntax errors caused by column ambiguity.

(15) substituting exists instead of in, using not exists instead of in:

In many queries based on the underlying table, it is often necessary to join another table in order to satisfy one condition. In this case, using EXISTS (or not EXISTS) usually increases the efficiency of the query. In a subquery, the NOT IN clause performs an internal sort and merge. In either case, not in is the least efficient (because it performs a full table traversal of the table in the subquery). In order to avoid using not in, we can rewrite it as 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 (underlying table) WHERE EMPNO > 0 and DEPTNO in (SELECT DEPTNO from DEPT where LO C = ' Melb ')

(16) Identify the ' inefficient execution ' of the SQL statement:

While there are many graphical tools for SQL optimization at the moment, writing your own SQL tools to solve problems 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) Use Index to improve efficiency:

An index is a conceptual part of a table that is used to improve the efficiency of retrieving data, and Oracle uses a complex, b-tree structure. In general, querying data through an index is faster than full table scans. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements. It also increases efficiency when you use indexes to join multiple tables. Another advantage of using the index is that it provides uniqueness validation for the primary key (primary key) ... Those long or long raw data types, you can index almost all columns. In general, using indexes in large tables is particularly effective. Of course, you'll also find that using indexes can also improve efficiency when scanning small tables. Although the use of indexes can improve query efficiency, we must also pay attention to its cost. Indexes require space for storage and regular maintenance, and the index itself is modified whenever a record is added or subtracted from the table or the index column is modified. This means that each record's insert, DELETE, and update will pay 4, 5 more disk I/O. Because indexes require additional storage space and processing, those unnecessary indexes can slow down query response times. It is necessary to periodically refactor the index.:

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

(18) Replace distinct with exists:

Avoid using DISTINCT in the SELECT clause when submitting a query that contains a one-to-many table of information, such as a department table and an employee table. It is generally possible to consider replacing with exist, EXISTS make the query faster, because the RDBMS core module returns the result immediately after the condition of the subquery is satisfied. Example:

  (inefficient): 
SELECT DISTINCT dept_no,dept_name from DEPT D, EMP E 
WHERE d.dept_no = e.dept_no 

(efficient): 
select Dept_no,dept_name from DEPT D where EXISTS (select ' X ' from 
EMP E where e.dept_no = D.dept_no);

The SQL statement is capitalized, because Oracle always parses the SQL statement first, converts lowercase letters to uppercase, and then executes

(20) in Java code as little as possible with the connector "+" connection string!

(21) Avoid using not usually on indexed columns,

We want to avoid using not on indexed columns, not 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.

In the WHERE clause, if the indexed column is part of the function. The optimizer uses a full table scan without indexing.

Example:

Low efficiency: 

Efficient: 
SELECT ... From DEPT WHERE SAL > 25000/12;

(23) replacing > with >=

Efficient: 

Low efficiency: 

The difference is that the former DBMS will jump directly to the first dept equals 4 and the latter will first navigate to the Deptno=3 record and scan forward to the first dept greater than 3 records.

(24) Replace with union or (applicable to indexed columns)

In general, replacing or in a WHERE clause with union would have a better effect. Using or for 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, query efficiency may be reduced because you have not selected an OR. In the following example, indexes are built on both loc_id and region.

High efficiency: 
Select loc_id, Loc_desc, REGION from 
LOCATION 
WHERE loc_id = 
UNION 
Select loc_id, Loc_de SC, REGION from 
LOCATION 

Low efficiency: 
SELECT loc_id, Loc_desc, REGION from 
LOCATION 

If you insist on using or, you need to return the least recorded index column to the front.

(25) to replace or with in

This is a simple and easy to remember rule, but the actual execution effect also needs to examine, under Oracle8i, the two execution path seems to be the same.

Low efficiency: 

High-efficiency 
SELECT ... From LOCATION WHERE loc_in in (10,20,30);

(26) Avoid using is null and is not NULL on indexed columns

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 a null value.　For composite indexes, if each column is empty, the record also does not exist in the index. If at least one column is not empty, the record exists in the index. For example: If the uniqueness index is based on the columns A and B of the table, and the a,b value of a record exists in the table (123,null), Oracle will not accept the next record (insert) with the same a,b value (123,null). However, if all the indexed columns are empty, Oracle will assume that the entire key value is empty and empty is not equal to NULL. So you can insert 1000 records with the same key value, of course they're all empty! Because a null value does not exist in an indexed column, a null comparison of an indexed column in a WHERE clause causes Oracle to deactivate the index.

Inefficient: (Index invalidated) 

Efficient: (index valid) 
SELECT ... From DEPARTMENT WHERE Dept_code >=0;

(27) Always use the first column of the index:

If the index is based on multiple columns, the optimizer chooses to use the index only if its first column (leading column) is referenced by the WHERE clause. This is also a simple and important rule that when only the second column of the index is referenced, 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 outputting the final result. If you use UNION ALL instead of union, this sort is not necessary. Efficiency will be improved accordingly. It should be noted that UNION all outputs the same record in the two result sets repeatedly. So you still need to analyze the feasibility of using union all from the business requirements analysis. The UNION will sort the result set, which will use the memory of the Sort_area_size block. The optimization of this block of memory is also very important. The following SQL can be used to query for sorted consumption

Inefficiency: 
SELECT acct_num, Balance_amt from 
debit_transactions 
WHERE tran_date = ' 31-dec-95 ' 
UNION 
SELECT Acct_num, Balance_amt from 
debit_transactions 
WHERE tran_date = ' 31-dec-95 ' 

High efficiency: 
SELECT acct_num, Balance_amt from 
debit_transactions 
WHERE tran_date = ' 31-dec-95 ' 
UNION 
All SELECT Acct_num, Balance_amt 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 the order by must be contained in the same index and remain in the index.
All columns in the order by must be defined as non-null.
The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.

For example:

The table dept contains the following:

Dept_code PK not NULL
Dept_desc not NULL
Dept_type NULL

Inefficient: (index not used) 

Efficient: (using the index) 
SELECT dept_code from DEPT WHERE dept_type > 0

(30) Avoid changing the type of the indexed column.:

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 ' 

In fact, after Oracle type conversion, the statement translates to:

SELECT ... From EMP WHERE EMPNO = to_number (' 123 ') 

Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.

Now, suppose Emp_type is an indexed column of character type.

This statement is converted by Oracle to:

SELECT ... From EMP Whereto_number (emp_type) =123 

This index will not be used because of a type conversion occurring internally! To avoid an implicit type conversion of Oracle to your SQL, it is a good idea to explicitly display the type conversion. Note that when characters and numeric comparisons are compared, Oracle converts numeric types to character types preferentially

(31) The WHERE clause needs to be careful:

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, an index can only tell you what exists in the table, not what doesn't 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 indexed columns cannot be compared to each other, which will enable full table scans.

(a). The number of records in a table that retrieves more than 30% of the data. Using indexes will not improve significantly.

B. In certain situations, using an index may be slower than full table scans, but this is the same order of magnitude difference. In general, using an index is a few times or even thousands of times times more than a full table scan!

(33) Avoid using resource-consuming operations:

SQL statement with Distinct,union,minus,intersect,order by will start the SQL engine

Perform a resource-intensive sort (sort) function. Distinct requires a sort operation, while the other requires at least two times to perform the sort. Typically, SQL statements with union, minus, and intersect can be overridden in other ways. If your database sort_area_size well, use union, minus, intersect can also be considered, after all, their readability is very strong

(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 result but the second one is obviously much faster.

Inefficient: 
SELECT job, AVG (SAL) from 
EMP 
GROUP job has 
job = ' PRESIDENT ' 

Efficient: 
SELECT job, AVG (SAL) from 
EMP 
WHERE job = ' PRESIDENT ' 
OR job = ' MANAGER ' 
GROUP JOB

Original link: http://www.cnblogs.com/rootq/archive/2008/11/17/1334727.html

The above is the entire content of this article, I hope to help you learn, but also hope that we support the cloud habitat community.