ORACLE性能优化操作
1.避免对列的操作
任何对列的操作都可能导致全表扫描，这里所谓的操作包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等式的右边，甚至去掉函数。
比如：select * from record where to_char(ActionTime,'yyyymmdd')='19991201'（10秒）
换成：select * from record where ActionTime= to_date ('19991201' ,'yyyymmdd')（< 1秒）
2.避免不必要的类型转换
需要注意的是，尽量避免潜在的数据类型转换。
如将字符型数据与数值型数据比较，ORACLE会自动将字符型用to_number()函数进行转换，从而导致全表扫描
比如：select col1,col2 from tab1 where col1>10;换成：select col1,col2 from tab1 where col1>'10';
3.增加查询的范围限制
增加查询的范围限制，避免全范围的搜索。
比如：select * from record where ActionTime < to_date ('20010301' ,'yyyymm');
换成：
- select * from record where ActionTime < to_date ('20010301' ,'yyyymm')
- and ActionTime > to_date ('20010101' ,'yyyymm');
如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
索引的选择性低，但数据的值分布差异很大时，仍然可以利用索引提高效率。
A、数据分布不均匀的特殊情况下，选择性不高的索引也要创建。
表ServiceInfo中数据量很大，假设有一百万行，其中有一个字段DisposalCourseFlag，取值范围为枚举值：[0，1，2，3，4，5，6，7]。
按照前面说的索引建立的规则，“选择性不高的字段不应该建立索引”，
该字段只有8种取值，索引值的重复率很高，索引选择性明显很低，因此不建索引。
然而，由于该字段上数据值的分布情况非常特殊，具体如下表：
取值范围 占总数据量的百分比
1~5 1%
6 98%
7 1%
而且，常用的查询中，查询DisposalCourseFlag<6 的情况既多又频繁，毫无疑问，如果能够建立索引，并且被用，
那么将大大提高这种情况的查询效率。因此，我们需要在该字段上建立索引。
**复合索引：如果我们创建了（area,age,salary）的复合索引，那么其实相当于创建了（area,age,salary）,(area,age),(area)三个索引，这样称为最佳左前缀特性
在ORACLE优化器无法用上合理索引的情况下，利用HINT强制指定索引。
继续“oracle 性能优化操作七”的例子，ORACLE缺省认定，表中列的值是在所有数据行中均匀分布的，
也就是说，在一百万数据量下，每种DisposalCourseFlag值各有12.5万数据行与之对应。
假设SQL搜索条件DisposalCourseFlag=2，利用DisposalCourseFlag列上的索引进行数据搜索效率，
往往不比全表扫描的高，ORACLE因此对索引“视而不见”，从而在查询路径的选择中，用其他字段上的索引甚至全表扫描。
根据我们上面的分析，数据值的分布很特殊，严重的不均匀。为了利用索引提高效率，此时，
一方面可以单独对该字段或该表用analyze语句进行分析，对该列搜集足够的统计数据，使ORACLE在查询选择性较高的值时能用上索引；
另一方面，可以利用HINT提示，在SELECT关键字后面，加上“/*+ INDEX(表名称，索引名称）*/”的方式，强制ORACLE优化器用上该索引。
比如：select * from serviceinfo where DisposalCourseFlag=1 ;
换成：select /*+ INDEX(SERVICEINFO,IX_S_DISPOSALCOURSEFLAG) */ * from serviceinfo
where DisposalCourseFlag=1;
如果查询语句中，表用到别名时，上边语句中的table一定要是别名，否则不走执行强制索引
9. 屏蔽无用索引
继续“oracle 性能优化操作八”的例子
由于实际查询中，还有涉及到DisposalCourseFlag=6的查询，而此时如果用上该字段上的索引，将是非常不明智的，效率也极低。
因此这种情况下，我们需要用特殊的方法屏蔽该索引，以便ORACLE选择其他字段上的索引。
比如，如果字段为数值型的就在表达式的字段名后，添加“+ 0”，为字符型的就并上空串：||""
比如：
- select * from serviceinfo where DisposalCourseFlag+ 0 = 6 and workNo = '36' ;
对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；
同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引
11.like子句尽量前端匹配
- select * from city where name like ‘%S%’;
以上查询的执行计划用了全表扫描（TABLE ACCESS FULL），如果能够修改为：
- select * from city where name like ‘S%’;
那么查询的执行计划将会变成（INDEX RANGE SCAN），成功的利用了name字段的索引。
这意味着Oracle SQL优化器会识别出用于索引的like子句，只要该查询的匹配端是具体值。
因此我们在做like查询时，应该尽量使查询的匹配端是具体值，即使用like ‘S%’
12.用Case语句合并多重扫描
我们常常必须基于多组数据表计算不同的聚集。例如下例通过三个独立查询：
- select count(*) from emp where sal<1000;
- select count(*) from emp where sal between 1000 and 5000;
- select count(*) from emp where sal>5000;
这样我们需要进行三次全表查询，但是如果我们使用case语句：
- select
- count (sale when sal <1000
- then 1 else null end) count_poor,
- count (sale when between 1000 and 5000
- then 1 else null end) count_blue_collar,
- count (sale when sal >5000
- then 1 else null end) count_poor
- from emp;
这样查询的结果一样，但是执行计划只进行了一次全表查询。
13. 使用nls_date_format
例：
- select * from t_wip_tracking where to_char(in_station_time,'mm')='12';
这个查询的执行计划将是全表查询，如果我们改变nls_date_format，
- alter session set nls_date_format=’MM’;
现在重新修改上面的查询：
- select * from t_wip_tracking where in_station_time='12';
这样就能使用in_station_time上的索引了，它的执行计划将是（INDEX RANGE SCAN）
14.使用基于函数的索引
前面谈到任何对列的操作都可能导致全表扫描，例如：
- select * from emp where substr(ename,1,2)=’SM’;
但是这种查询又经常使用，我们可以创建一个带有substr函数的基于函数的索引，
- create index emp_ename_substr on eemp ( substr(ename,1,2) );
这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）
扫一扫
825
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
