ORACLE调优

1)

ORACLE 的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行排序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

WHERE子句中的连接顺序
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

 

2)

多个表查询时, 在返回列前面加上对应表的别名

 

3)

Create an index when a query retreives <= 10 percent of the total rows in a table.

A good candidate for indexing would be a column containing a unique value for each row. (This is for B-tree Index)

In a hierarchical query, consider adding indexes to the columns referenced by in the START WITH and CONNECT BY clause

 

4)

Use WHERE rather than HAVING, because HAVING is used after group by.

 

5)

Use UNION ALL rather than UNION

 

6)

Use EXISTS rather than IN

 

7)

Use EXISTS rather than DISTINCT, because DISTINCT sorts the retrieved rows before suppressing the duplicate rows.

 

8)

Use GROUPING SETS rather than CUBE

 

9)

Use Bind Variables.

The Oracle database caches SQL Statement, a cached statement is reused if an identical statement is submitted to the database.

The rules to identify if the sql is identical :

a) All characters in the SQL must be same.

b) All letters in the SQL must be in the same case.

c) All spaces in the SQL must be same.

 

Create a Bind Variable :

VARIABLE v_product_id NUMBER

 

Assign a value :

BEGIN

 :v_product_id := 1;

END;

/

SELECT * FROM products WHERE product_id = :v_product_id;

 

BEGIN

 :v_product_id := 2;

END;

/

SELECT * FROM products WHERE product_id = :v_product_id; (This statement will be executed faster by using bind variable)

 

Using bind variable to store the a value returned by a PL/SQL Funtion

Using bind variable to store rows from a REFCURSOR

 

10)

Oracle uses optimizer to generate the most efficient path to access the data stored in the tables.

The path is called as execution plan, the best execution plan is known as cost-based optimization

Database versions prior to Oracle 10g is rule-based optimization.

The cost-based optimization automatically uses actual information gathered from the data in the tables and indexes.

 

SQL>DESCRIBE plan_table

If the plan_table doesn't exist, then execute below script

SQL>@ E:\oracle_11g\product\11.1.0\db_1\RDMS\ADMIN\utlxplan.sql

 

EXPLAIN PLAN (populates a table named plan_table with the SQL statement's execution plan)

syntax : EXPLAIN PLAN SET STATEMENT_ID = statement_id FOR sql_statement;

 

UNDEFINE v_statement_id;

SELECT
  id ||
  DECODE(id, 0, '', LPAD(' ', 2*(level - 1))) || ' ' ||
  operation || ' ' ||
  options || ' ' ||
  object_name || ' ' ||
  object_type || ' ' ||
  DECODE(cost, NULL, '', 'Cost = ' || position)
AS execution_plan
FROM plan_table
CONNECT BY PRIOR id = parent_id
AND statement_id = '&&v_statement_id'
START WITH id = 0
AND statement_id = '&v_statement_id';

 

 

TABLE ACCESS FULL (full table scan)

INDEX FULL SCAN

TABLE ACCESS BY INDEX

SORT JOIN

MERGE JOIN

 

SELECT /*+ FIRST_ROWS(2) */ p.name, pt.name
FROM products p, product_types pt
where p.product_type_id = pt.product_type_id;