Oracle statement optimization tips can be used as a reference to make your oracle operation more efficient.
Oracle statement optimization tips can be used as a reference to make your oracle operation more efficient.
1. The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) will be processed first. When the FROM clause contains multiple tables, You must select the table with the least number of records as the base table.
For example:
Table TAB1 16,384 records
Table TAB2 1 record
Select TAB2 as the base table (the best method)
Select count (*) from tab1, tab2 execution time 0.96 seconds
Select TAB2 as the base table (poor method)
Select count (*) from tab2, tab1 execution time 26.09 seconds
2. ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example:
(Inefficient, execution time: 156.3 seconds)
SELECT...
FROM EMP E
Where sal> 50000
And job = 'manager'
AND 25 <(select count (*) FROM EMP
Where mgr = E. EMPNO );
(Efficient, execution time: 10.6 seconds)
SELECT...
FROM EMP E
WHERE 25 <(select count (*) FROM EMP
Where mgr = E. EMPNO)
And sal> 50000
And job = 'manager ';
3. Avoid '*' in the SELECT clause '*'
When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. Unfortunately, this is a very inefficient method. In fact, ORACLE converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, which means it takes more time.
4. Use the DECODE function to reduce processing time. Use the DECODE function to avoid repeated scanning of the same record or joining the same table.
5. Delete duplicate records
The most efficient method for deleting duplicate records (because ROWID is used)
DELETE FROM EMP E
Where e. ROWID> (select min (X. ROWID)
FROM EMP X
Where x. EMP_NO = E. EMP_NO );
6. Calculate the number of records
In contrast, count (*) is faster than count (1 ).
The column count is still the fastest. For example, COUNT (EMPNO)
7. Replace HAVING clause with WHERE clause
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved.
This process requires sorting, total, and other operations. If the WHERE clause can be used to limit the number of records, this overhead can be reduced.
For example:
Inefficient
Select region, AVG (LOG_SIZE)
FROM LOCATION
GROUP BY REGION
Having region! = 'Sydney'
And region! = 'Perth'
Efficient
Select region, AVG (LOG_SIZE)
FROM LOCATION
Where region! = 'Sydney'
And region! = 'Perth'
GROUP BY REGION
The conditions in HAVING are generally used to compare some set functions, such as COUNT. In addition, general conditions should be written in the WHERE clause.
8. Reduce table queries
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.
For example:
Inefficient
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = (SELECT TAB_NAME
FROM TAB_COLUMNS
Where version = 604)
AND DB_VER = (SELECT DB_VER
FROM TAB_COLUMNS
Where version = 604)
Efficient
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME, DB_VER)
= (SELECT TAB_NAME, DB_VER)
FROM TAB_COLUMNS
Where version = 604)
9. Use the table ALIAS
When connecting multiple tables in an SQL statement, use the table alias and prefix the alias on each Column. This
As a result, the parsing time can be reduced and the syntax errors caused by Column ambiguity can be reduced.
(Column ambiguity refers to the fact that different SQL tables have the same Column name. When this Column appears in an SQL statement, the SQL parser cannot determine the attribute of this Column)
10. Replace IN with EXISTS
In many basic table-based queries, to meet one condition, you often need to join another table. Here
In this case, using EXISTS (or not exists) usually improves the query efficiency.
Inefficiency:
SELECT *
From emp (basic table)
Where empno> 0
And deptno in (SELECT DEPTNO
FROM DEPT
Where loc = 'melb ')
Efficient:
SELECT *
From emp (basic table)
Where empno> 0
And exists (SELECT 'x'
FROM DEPT
Where dept. DEPTNO = EMP. DEPTNO
And loc = 'melb ')
11. replace not in with NOT EXISTS
IN a subquery, the not in Clause executes an internal sorting and merging. IN either case, not in is the most inefficient (because it executes a full table traversal for the table IN the subquery ). To avoid the use of not in, we can rewrite it into an Outer join (Outer Joins) or not exists.
For example:
SELECT...
FROM EMP
WHERE DEPT_NO not in (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT = 'A ');
To improve efficiency. Rewrite:
(Method 1: efficient)
SELECT ....
From emp a, DEPT B
Where a. DEPT_NO = B. DEPT (+)
And B. DEPT_NO IS NULL
And B. DEPT_CAT (+) = 'A'
(Method 2: most efficient)
SELECT ....
FROM EMP E
Where not exists (SELECT 'x'
FROM DEPT D
Where d. DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A ');
12. Replace EXISTS with table join
Generally, table join is more efficient than EXISTS.
SELECT ENAME
FROM EMP E
Where exists (SELECT 'x'
FROM DEPT
WHERE DEPT_NO = E. DEPT_NO
AND DEPT_CAT = 'A ');
(More Efficient)
SELECT ENAME
From dept d, EMP E
Where e. DEPT_NO = D. DEPT_NO
AND DEPT_CAT = 'a ';
13. Replace DISTINCT with EXISTS
When you submit a query that contains one-to-multiple table information (such as the Department table and employee table ),
Use DISTINCT. Generally, you can consider replacing it with EXIST.
For example:
Inefficiency:
Select distinct DEPT_NO, DEPT_N
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 );
14. Avoid using computation on index Columns
In the WHERE clause, if the index column is part of the function. The optimizer uses full table scan instead of indexing.
Example:
Inefficiency:
SELECT...
FROM DEPT
Where sal * 12> 25000;
Efficient:
SELECT...
FROM DEPT
Where sal> 25000/12;
: This is a very practical rule, please remember
