Oracle SQL Performance Optimization Series 2

The ORACLE tutorial is: Oracle SQL Performance Optimization Series 2.
4. Select the most efficient table name sequence (only valid in the rule-based Optimizer)

The ORACLE parser processes the table names in the FROM clause in the order FROM right to left. Therefore, the table written in the FROM clause (basic table driving table) will be processed first. when the FROM clause contains multiple tables, You must select the table with the least number of records 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 sort the records, and then scan the second table (the last second table in the FROM clause ), finally, all records retrieved from the second table are merged with the appropriate records in the first table.

For example:

Table TAB1 16,384 records

Table TAB2 1 record

Select TAB2 as the base table (the best method)

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

Select TAB2 as the base table (poor method)

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

If more than three tables are connected for query, You need to select an intersection table as the base table, which is the table referenced by other tables.

For example:

The EMP table describes the intersection between 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

It will be more efficient than the following SQL statements

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

ORACLE uses the bottom-up sequence to parse the WHERE clause. According to this principle, the join between tables must be written before other WHERE conditions. The conditions that can filter out the maximum number of records must be written at the end of the WHERE clause.

Example: (inefficient, execution time: 156.3 seconds)

SELECT...

FROM EMP E

Where sal> 50000

And job = 'manager'

AND 25 <(select count (*) FROM EMP

Where mgr = E. EMPNO );

(Efficient, execution time: 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 '*' in the SELECT clause '*'

When you want to list all columns in the SELECT clause, using dynamic SQL COLUMN reference '*' is a convenient method. unfortunately, this is a very inefficient method. in fact, ORACLE converts '*' into all column names in sequence during parsing. This task is done by querying the data dictionary, which means it takes more time.

7. Reduce the number of database accesses

When each SQL statement is executed, ORACLE performs a lot of internal work: parsing SQL statements, estimating index utilization, binding variables, and reading data blocks. it can be seen that reducing the number of visits to the database can actually reduce the workload of ORACLE.

For example, there are three ways to retrieve employees whose employee number is 0342 or 0291.

Method 1 (most inefficient)

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE emp_no. = 342;

SELECT EMP_NAME, SALARY, GRADE

FROM EMP

WHERE emp_no. = 291;

Method 2 (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 ...,..,.. ;

.....

OPEN C1 (291 );

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

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;

Note

[1] [2] Next page

The ORACLE tutorial is: Oracle SQL Performance Optimization Series 2. Meaning:

You can reset the ARRAYSIZE parameter in SQL * Plus, SQL * Forms, and Pro * C to increase the retrieval data volume for each database access. The recommended value is 200.

Previous Page [1] [2]