Oracle SQL Performance Optimization (III)

8. Use the decode function to reduce processing time
You can use the decode function to avoid repeated scan of the same record or join the same table.
For example:
Select count (*), sum (SAL) from EMP where dept_no = 0020 and ename like 'Smith % ';
Select count (*), sum (SAL) from EMP where dept_no = 0030 and ename like 'Smith % ';
You can use the decode function to efficiently get the same result.

Select count (decode (dept_no, 0020, 'x', null ))
D0020_count, count (decode (dept_no, 0030, 'x', null ))
D0030_count, sum (decode (dept_no, 0020, Sal, null) d0020_sal,
Sum (decode (dept_no, 0030, Sal, null) d0030_sal from EMP where ename like 'Smith % ';
Similarly, the decode function can also be used in the group by and order by clauses.
　　
9. Simple Integration with no associationDatabase


Access
If you have several simple database query statements, you can integrate them into a single query (even if there is no relationship between them)
For example:
Select name from EMP where emp_no = 1234;
Select name from DPT where dpt_no = 10;
Select name from cat where cat_type = 'rd ';
The preceding three queries can be merged into one:

Select E. Name, D. Name, C. name from cat c, dpt d, emp e, dual X where
Nvl ('x', X. Dummy) = nvl ('x', E. rowid (+) and nvl ('x', X. Dummy) =
Nvl ('x', D. rowid (+ ))
And nvl ('x', X. dummy) = nvl ('x', C. rowid (+) and E. emp_no (+) = 1234 and D. dept_no (+) = 10 and C. cat_type (+) = 'rd ';
(Translator's note: although this method is adopted, the efficiency is improved, but the readability of the program is greatly reduced, so readers still need to weigh the advantages and disadvantages)

10. 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 );
　　
11. Replace Delete with truncate

When deleting records in a table, a rollback segment is usually used to store information that can be recovered.
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,
The rollback segment no longer stores any information that can be recovered. After the command is run, the data cannot be restored. Therefore, a small number of 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)
　　
12. 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
(Translator's note: when using commit, you must pay attention to the integrity of the transaction. In reality, the efficiency and integrity of the transaction are often the same as that of the fish and the bear's paw)