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_typewhere 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)
(Excerpt from 9cbs)
