Oracle SQL Performance Optimization Series III

The ORACLE tutorial is: Oracle SQL Performance Optimization Series 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 and unrelated Database 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 you use TRUNCATE, rollback segments no longer store any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short. (Note: TRUNCATE is applicable only when the entire table is deleted. If TRUNCATE is DDL, It is not 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

(Note: When Using COMMIT, you must pay attention to the integrity of the transaction. In reality, efficiency and transaction integrity are often unable to meet the needs of both the fish and the bear's paw)

If DECODE is NULL, SUM (NULL) is NULL --> if all values are NULL, SUM (NULL) = NULL, but as long as one value is not NULL, SUM () <> NULL. Therefore, the original SQL statement should have no logic problems.

My Opinion on the eighth point: if the value of DECODE is NULL and the value of SUM (NULL) is NULL, the SUM will not be normal. You can change it to the following: 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, 0) D0020_SAL, SUM (DECODE (DEPT_NO, 0030, SAL, 0 )) d0030_SAL from emp where ename like 'Smith % ';