Oracle数据库无疑是目前操作最多的数据库了，作为基于框架的Java开发人员，不能达到专业DBA的高度，可能编写SQL并分析SQL语句性能也是必须的。
我所接触的Oracle版本都在Oracle10g或之上，现阶段主流版本也是10g之上。谈到版本是因为要说到Oracle的优化器：CBO(基于代价的优化器)、RBO(基于规则的优化器，它只认规则，对数据不敏感)。
CBO是从ORACLE 8中开始引入，但到ORACLE 9i 中才逐渐成熟，在ORACLE 10g中完全取代RBO， CBO是计算各种可能“执行计划”的“代价”，即COST，从中选用COST最低的执行方案，作为实际运行方案，即检查SQL 语句中的每个表的物理大小,索引的状态,然后选用花费最低的执行路径.。它依赖数据库对象的统计信息，统计信息的准确与否会影响CBO做出最优的选择。如果对一次执行SQL时发现涉及对象（表、索引等）没有被分析、统计过，那么ORACLE会采用一种叫做动态采样的技术，动态的收集表和索引上的一些数据信息。
了解了当前Oracle默认的优化器，意味着我们在对SQL进行优化的时候有了一个方向，去查看执行计划的代价，尽量降低这个代价。以下列出SQL优化的一些方式。
访问Table 的方式：多通过ROWID 访问表少全表扫描
ORACLE 采用两种访问表中记录的方式:
a.全表扫描
全表扫描就是顺序地访问表中每条记录. ORACLE 采用一次读入多个数据块(database block)的方式优化全表扫描.
b.通过ROWID 访问表
你可以采用基于ROWID 的访问方式情况,提高访问表的效率, , ROWID 包含了表中记录的物理位置信息..ORACLE 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系.通常索引提供了快速访问ROWID 的方法,因此那些基于索引列的查询就可以得到性能上的提高。
共享SQL 语句
为了不重复解析相同的SQL 语句,在第一次解析之后, ORACLE 将SQL 语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL 语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE 就能很快获得已经被解析的语句以及最好的执行路径. ORACLE 的这个功能大大地提高了SQL 的执行性能并节省了内存的使用.可惜的是ORACLE 只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.数据库管理员必须在init.ora 中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了。
WHERE 子句中的连接顺序
ORACLE 采用自下而上的顺序解析WHERE 子句,根据这个原理,表之间的连接必须写在其他WHERE 条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE 子句的末尾。
SELECT 子句中避免使用星号
当你想在SELECT 子句中列出所有的COLUMN 时,使用动态SQL 列引用‘’ 是一个方便
的方法.不幸的是,这是一个非常低效的方法。实际上,ORACLE 在解析的过程中, 会将’’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。
减少访问数据库的次数
当执行每条SQL 语句时, ORACLE 在内部执行了许多工作: 解析SQL 语句, 估算索引的利用率, 绑定变量, 读数据块等等. 由此可见, 减少访问数据库的次数, 就能实际上减少ORACLE 的工作量。
删除重复记录
最高效的删除重复记录方法( 因为使用了ROWID)
delete from emp e
where e.rowid > (select min(x.rowid) from emp x where x.emp_no = e.emp_no);
使用DECODE 函数来减少处理时间
使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表。
例如：
低效
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%';
高效
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%';
用TRUNCATE 替代DELETE
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息。如果你没有COMMIT 事务,ORACLE 会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)。而当运用TRUNCATE 时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复。因此很少的资源被调用,执行时间也会很短。
ORACLE 存储过程不能用truncate table xxx，可以用execute immediate 'TRUNCATE TABLE BAK_F41021_MRP';需要注意是truncate是DDL操作(CREATE、ALTER、DROP等)，隐式自动提交事务，所以如果该代码是在事务中一定要慎用。
尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高 ,需求也会因为
COMMIT 所释放的资源而减少:
COMMIT 所释放的资源:
a.回滚段上用于恢复数据的信息.
b.被程序语句获得的锁
c.redo log buffer 中的空间
d.ORACLE 为管理上述3 种资源中的内部花费
计算记录条数
和一般的观点相反, count(*) 比count(1)稍快(解析之后发现并没有), 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如COUNT(EMPNO)
用Where 子句替换HAVING 子句
避免使用 HAVING 子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序、总计等操作。如果能通过WHERE 子句限制记录的数目,那就能减少这方面的开销。
减少对表的查询
在含有子查询的SQL 语句中,要特别注意减少对表的查询.
例如:
低效
select tab_name
from tables
where tab_name = (select tab_name from tab_columns where version = 604)
and db_ver = (select db_ver from tab_columns where version = 604);
高效
select tab_name
from tables
where (tab_name, db_ver) = (select tab_name, db_ver) from tab_columns
where version = 604);
Update 多个Column 例子:
低效:
update emp
set emp_cat =
(select max(category) from emp_categories),
sal_range =
(select max(sal_range) from emp_categories)
where emp_dept = 0020;
高效:
update emp
set (emp_cat, sal_range) =
(select max(category), max(sal_range) from emp_categories)
where emp_dept = 0020;
使用表的别名(Alias)
当在SQL 语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column 上.这样一来,就可以减少解析的时间并减少那些由Column 歧义引起的语法错误。
用EXISTS 替代IN/用NOT EXISTS 替代NOT IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN 子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN 都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。
用索引提高效率
虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的 INSERT , DELETE , UPDATE 将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.
ORACLE 对索引有两种访问模式.
1、索引唯一扫描( INDEX UNIQUE SCAN)
在内部, 首先索引将通过索引唯一扫描的方式被访问, 获得相对应的ROWID, 通过ROWID 访问表的方式执行下一步检索.如果被检索返回的列包括在INDEX 列中,ORACLE 将不执行第二步的处理(通过ROWID 访问表). 因为检索数据保存在索引中, 单单访问索引就可以完全满足查询结果.
2、索引范围查询(INDEX RANGE SCAN)
适用于两种情况: 基于一个范围的检索(WHERE 子句条件包括一系列值, ORACLE 将通过索引范围查询的方式查询索引，由于索引范围查询将返回一组值, 它的效率就要比索引唯一扫描低一些.)/基于非唯一性索引的检索(SQL 返回列,而它并不存在于索引中, 所以在索引范围查询后会执行一个通过ROWID 访问表的操作)
网友评论