Oracle SQL Performance Optimization 2

8. Use the DECODE function to reduce processing time

9. Simple Integration and unrelated Database Access

10. Delete duplicate records

11. Replace DELETE with TRUNCATE

12. Try to use COMMIT as much as possible

13. Calculate the number of records

14. Replace HAVING clause with Where clause

15. Reduce table queries

16. Improve SQL efficiency through internal functions.

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 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.
(The translator Press: TRUNCATE applies only to deleting the entire table, and TRUNCATE is DDL rather than 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

(Translator's note: when using COMMIT, you must pay attention to the integrity of the transaction. In reality, the efficiency and integrity of the transaction are often the same as that of the fish and the bear's paw)

13. Calculate the number of records
In contrast, count (*) is slightly faster than count (1). Of course, if you can search by index, the index column COUNT is still the fastest. For example, count (EMPNO)
(According to the Press: In the CSDN Forum, there was a very heated discussion on this. The author's opinion was not very accurate. Through actual tests, there was no significant performance difference between the above three methods)

14. Replace HAVING clause with Where clause
Avoid using the HAVING clause. HAVING filters the result set only after all records are retrieved. this process requires sorting, total, and other operations. if the WHERE clause can be used to limit the number of records, this overhead can be reduced.
For example:

Inefficiency:

Select region, AVG (LOG_SIZE)

FROM LOCATION

GROUP BY REGION

Having region! = 'Sydney'

And region! = 'Perth'

Efficient

Select region, AVG (LOG_SIZE)

FROM LOCATION

Where region! = 'Sydney'

And region! = 'Perth'

GROUP BY REGION

(The conditions in HAVING are generally used to compare some Aggregate functions, such as COUNT (). In addition, the general conditions should be written in the WHERE clause)

15. Reduce table queries
In SQL statements containing subqueries, pay special attention to reducing the number of queries to the table.
For example:

Inefficient

SELECT TAB_NAME

FROM TABLES

WHERE TAB_NAME = (SELECT TAB_NAME

FROM TAB_COLUMNS

Where version = 604)

AND DB_VER = (SELECT DB_VER

FROM TAB_COLUMNS

Where version = 604)

Efficient

SELECT TAB_NAME

FROM TABLES

WHERE (TAB_NAME, DB_VER)

= (SELECT TAB_NAME, DB_VER)

FROM TAB_COLUMNS

Where version = 604)

Example of updating multiple columns:

Inefficiency:

UPDATE EMP

SET EMP_CAT = (select max (CATEGORY) FROM EMP_CATEGORIES ),

SAL_RANGE = (select max (SAL_RANGE) FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

Efficient:

UPDATE EMP

SET (EMP_CAT, SAL_RANGE)

= (Select max (CATEGORY), MAX (SAL_RANGE)

FROM EMP_CATEGORIES)

WHERE EMP_DEPT = 0020;

16. Improve SQL efficiency through internal functions.

Select h. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DESC, COUNT (*)

FROM HISTORY_TYPE T, emp e, EMP_HISTORY H

Where h. EMPNO = E. EMPNO

And h. HIST_TYPE = T. HIST_TYPE

Group by h. EMPNO, E. ENAME, H. HIST_TYPE, T. TYPE_DESC;

You can call the following functions to improve efficiency.

FUNCTION LOOKUP_HIST_TYPE (typ in number) RETURN VARCHAR2

AS

TDESC VARCHAR2 (30 );

CURSOR C1 IS

SELECT TYPE_DESC

FROM HISTORY_TYPE

WHERE HIST_TYPE = TYP;

BEGIN

OPEN C1;

FETCH C1 into tdesc;

CLOSE C1;

RETURN (NVL (TDESC ,'? '));

END;

FUNCTION LOOKUP_EMP (emp in number) RETURN VARCHAR2

AS

ENAME VARCHAR2 (30 );

CURSOR C1 IS

SELECT ENAME

FROM EMP

Where empno = EMP;

BEGIN

OPEN C1;

FETCH C1 into ename;

CLOSE C1;

RETURN (NVL (ENAME ,'? '));

END;

Select h. EMPNO, LOOKUP_EMP (H. EMPNO ),

H. HIST_TYPE, LOOKUP_HIST_TYPE (H. HIST_TYPE), COUNT (*)

FROM EMP_HISTORY H

Group by h. EMPNO, H. HIST_TYPE;

You can often see in the forum that 'the SQL statement can be used to write .... ', But I do not know that complicated SQL statements often sacrifice execution efficiency. It is very meaningful to grasp the above methods to solve problems using functions)