Oracle SQL performance optimization series 2
- 2020-05-12 06:23:17
- OfStack
The ORACLE tutorial you are looking at is :Oracle SQL performance optimization series learning 2.
4. Select the most efficient order of table names (only valid in rules-based optimizers)
The ORACLE parser processes the table names in the FROM clause from right to left, so the table that is written last in the FROM clause (the base table driving table) will be processed first. Sorting and merging are used to join them. First, scan the first table (the last table in the FROM clause) and dispatch the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.
Such as:
Table TAB1 16,384 records
Table TAB2 1 records
Select TAB2 as the base table (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 you have more than three table join queries, you need to select a cross-table (intersection table), which refers to the table referenced by the other tables, as the underlying table.
Such as:
The EMP table describes the intersection of the LOCATION and CATEGORY tables.
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. Join order in WHERE clause.
ORACLE parses the WHERE clause in bottom-up order. According to this principle, joins between tables must be written before other WHERE conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example: (low efficiency, 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, 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 do you want to list all COLUMN SELECT clause, using dynamic SQL column reference '*' is a convenient way. Unfortunately, this is a very inefficient way. In fact, ORACLE in parsing process, will be '*' in turn into all the column, the work is done by querying the data dictionary, which means that will cost more time.
7. Reduce the number of times you access the database
When each SQL statement is executed, ORACLE does a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on.
For example, there are three ways to retrieve an employee whose employee number equals 0342 or 0291.
Method 1 (least efficient)
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 inefficiency)
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;
note
Reset the ARRAYSIZE parameters in SQL*Plus, SQL*Forms and Pro*C to increase the amount of retrieved data per database visit. The recommended value is 200.
4. Select the most efficient order of table names (only valid in rules-based optimizers)
The ORACLE parser processes the table names in the FROM clause from right to left, so the table that is written last in the FROM clause (the base table driving table) will be processed first. Sorting and merging are used to join them. First, scan the first table (the last table in the FROM clause) and dispatch the records, then scan the second table (the last second table in the FROM clause), and finally merge all the records retrieved from the second table with the appropriate records in the first table.
Such as:
Table TAB1 16,384 records
Table TAB2 1 records
Select TAB2 as the base table (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 you have more than three table join queries, you need to select a cross-table (intersection table), which refers to the table referenced by the other tables, as the underlying table.
Such as:
The EMP table describes the intersection of the LOCATION and CATEGORY tables.
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. Join order in WHERE clause.
ORACLE parses the WHERE clause in bottom-up order. According to this principle, joins between tables must be written before other WHERE conditions, and those that can filter out the maximum number of records must be written at the end of the WHERE clause.
For example: (low efficiency, 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, 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 do you want to list all COLUMN SELECT clause, using dynamic SQL column reference '*' is a convenient way. Unfortunately, this is a very inefficient way. In fact, ORACLE in parsing process, will be '*' in turn into all the column, the work is done by querying the data dictionary, which means that will cost more time.
7. Reduce the number of times you access the database
When each SQL statement is executed, ORACLE does a lot of work internally: parsing SQL statements, estimating index utilization, binding variables, reading blocks, and so on.
For example, there are three ways to retrieve an employee whose employee number equals 0342 or 0291.
Method 1 (least efficient)
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 inefficiency)
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;
note
[1] [2] next page
The ORACLE tutorial you are looking at is :Oracle SQL performance optimization series learning 2. Meaning:Reset the ARRAYSIZE parameters in SQL*Plus, SQL*Forms and Pro*C to increase the amount of retrieved data per database visit. The recommended value is 200.
Previous page [1] [2]