一、一般语句的优化
1、建议不用“*” 来代替所有列名
select语句中可以使用“*” 来代替某个表中的所有列,但这样在Oracle系统中会存在解析的动态问题。Oracle系统会通过查询数据字典来将“*”转换成该表的所有列名。建议在select语句使用 访问表有关的实际列名。
2、用truncate代替delete
当用delete语句删除表中的数据时,Oracle会使用撤销表空间来存放恢复的信息。在这期间,如果发出的是rollback语句,Oracle会将数据恢复到删除之前的状态。当用户使用truncate语句时系统不会将被删除的数据写到回滚段上,速度相对快些。
建议在对表中的数据全部删除时使用truncate来代替delete
3、在确保完整性的情况下多用commit语句
在pl/sql块中,经常将几个相互联系的dml语句写在一个begin...end 块中,建议咋每个块中的end前使用commit语句,这样可实现对象dml数据的及时提交,同时也释放事务占用的资源。
commit所释放的资源如下:
*回滚段上用于恢复数据的信息;撤销表空间也只作短暂的保留。
*被程序语句获得的锁。
*redo log buffer中的空间。
*Oracle为管理上述3中资源的内部花费。
4、尽量减少表的查询次数
在含子查询的sql语句中,要特别注意减少对表的查询。
--低效的select语句,子查询中执行了两次,导致系统两次访问同一个表
select tab_name from tables
where tab_name = (select tab_name form tab_columns
where version=3)
and db_ver=(select db_ver from tab_columns where version =3)
--高校的select查询,将子查询合并在一起一次执行
select tab_name from tables
where (tab_name,db_ver)=(select tab_name,db_ver from tab_columns where version=3)
5、用not exists代替not in
在子查询中,not in子句将执行一个内部排序和合并。无论在那种情况xia,not in都是低效的,因为他对子查询中的表执行了一个全表遍历。为了避免使用not in,我们可以把他改写为外连接(outer join) 或使用not exists 子句。
当not in后括号内是列表或子查询所满足结果很少时,也可以使用。
6、用exists代替in
在许多基于驱动表的查询中,为了满足一个条件,往往需要对另一个表进行连接。在这种情况下,使用exists通常将提高查询效率。
7、用exists代替distinct
当提交一个包含一对多表信息的查询时,避免在select子句中使用distinct
。一般可以考虑用exists代替。
二、表的连接方法
1、选择from表的顺序
在基于规则的优化器下,Oracle的解析器按照从右到左的顺序处理from子句的表,因此from子句中写在最后的表将被先处理。在from子句中包含多个表的情况西安,建议选择记录数最少的表作为驱动表。Oracle在处理多个表时,会运用排序及合并的方式连接他们。
2、where子句的连接顺序
Oracle采用从左到右顺序解析where子句,根据这个原理,表之间的连接必须写在其他where条件之前;那些可以过滤掉最大数记录的条件必须写在where子句的末尾。
三、有效使用索引
1、创建索引的基本原则:
*对于经常以查询的关键字为基础的表,并且表中的行遵从均匀分布的;
*以查询关键字为基础,表中的行随机排序;
*包含的列数相对比较少的表;
*表中的大多数查询都包含相对简单的where语句
*缓存命中率较低,并且不需要操作系统缓存。
2、创建索引时选择索引列的原则:
*where语句中频繁使用的关键字;
*sql语句中频繁用于进行表链接的关键字
*可选择性高(重复性少)的关键字;
*对于取值较少的关键字或表达式,不要采用标准的B+树索引,可考虑建立位图索引;
*不要将那些频繁修改的列作为索引列;
*不要使用包含函数或操作符的where从句中的关键字作为索引列,如果存在这样的需要的话可以考虑建立函数索引;
*如果大量的并发insert、update、delete语句访问了父表或子表,则考虑使用完整性约束的外键作为索引;
*在选择索引列时,还要考虑索引所引起的insert、update、delete操作是否值得。
声明:此内容来源于 赵元杰编著的 Oracle 10G 系统管理员简明教程,与大家分享