oracle statement optimization rules 2

Reprinted from: IT expert network of: Peak Wong
ORACLE uses a bottom-up parsing WHERE clause of the order, according to this principle, the connection between tables must be written before the other WHERE condition, which can filter out the maximum number of records which must be written in the WHERE clause at the end.
1. WHERE clause in the connection order.
ORACLE uses a bottom-up parsing WHERE clause of the order, according to this principle, the connection between tables must be written before the other WHERE condition, which can filter out the maximum number of records which must be written in the WHERE clause at the end.

For example:

(Inefficient, the 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);

(Efficiency, 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';

2. SELECT clause to avoid using '*'

When you want listed in the SELECT clause of the COLUMN of all, the use of dynamic SQL column reference to '*' is a convenient way. Unfortunately, this is a very inefficient method. In fact, ORACLE parsing process, will be '*' turn into all the column names, the work is done by querying the data dictionary, which means that will cost more time.

3. To reduce the number of times to access the database

When the execution of each SQL statement, ORACLE internal implementation of a number of tasks: parsing SQL statements, estimates the index of utilization, bind variables, so read the data block. Thus, to reduce the number to access the database, you can actually reduce the workload of ORACLE.

For example, here are three ways to retrieve the number of employees is equal to 0342 or 0291 staff.

Methods 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 (times inefficient)

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 (high)

The following is quoted fragment:
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:
In SQL * Plus, SQL * Forms and Pro * C in the reset ARRAYSIZE parameters for each database access can increase the amount of data retrieval, the proposed value of 200.

4. Use the DECODE function to reduce the processing time

Scan using the DECODE function to avoid duplicate records or to repeat the same connection 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 efficiently get the same results 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%';

Similar, DECODE function can also be used in GROUP BY and ORDER BY clause.
5. Integration is simple, without the associated database access

If you have a few simple database queries, you can put them into a query (even if no relationship between them)

For example:

SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = 'RD';

The above three queries can be combined into one:

SELECT E. NAME, D. NAME, C. NAME
FROM CAT C, DPT D, EMP E, DUAL X
WHERE NVL ('X', X. DUMMY) = NVL ('X', E. ROWID (+))
AND NVL ('X', X. DUMMY) = NVL ('X', D. ROWID (+))
AND NVL ('X', X. DUMMY) = NVL ('X', C. ROWID (+))
AND E. EMP_NO (+) = 1234
AND D. DEPT_NO (+) = 10
AND C. CAT_TYPE (+) = 'RD';

(Although this approach, efficiency is improved, but the readability greatly reduced, so we should weigh the pros and cons between)

分类:Database 时间:2010-02-25 人气:207
分享到:
blog comments powered by Disqus

相关文章

iOS 开发

Android 开发

Python 开发

JAVA 开发

开发语言

PHP 开发

Ruby 开发

搜索

前端开发

数据库

开发工具

开放平台

Javascript 开发

.NET 开发

云计算

服务器

Copyright (C) codeweblog.com, All Rights Reserved.

CodeWeblog.com 版权所有 黔ICP备15002463号-1

processed in 0.635 (s). 12 q(s)