Oracle SQL Performance Optimization Series (2)

xiaoxiao2021-03-06  76

4. Select the most efficient table order (which is only valid in rule-based optimers)

Oracle's parser handles the table name in the FROM clause in order from right to left, so the FROM clause writes in the final table (base table driving table) will be processed. In the FROM clause contains multiple In the case of the table, you must select the table for the number of records as a base table. When Oracle handles multiple tables, you will connect to them, first, first, scan the first table (last in the FROM clause That table) and sequence the record, then scan the second table (the last second table in the FROM clause), finally record all records detected from the second table with the first table suitable record merge.

E.g:

Table Tab1 16, 384 records

Table Tab2 1 record

Select Tab2 as a base table (best way)

Select count (*) from tab1, tab2 execution time 0.96 seconds

Select Tab2 as a basic table (poor method)

SELECT Count (*) from Tab2, Tab1 execution time 26.09 seconds

If there are more than 3 table connection queries, you need to select a crosstum (INTERSECTION TABLE) as a base table, and the crosstab refers to the table referenced by the other tables.

E.g:

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

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 connection order in the WHERE clause.

Oracle parses the WHERE clause from the bottom, according to this principle, the connection between the tables must be written before other WHERE conditions, and the conditions that can be filtered out must be written at the end of the WHERE clause.

E.g:

(Inefficient, execution time 156.3 seconds)

SELECT ...

From EMP E

Where sal> 5000

And job = 'manager'

And 25 <(Select Count (*) from EMP

WHERE MGR = E.EMPNO);

(Efficient, execution time is 10.6 seconds)

SELECT ...

From EMP E

WHERE 25 <(Select Count (*) from EMP

Where mgr = e.empno)

And Sal> 50000

And Job = 'Manager';

6. Avoid using '*' in the SELECT clause

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

7. Reduce the number of access to the database

When performing each SQL statement, Oracle has performed a lot of work inside: parsing the SQL statement, estimating the utilization, binding variables, reading data blocks, etc. Reduce the workload of Oracle.

E.g,

There are three ways to retrieve employee numbers equal to 0342 or 0291.

Method 1 (Minimum Efficiency)

SELECT EMP_NAME, SALARY, GRADE

From EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME, SALARY, GRADE

From EMP

WHERE EMP_NO = 291;

Method 2 (inefficient)

Declarecursor 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 (Efficiency)

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;

note:

Reset the Arraysize parameter in SQL * Plus, SQL * Forms, and Pro * C, you can increase the amount of retrieval amount of each database access, the recommended value is 200

转载请注明原文地址:https://www.9cbs.com/read-120577.html

New Post(0)