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)
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of oracle statement optimization rules 2

  • Hibernate configuration parameters hibernate.hbm2ddl.auto

    Hibernate in the configuration file: <properties> <property name="hibernate.hbm2ddl.auto" value="create" /> </ properties> Parameter Description: validate load hibernate, the authentication to create a database t ...

  • spring + hibernate + oracle9i to read and write CLOB

    Database-driven update classes12-9i.jar Hibernate modify the configuration of the following code <bean/> <bean Lazy-init="true"> <property name="nativeJdbcExtractor"> <ref local="nativejdbcExtractor"/>

  • Build flex + spring + blazeds + hibernate application

    Build flex + spring + blazeds + hibernate application First, set up the project blazeds 1, will blazeds.war extract to a directory, such as: myflex /; 2, set up java works were such as: MyFlex, in the orientation of selection create project from exis ...

  • Hibernate connection pool configuration

    Hibernate connection pool configuration <! - Jdbc -> <property name="connection.driver_class"> oracle.jdbc.driver.OracleDriver </ property> <property name="connection.url"> jdbc: oracle: thin: @ 10.203.14.132:15

  • hibernate to use the principle of

    The use of hibernate, implementation of data persistence. Has the following several processes. One configuration database connection information. Hibernate.config 2 configuration mapping. 3 use: the use of the process are the following steps: 3.1: Ge ...

  • hibernate generic generic DAO

    package org.lzpeng.dao; import java.io.Serializable; import java.util.List; import org.hibernate.Criteria; import org.hibernate.Query; import org.hibernate.criterion.Criterion; import org.springside.modules.orm.hibernate.Page; /** * * @version 2009-1-10 *

  • Struts2 + hibernate + spring problem user log in

    dao layer services layer action jsp <tr> <td align="center"> <b> user name: </ b> </ td> <td> <s: textfield name = "czyNumber" cssClass = "textstyle" theme = "simple" size = &q

  • Hibernate secondary cache

    Hibernate cache: 2-bit cache, also known as process-level cache or SessionFactory level cache, secondary cache can be shared by all of the session Cache configuration and the use of: Will echcache.xml (the document code in hibernate package directory ...

  • Hibernate's lazy strategy

    hibernate Lazy strategy can be used in: <class> tag, it can be true / false Tags can <PROPERTY> values true / false type of necessary tools to enhance <set> <list> can tag values true / false / extra <many-to-one> <on ...

blog comments powered by Disqus
Recent
Recent Entries
Tag Cloud
Random Entries