ORACLE SQL Performance Optimization Series (iv)

oracle| Performance | optimization


13. Counting the number of record bars

Contrary to general opinion, COUNT (*) is slightly faster than count (1), although the count of indexed columns is still the fastest if indexed retrieval is possible. such as COUNT (EMPNO)



(Translator: In the CSDN forum, there has been quite a heated discussion, the author's point of view is not very accurate, through the actual test, the above three methods do not have significant performance differences)



14. Replace the HAVING clause with the WHERE clause



Avoid the HAVING clause, which will filter the result set only after all records have been retrieved. This processing requires sorting, totals, and so on. If you can limit the number of records through the WHERE clause, you can reduce the overhead.



For example:



Low efficiency:

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: The condition in having is generally used for comparisons of some aggregate functions, such as count (), and so on. In addition, general conditions should be written in the WHERE clause)



15. Reduce the query to the table

In SQL statements that contain subqueries, you should pay special attention to reducing the query to the table.



For example:

Low efficiency

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:

Low efficiency:

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 improve efficiency by calling the following function.

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: Often seen in forums like ' can write with a SQL ... ' Posts, but the complexity of SQL often sacrificed the efficiency of execution. It is very meaningful to be able to master the application of the above function to solve the problem in practical work.



adjourned