Oracle SQL performance optimization series 3

The ORACLE tutorial you are looking at is :Oracle SQL performance optimization series learning 3. 8. Use the DECODE function to reduce processing time

Using the DECODE function avoids the need to repeatedly scan the same record or join the same tables.

Such as:

SELECT COUNT(*), SUM(SAL) FROMEMP
WHERE DEPT_NO = 0020
AND ENAME LIKE 'SMITH %';

SELECT COUNT (*), SUM (SAL)
FROMEMP
WHERE DEPT_NO = 0030
AND ENAME LIKE 'SMITH %';

You can get the same result efficiently with the DECODE function

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 be used in the GROUP BY and ORDER BY clauses.

9. Integration of simple, unrelated database access

If you have a few simple database queries, you can combine them into one query (even if they are unrelated)

Such as:

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 three queries can be combined 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';

Although the efficiency of this method is improved, the readability of the program is greatly reduced, so the reader still has to balance the advantages and disadvantages.

10. Delete duplicate records

Most efficient way to delete 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);

Replace DELETE with TRUNCATE

When delete records in a table, under normal circumstances, the rollback segments (rollback segments) used to store information can be restored. If you do not have COMMIT affairs, ORACLE will data recovery to delete before (accurately, the status of the recovery before the delete command), and when using TRUNCATE, rollback segments not store any information that can be restored. After the command to run, data cannot be recovered. So few resources is called, the execution time is short. (note: TRUNCATE only applies when deleting the full table,TRUNCATE is DDL, not DML)

12. Use COMMIT as often as possible

Whenever possible, use COMMIT in your application as much as possible. This will improve the performance of your application and reduce the demand due to the resources released by COMMIT:

Resources released by COMMIT:

a. Information used to recover data on the rollback segment.

b. Lock acquired by program statement

c. redo log buffer

d. Oracle is the internal cost of managing the above three resources

(note: when using COMMIT, you must pay attention to the transaction integrity. In reality, both efficiency and transaction integrity are often impossible to achieve.)

If the value of DECODE is NULL, the value of SUM (NULL) is NULL -- > If all the values are NULL, SUM(NULL) = NULL but only one value is not NULL,SUM() < > NULL so there should be no logical problem with SQL, right

Personal opinion on point 8: if DECODE is NULL and SUM (NULL) is NULL, the sum will not be normal. It can be changed as follows: SELECT COUNT(DEPT_NO,0020,'X',NULL)) D0020_COUNT, COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT, SUM(DECODE(DEPT_NO,0020,SAL,0)) D0020_SAL, SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_FROM WHERE ENAME SMITH LIKE 'SMITH%';