Oracle SQL Performance Optimization series (5)

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 with employee numbers equal to 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::

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.

8.UseDECODEFunction to reduce processing time

You can use the DECODE function to avoid repeated scan of the same record or join the same table.

For example:

Select count (*), SUM (SAL)

FROM EMP

WHERE DEPT_NO = 0020

And ename like 'Smith % ';

Select count (*), SUM (SAL)

FROM EMP

WHERE DEPT_NO = 0030

And ename like 'Smith % ';

You can use the DECODE function to efficiently get the same result.

Select count (DECODE (DEPT_NO, 0020, 'x', NULL) D0020_COUNT,

COUNT (DECODE (DEPT_NO, 0030, 'x', NULL) D0030_COUNT,

SUM (DECODE (DEPT_NO, 0020, SAL, NULL) D0020_SAL,

SUM (DECODE (DEPT_NO, 0030, SAL, NULL) D0030_SAL

From emp where ename like 'Smith % ';

Similarly, the DECODE function can also be used in the group by and order by clauses.