Oracle query optimization skills

Connection sequence in WHERE clause: ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connection between tables must be written before other WHERE conditions.

Connection sequence in WHERE clause: ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connection between tables must be written before other WHERE conditions.

1. connection sequence in WHERE clause: Oracle uses the bottom-up sequence to parse the WHERE clause. According to this principle, the connection 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.
2. avoid using '*' in the SELECT clause: ORACLE converts '*' into all column names in sequence during parsing. This is done by querying the data dictionary, this means it will take more time
3. Use the DECODE function to reduce processing time: Use the DECODE function to avoid repeated scanning of the same record or reconnecting to the same table.
4. replace DELETE with TRUNCATE: When deleting records in a table, a ROLLBACK segment is usually used to store recoverable information. if you do not have a COMMIT transaction, ORACLE will recover the data to the State before the deletion (which is precisely the State before the deletion command is executed). When TRUNCATE is used, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than DML)
5. Use COMMIT as much as possible: if possible, use COMMIT as much as possible in the program. In this way, the program's performance is improved, and the demand will be reduced due to the resources released by COMMIT:
Resources released by COMMIT:
Information used to restore data on the rollback segment.
Lock obtained by Program Statements
Space in REDO LOG BUFFER
ORACLE manages the internal costs of these three types of resources
6. use the table ALIAS (ALIAS): when connecting multiple tables in an SQL statement, use the table ALIAS and prefix the ALIAS on each COLUMN. in this way, the parsing time can be reduced and the syntax errors caused by COLUMN ambiguity can be reduced.
7. replace IN with EXISTS and not exists instead of not in: IN many basic table-based queries, to meet one condition, you often need to join another table. in this case, using EXISTS (or not exists) usually improves the query efficiency. 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.
Example:
Efficient:
SELECT * from emp (basic table) where empno> 0 and exists (SELECT 'x' from dept where dept. DEPTNO = EMP. deptno and loc = 'melb ')
Inefficiency:
SELECT * from emp (basic table) where empno> 0 and deptno in (select deptno from dept where loc = 'melb ')
8. replace DISTINCT with EXISTS: when you submit a query that contains one-to-many table information (such as the Department table and employee table), avoid using DISTINCT in the SELECT clause. in general, you can consider replacing it with EXIST, and EXISTS makes the query more rapid, because the RDBMS core module will immediately return results once the subquery conditions are met.
Example:
Inefficiency:
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 );
9. SQL statements are written in uppercase. Because ORACLE always parses SQL statements first, converts lowercase letters to uppercase and then executes them;
10. replace order by with WHERE:
The order by clause only uses indexes under two strict conditions.
All columns in order by must be included in the same index and maintained in the ORDER of the index.
All columns in order by must be defined as non-empty.
The index used BY the WHERE clause and the index used in the order by clause cannot be tied together.
For example:
The DEPT table contains the following columns:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
Inefficiency: (indexes are not used)
SELECT DEPT_CODE from dept order by DEPT_TYPE
Efficiency: (using indexes)
SELECT DEPT_CODE from dept where DEPT_TYPE> 0
11. Optimize group:
To improve the efficiency of the group by statement, you can filter out unnecessary records before group by. The following two queries return the same results, but the second query is much faster.
Inefficiency:
Select job, AVG (SAL)
FROM EMP
GROUP JOB
Having job = 'President'
Or job = 'manager'
Efficient:
Select job, AVG (SAL)
FROM EMP
Where job = 'President'
Or job = 'manager'
GROUP JOB

,