高级sql优化详解

1. 使用Truncate代替delete

oracle执行delete后会将被删除的数据存放到undo表空间以便恢复，如果之后用户使用rollback而不是commit，则oracle会利用undo表空间中的数据进行恢复。而是用truncate时，oracle不会将被删除的数据放入undo表空间，因而速度要快很多。

2. 活用commit

PL/SQL块中，经常将几个相互联系的DML语句写在BEGIN...END，如果不影响事务的完整性，则建议在每个END前面写一个COMMIT，以达到对DML的及时提交和释放事务所占资源的目的。

3. where子句如何写

oracle优化器的原理是采用自下而上的顺序解析where子句，因此表之间的连接永远写在where后面的第一个位置，并对过滤条件进行估算，

可过滤掉最大数量记录的条件必须写在where子句的末尾。

select count(*)

  from bigtab a, smalltab b

   where a.owner = b.owner 

   and a.object_name = b.table_name

   and b.num_rows > 80

4. 取别名：

联合表的查询中，表名和列名以一个字母为别名可提高1.5倍查询速度

5. 充分利用索引

[何时需要索引？]

(1) 如果检索全表，不必要建索引，因为索引会带来额外的IO操作，如果检索的记录数占全部表记录的10%以下，可以考虑建索引(大 )

(2) 表之间的关联字段可以考虑建索引，特别是一张大表和一张小表的关联

(3) 如果表的记录数比较少时，不建议使用索引，如数据不超过1万行的表不要建立索引

(4) 索引是把双刃剑，在查询和DML之间寻求平衡

[什么列上需要索引？]

(1) 经常在where子句中使用的列

(2) sql语句中经常用于表之间连接的列

(3) 不宜将经常update的列作为索引项

(4) 不宜将经常需要和函数或操作符相结合的列作为索引项

[索引的缺点]

(1) 索引需要磁盘存储空间

(2) 降低了数据维护速度，如delete update insert操作

(3) 过度或不恰当的索引，反而会带来数据检索效率的降低，如表数据很少时。

举例说明：

(1) 索引对is null的限制：使用默认值代替空值

(2) 索引对不等号和not的限制： != 或 <> (不等于) 操作不走索引，推荐a <> 0 改为 a > 0 or a < 0

(3) 索引对不匹配数据类型的限制：提防隐式类型转换，oracle内部处理 a = 0 与 a = ‘0’ 是完全不同的，会导致不走索引

(4) 索引对函数的限制：

select * from bigtab a where substr(a.object_name, 3, 5) = 'NTAB1'

如果在where子句中经常要使用函数时，应该建立基于函数的索引，且只有当查询语句包含该函数或者表达式时，基于函数的索引才会被调用。

创建并使用函数索引：

create index idx_bigtab_objectname_part

on bigtab(substr(object_name, 3, 5)) tablespace ts_oralearn_idx;

http://blog.csdn.net/jdzms23/article/details/23850783