ORACLE SQL优化

内容来自于Oracle DataBase 10g SQL开发指南

 

1）使用表连接而不是多个查询

• 查询中必须要选择连接顺序，将行较少的表连接到后面。

SELECT *
   FROM tab1， tab2， tab3
WHERE tab1.id = tab2.id
     AND tab2.id = tab3.id;

          逆向执行，小表过滤大表

• 查询中避免使用复杂的试图，这样会导致先执行试图，然后才是实际查询，相反的，使用表而不是试图编写查询。

2）在查询中包含表别名，并为查询所引用的每列显式指定合适的别名。这样，数据库不需要搜索查询所使用的表的每列。

3）使用CASE表达式而不是多个查询

    

SELECT 
   COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low,
   COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,
   COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high
FROM products;

 

 4）添加表索引

• ORACLE自动为表的PK和UNIQUE列创建索引
• 使用CONNECT BY和START WITH 时应为相关列建立索引
• 当单个查询检索的行数不大于表总行数的10%时，建立索引是有效的。 

 5）使用WHERE而不是HAVING

• WHERE子句过滤行；HAVING子句过滤组。过滤组需要操作所有的记录，可能使用了多余的记录。

6）使用UNION ALL而不是UNION，避免比较

7）使用EXISTS而不是IN

• IN用于检查一个值是否包含在列表中，检查实际的值，EXISTS只检查行的存在性。因此，尽量使用EXISTS而不是IN。

SELECT product_id, name
   FROM products
WHERE product_id IN
  (SELECT product_id FROM purchases);

    可改成

SELECT product_id, name
   FROM products outer
WHERE EXISTS(
    SELECT 1
      FROM purchases inner
    WHERE inner.product_id = outer.product_id
);

8）尽量少使用DISTINCT

• 尽量少使用DISTINCT，跟UNION 类似，要排序检索到的行和比较。

 9）使用绑定变量（JNDI PreparedStatement）

      ORACLE会缓存已执行的SQL语句，复用语句会减少执行时间。但是，要复用缓存语句，SQL语句必须绝对相同。

• 所有字符必须相同
• 所有字母的大小写必须相同
• 语句中所使用的空格必须相同

10）CBO(Cost Based Optimization), RBO(Rule Based Optimization)

      Oracle10g自动收集表和索引中数据的统计信息，从而生成最优执行计划，这称为基于成本的优化CBO。

      Oracle9i则需手动收集统计信息，否则默认使用基于规则的优化(RBO)。