Oracle SQL Performance Optimization series (III)

8.Use DECODEFunction 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,No associated 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 ';

 

(Press:Although this method is adopted,Improved efficiency,However, the program's readability is greatly reduced,Readers We 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.Use TRUNCATEReplace DELETE

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 (accurately speaking

Restore to the status before executing the DELETE command)

 

When TRUNCATE is used, the rollback segment no longer stores any recoverable information. after the command is run, the data cannot be restored. therefore, few resources are called and the execution time is short.

 

(Press: TRUNCATEApplicable only when the entire table is deleted. TRUNCATEYes DDLNot DML)

 

 

12.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