ORACLE SQL Performance Optimization Series (iii)

oracle| Performance | optimization


8. Use the Decode function to reduce processing time



Use the Decode function to avoid repeatedly scanning the same record or repeating 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 get the same results efficiently.



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 the EMP WHERE ename like ' smith% ';



Similarly, the Decode function can also be applied to the group BY and ORDER BY clauses.





9. Simple integration, no associated database access



If you have a few 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 above 3 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: Although this approach to improve efficiency, but the readability of the program is greatly reduced, so the reader still have to weigh the pros and cons)



10. Delete duplicate records

The most efficient way to delete a duplicate record (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 you delete records in a table, in general, the rollback segment (rollback segments) is used to hold information that can be recovered. If you do not commit a transaction, Oracle restores the data to the state it was in before it was deleted (exactly

Revert to the condition before the delete command was executed)



When using truncate, the rollback segment no longer holds any recoverable information. When the command runs, the data cannot be recovered. Therefore, few resources are invoked and execution time is short.



(Translator: Truncate only applies when deleting full table, truncate is DDL not DML)





12. Use of commit as much as possible



Whenever possible, use a commit in the program as much as possible, so that the performance of the program is improved and the requirements are reduced by the resources released by the commit:

Resources released by commit:

A. The information used to recover data on the rollback segment.

B. Locks obtained by program statements

C. Space in the Redo log buffer

D. Oracle to manage the internal costs of the 3 resources mentioned above



(Translator: In the use of commit must pay attention to the integrity of the transaction, in reality, efficiency and transactional integrity is often the fish and bear the cake can not be)