Oracle SQL Performance Optimization series (4)

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)

 

(Press:In CSDNForum,I have had a very heated discussion about this,The author's opinion is not very accurate,Through actual tests,There is no significant performance difference between the above three methods)

 

14.Use WhereReplace HAVING with a clauseClause

 

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! ='Sysydney'

And region! ='PERTH'

GROUP BY REGION

(Press: HAVINGThe condition in is generally used to compare some set functions,For example, COUNT ()And so on.In addition,General conditions should be written in WHEREClause)

 

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;