Oracle SQL Performance Optimization Series (4)

xiaoxiao2021-03-06  74

13. Calculate the number of records

In contrast, count (*) is slightly faster than count (1), of course, if you can retrieve it, the count of the index column is still the fastest. For example count (Empno)

(Translator Press: In the 9CBS Forum, there was a very enthusiastic discussion on this. The author's view is not very accurate. Through the actual test, the above three methods have no significant performance differences)

14. Replace the Having clause with WHERE clause

Avoid using a Having clause, Having will only filter the result set after retrieving all records. This processing requires sorting, totaling, etc. If you can limit the number of records through the WHERE clause, it can reduce this overhead. .

E.g:

Inefficient:

Select Region, AVG (log_size)

From location

GROUP BY Region

Having Region Region! = 'Sydney'

And Region! = 'Perth'

Efficient

Select Region, AVG (log_size)

From location

WHERE region region! = 'Sydney'

And Region! = 'Perth'

GROUP BY Region

(Translator Press: Having conditions are generally used to compare some set functions, such as count (), etc. In addition, the general conditions should be written in the WHERE clause.)

15. Reduce the query of the table

In the SQL statement containing subquery, pay special attention to reduce the query of the table.

E.g:

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)

Update multiple column examples:

Inefficient:

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 by 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;

Efficiency can be improved by calling the following functions.

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;

(Translator Press: It is often seen in the forum, such as' can write out ... " It is very meaningful in it)

转载请注明原文地址:https://www.9cbs.com/read-120575.html

New Post(0)