Oracle SQL Performance Optimization series (6)

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 between them.)

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

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.