SQL optimization policy

These are a combination of online materials for your reference. Welcome to add ~

1. Select the most efficient table name sequence (only valid in the rule-based Optimizer)

First, set the rule optimizer and use the statement:
Alter system set optimizer_mode = rule;
The Oracle parser processes the table names in the from clause in the order from right to left, so the from clause is written in the final table (we call it the driving table or 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. 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, and then scan the second table (the last second table in the from clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

2. The connection sequence in the WHERE clause .:
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.

3. Avoid '*' in the select clause '*':
During the parsing process, Oracle converts '*' into all column names in sequence. This is done by querying the data dictionary, which means it takes more time.

4. Replace having clause with the 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. The system first combines multiple tables into a temporary table based on the join conditions between tables, then filters them by where, then computes them, and then filters them by having after calculation.

5. 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.

6. 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 ')
(Inefficient) Select * from EMP (basic table) Where empno> 0 and deptno in (select deptno from Dept where loc = 'melb ')

7. Use the decode function to reduce the processing time:
You can use the decode function to avoid repeated scan of the same record or join the same table.

8. Delete duplicate records:
The most efficient way to delete duplicate records (because rowid is used) is as follows:
Delete from EMP e where E. rowid> (select Min (X. rowid)
From emp x where X. emp_no = E. emp_no );

9. Reduce table queries:
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table. Example:
Select tab_name from tables where (tab_name, db_ver) = (select
Tab_name, db_ver from tab_columns where version = 604)

10. SQL statements in uppercase:
Because Oracle always parses SQL statements first, converts lowercase letters into uppercase letters, and then executes

11. Try to use the connector "+" to connect strings in Java code!

12. Replace with> =>
Efficient:
Select * from EMP where deptno> = 4
Inefficiency:
Select * from EMP where deptno> 3
The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose deptno is = 3 and scan forward to the record whose first DEPT is greater than 3.

13. 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 by job
Having job = 'President'
Or job = 'manager'
Efficient:
Select job, AVG (SAL)
From EMP
Where job = 'President'
Or job = 'manager'
Group by job

14. Try to use commit as much as possible:
As long as possible, use commit as much as possible in the program, so that the program's performance is improved, and the demand will also be reduced by the resources released by commit:
Resources released by commit:
A. Information used to restore data on the rollback segment.
B. Locks obtained by Program Statements
C. Space in redo log Buffer
D. Oracle manages the internal costs of the above three types of resources

15. Reduce the number of database accesses:
Oracle has performed a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks;