Oracle Query optimization
1> Select the most efficient table name order: Put a table with fewer records on the last side of the FROM clause. 2. If there are more than 3 table join queries, then you need to select the Crosstab table (intersection table) as the underlying table, which refers to the table that is referenced by the other tables. Cause: The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In cases where multiple tables are included in the FROM clause, you must select a table with the fewest number of records to use as the base table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and sort the records, then scan the second table ( The last second table in the FROM clause), finally, all the records retrieved from the second table are merged with the appropriate records in the first table 2> the join order in the WHERE clause: oracle resolves the WHERE clause in a right-to-left order, and the conditions that can filter out the maximum number of records must be written At the end of the WHERE clause.
Avoid using the ' * ' Oracle in the 3.>SELECT clause in the parsing process, you need to query the data dictionary to convert ' * ' to all the column names in turn. 4.> using the alias of a table (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.
(column ambiguity refers to the fact that because different tables in SQL have the same column name, the SQL parser cannot determine the attribution of this column when the column appears in the SQL statement)
5.> reduce the number of accesses to the database: when executing each SQL statement, Oracle does a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload 6.> (if possible) 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 the delete command was executed. When using truncate, the rollback segment no longer holds any recoverable information. When the command runs, the data cannot be recovered. Therefore, few resources are invoked and execution time is short. (Truncate only applies when deleting records in the entire table, truncate is DDL, not DML) 7.> (if possible) use a commit as much as possible to use a commit in the program, 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. The lock obtained by the program statement c. Redo Log Buffer space D. Oracle uses a WHERE clause to replace the HAVING clause in the 8.> (if possible) to manage the internal costs of the above 3 resources, and to use the HAVING clause as little as possible, Having the result set is filtered 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. 9.> (in some cases) can be replaced by exists in. Not exists instead of in performance comparison: 1.Select * from T1 where x in (Select y to T2) 2.select * from T1 where exists (s Elect 1 from T2 where T2. y = t1.x) When T1 records are relatively small, T2 is suitable for exists (exists most cases will be used to index), when the subquery recordset is more suitable for the hour in. Cause Analysis: 1.Select * from T1 where x in (Select y from T2)
The process of execution is equivalent to:
SELECT * from T1, (select distinct y from T2) t3 where t1.x = T3.y; 2. Select * from t1 where exists (select 1 from t2 where t2.y = t1.x)
The process of execution is equivalent to:
For x in (SELECT * from T1) loop
if (exists (select 1 from t2 where t2.y = t1.x)
Then
OUTPUT the Record
End If
End Loop
So the table T1 to be completely scanned. So it can be concluded that when the T1 record is relatively small, the T2 is suitable for exists (exists most of the cases will be used to index), when the subquery recordset is very hours in more appropriate. 10.> Replace exists with table joins
An example of improving the 9th dozen optimization rules. 11.> replaces distinct EXISTS with EXISTS to make queries faster because the RDBMS core module returns results immediately after the condition of the subquery is satisfied. The distinct is sorted first, and then the same rows are removed according to the order in which they were sorted. 12.> uses an explicit cursor (CURSOR) to use an implicit cursor, two operations are performed. The first time to retrieve records, the second check too MANY ROWS this exception. An explicit cursor does not perform a second operation. 11-Example: 1. Inefficient: SELECT DISTINCT dept_no,dept_name from DEPT d,emp E WHERE d.dept_no = e.dept_no; 2. High efficiency: Select Dept_no,dept_name from DEPT D where EXISTS (SELECT * from EMP E where e.dept_no = D.dept_no); 13. Increasing efficiency with indexes typically, querying data through an index is faster than a full table scan. The Oracle optimizer uses indexes when Oracle finds the best path to execute queries and UPDATE statements.
In addition to those long or long raw data types, you can index almost all columns. Using indexes in large tables is particularly effective.
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>
Index Tuning rules:1. Do not start with a wildcard character (wildcard) in a like piece, otherwise the index will not be used. Example: SELECT lodging from lodging WHERE MANAGER like '%hanman '; 2. Avoid using the type of calculation or change indexed columns on indexed columns or using '!= ' and <>: SELECT ... From DEPT WHERE SAL * > 25000; SELECT ... From EMP WHERE Emp_type=to_char (123);
Select .... Where account_name| | Account_type= ' Amexa '; Select ... where empno!=8888; 3. Avoid using not on indexed columns. 4. 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 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. 5. Replacing or with union (for indexed columns) typically, replacing or in the 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.
Efficient:
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE loc_id = 10
UNION
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE REGION = "MELBOURNE" inefficiency:
SELECT loc_id, Loc_desc, REGION
From LOCATION
WHERE loc_id = ten OR REGION = "MELBOURNE"
If you insist on using or, then you need to return the least logged index column to the top note: where KEY1 = 10 (returns minimum records)
OR KEY2 = 20 (returns maximum Records)
ORACLE internally converts the above to
where KEY1 = ten and (not KEY1 = ten) and KEY2 = 20) 6, avoid using is null and are NOT NULL on indexed columns to avoid using any nullable column in the index, which Oracle will not be able to use ． 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.
(Recommended: You can set a default value for a field with a null value)
7. If the index is built on multiple columns, the index window needs to be placed on the first condition of the Where condition (before oracle8i), and the oracle8i is allowed after the jump index. 8. If possible) Replace union with Union-all. Union-all is to do simple merging, will not be sorted, union first do a simple merge, then do the sorting, and finally eliminate duplicate records. 9. Avoid using resource-consuming operations
An SQL statement with Distinct,union, Minus,intersect,order by will start the SQL engine. Perform the sort of resource ordering (sort) feature. Distinct requires a sort operation, while the other requires at least two sorting. For example, a union query in which each query has a GROUP BY clause, and GROUP by triggers an embedded sort (NESTED sort); In this way, each query needs to perform a sort, and then, when the union is executed, another unique sort (sort UNIQUE) operation is executed and it can only begin execution after the previous embedded sort has ended. The depth of the embedded sort will greatly affect the efficiency of the query. Typically, SQL statements with union, minus, and intersect can be overridden in other ways.
Turn from: http://blog.csdn.net/whereusejava/article/details/7733422
