Oracle SQL Performance Optimization (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)
According to the author's suggestion: In the csdn forum, the author has had a very heated discussion on this.Test


, There is 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
InSQL


In the statement, pay special attention to reducing the query of 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
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 complex SQL statements often sacrifice execution efficiency, so that I can master the above methods to solve problems using functions in reality.Work


Is very meaningful)