ORACLE SQL Performance Optimization series (ii)

oracle| Performance | optimization
4. Select the most efficient table name order (valid only in the Rule-based optimizer)

The Oracle parser processes the table names in the FROM clause in Right-to-left order, so the last table in the FROM clause (the underlying table driving tables) is processed first. In cases where multiple tables are included in the FROM clause, you must select a table with the fewest number of records to use as the base table. When Oracle processes multiple tables, it uses sorting and merging to connect them. First, scan the first table (the last table in the FROM clause) and order the records, and then scan the second table ( The last second table in the FROM clause, and finally merges all the records retrieved from the second table with the appropriate records in the first table.



For example:

Table TAB1 16,384 Records

Table TAB2 1 Records



Select TAB2 as the base table (best Practice)

Select COUNT (*) from TAB1,TAB2 execution time 0.96 seconds



Select TAB2 as the base table (Bad method)

Select COUNT (*) from TAB2,TAB1 execution time 26.09 seconds



If you have more than 3 table join queries, you need to select the Crosstab table (intersection table) as the underlying table, which is the table referenced by the other tables.



For example:



The EMP table describes the intersection of the location table and the category table.



SELECT *

From LOCATION L,

CATEGORY C,

EMP E

WHERE e.emp_no BETWEEN 1000 and 2000

and e.cat_no = C.cat_no

and E.LOCN = L.LOCN



will be more efficient than the following SQL



SELECT *

From EMP E,

LOCATION L,

CATEGORY C

WHERE e.cat_no = C.cat_no

and E.LOCN = L.LOCN

and E.emp_no BETWEEN 1000 and 2000





5. The order of joins in the WHERE clause.



Oracle parses the WHERE clause in a bottom-up order, according to which the connection between the tables must be written before the other where conditions, and the conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.



For example:



(Inefficient, execution time 156.3 seconds)

SELECT ...

From EMP E

WHERE SAL > 50000

and JOB = ' MANAGER '

< (SELECT COUNT (*) from EMP

WHERE mgr=e.empno);



(Efficient, execution time 10.6 seconds)

SELECT ...

From EMP E

WHERE < (SELECT COUNT (*) from EMP

WHERE mgr=e.empno)

and SAL > 50000

and JOB = ' MANAGER ';





6. Avoid the use of ' * ' in the SELECT clause

When you want to list all columns in the SELECT clause, it is a convenient way to use the dynamic SQL column reference ' * '. Unfortunately, this is a very inefficient approach. In fact, Oracle converts ' * ' to all column names in the parsing process, which is done by querying the data dictionary, which means more time will be spent.





7. Reduce the number of accesses to the database

When executing every SQL statement, Oracle does a lot of work internally: Parsing SQL statements, estimating index utilization, binding variables, reading chunks, and so on. Thus, reducing the number of accesses to the database can actually reduce Oracle's workload.



For example

Here are three ways to retrieve employees with an employee number equal to 0342 or 0291.



Method 1 (lowest effect)

SELECT Emp_name, SALARY, GRADE

From EMP

WHERE emp_no = 342;



SELECT Emp_name, SALARY, GRADE

From EMP

WHERE emp_no = 291;



Method 2 (secondary low efficiency)



DECLARE

CURSOR C1 (e_no number) is

SELECT Emp_name,salary,grade

From EMP

WHERE emp_no = e_no;

BEGIN

OPEN C1 (342);

FETCH C1 into ...,..,.. ;

.....

OPEN C1 (291);

FETCH C1 into ...,..,.. ;

Close C1;

End;











Method 3 (efficient)



SELECT A.emp_name, A.salary, A.grade,

B.emp_name, B.salary, B.grade

From EMP a,emp B

WHERE A.emp_no = 342

and b.emp_no = 291;





Attention:

The arraysize parameters are reset in Sql*plus, Sql*forms, and pro*c to increase the amount of retrieved data per database access, with a recommended value of 200



adjourned