SQL Optimization (Oracle)-Part III Summary of SQL optimizations

The third part of SQL optimization summary
1. Optimization of general principles
2. Specific considerations

1. General principles of SQL optimization
1) Target: Reduce server resource consumption (mainly disk IO)
2) Design:
1. Rely on the Oracle optimizer as much as possible
2. Appropriate index (data repeat large column do not resume binary tree index, you can use the bitmap index; tables with frequent data operations, indexes need to be rebuilt periodically to reduce failed indexes and fragmentation)
3) Code:
1. Using the index
2. Reasonable use of temporary tables
3. Avoid writing overly complex SQL;
4. Minimize the granularity of transactions

2. Specific considerations
1) Use the identified column names as much as possible when querying
2) use as few nested subqueries as possible, which consumes CPU resources
3) When querying multiple tables, select the most efficient table name order
　　　 The Oracle parser processes the table from right to left, so the table with fewer records is placed on the right (the rightmost table is the base table, drivering table is processed first), and if more than 3 tables join the query, select the crosstab as the base table
4) or more than multiple queries, use union ALL (try to use UNION all instead of union) Junction (for indexed columns)
5) Commit the transaction as much as possible, release the resources, unlock, release the log in time
6) Frequently accessed tables can be placed in memory
7) Avoid complex multi-table associations
8) Avoid distinct,union (and set), minus (difference), intersect (intersection), order by, and other resource-intensive operations, because the resource-intensive sorting function is performed
9) Replace distinct with exists
Select C.distinct c.classname, C.classid, Classno from student S, Class C where s.classno= C.classno;
--Replaced by
Select ClassName, ClassID, Classno from class C where exists (SELECT * from student s where S.classno = C.classno);

Delete can be rolled back without commit, not rollback after truncate, short execution time
11) Use the alias of the table to reduce the parsing time
exists and in the choice of the question, different times differentiated treatment
13) Fair use index

SQL Optimization (Oracle)-Part III Summary of SQL optimizations