6. 优化器的优化模式 Oracle优化器的优化模式主要有四种:
Rule:基于规则;
Choose:默认模式。根据表或索引的统计信息,如果有统计信息,则使用CBO方式;如果没有统计信息,相应列有索引,则使用RBO方式。
First rows:与Choose类似。不同的是如果表有统计信息,它将以最快的方式返回查询的前几行,以获得最佳响应时间。
All rows:即完全基于Cost的模式。当一个表有统计信息时,以最快方式返回表所有行,以获得最大吞吐量。没有统计信息则使用RBO方式。
16. 单表查询的优化例子:
SERV表建有组合索引:
SERV_PLACE_X_IDX(PLACE_S_NODE, INTEGRAL, STATE)
SERV表建有单索引:
SERV_PLACE_S_NODE_IDX(PLACE_S_NODE)
SERV_INTEGRAL_IDX(INTEGRAL)
SERV_STATE_IDX(STATE)
SQL语句:
A)SELECT *
FROM SERV T
WHERE T.PLACE_S_NODE = 600488
AND T.INTEGRAL = 594000248
AND T.STATE = 'A';
B) SELECT *
FROM SERV T
WHERE T.PLACE_S_NODE = 600488
AND T.INTEGRAL = 594000248;
C)SELECT *
FROM SERV T
WHERE T.PLACE_S_NODE = 600488
AND T.STATE = 'A';
说明:
例子A、 例子B优化器选用的组合索引SERV_PLACE_X_IDX进行索引扫描;例子A采用全部说明方式,例子B采用部分说明方式。
例子C优化器则选用SERV_PLACE_S_NODE_IDX、SERV_STATE_IDX进行索引合并,而不能使用组合SERV_PLACE_X_IDX,因为PLACE_S_NODE、STATE不是组合索引列PLACE_S_NODE、INTEGRAL、STATE的前部
17. 多表连接的优化处理——连接方式多表连接的基础是两表连接,连接优化的主要工作有:
1、有关连接方式的选择
排序合并连接(Sort Merge Join (SMJ) )
连接属性上都建有索引,则可利用索引已有的排序作合并连接。但在连接属性上没有索引时,则要首先对两表在连接属性上排序,对排序结果再作连接。SELECT A.MDSE_ID, B.NAME
FROM MDSE A, DIM_MDSE_SPEC B
WHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_IDSELECT MDSE_ID, MDSE_SPEC_ID
FROM MDSE
ORDER BY MDSE_ID, MDSE_SPEC_IDSELECT NAME,MDSE_SPEC_ID
FROM DIM_MDSE_SPEC
ORDER BY NAME, MDSE_SPEC_IDABSELECT A.MDSE_ID, B.NAME
FROM A, B
WHERE A.MDSE_SPEC_ID = B.MDSE_SPEC_ID
20. 多表连接的优化处理——连接方式总结一下,在哪种情况下用哪种连接方法比较好:
A)排序合并连接(Sort Merge Join, SMJ):
a) 对于非等值连接,这种连接方式的效率是比较高的。
b) 如果在关联的列上都有索引,效果更好。
c) 对于将2个较大的表源做连接,该连接方法比NL连接要好一些。
B)嵌套循环(Nested Loops, NL):
a) 如果驱动表(外部表)比较小,并且在被驱动表(内部表)上有唯一索引,或有高选择性非唯一索引时,使用这种方法可以得到较好的效率。
b)嵌套循环连接有其它连接方法没有的的一个优点是:可以先返回已经连接的行,而不必等待所有的连接操作处理完才返回数据,这可以实现快速的响应时间。
C)哈希连接(Hash Join, HJ):
a) 这种方法是在oracle7后来引入的,使用了比较先进的连接理论,一般来说,其效率应该好于其它2种连接,但是这种连接只能用在CBO优化器中,而且需要设置合适的hash_area_size参数,才能取得较好的性能。
b) 在2个较大的表源之间连接时会取得相对较好的效率,在一个表源较小时则能取得更好的效率。
c) 只能用于等值连接中
30. 查询优化实例及分析——操作符优化避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列,ORACLE将无法使用该索引。对于单列索引,如果列包含空值,索引中将不存在此记录。 对于复合索引,如果每个列都为空,索引中同样不存在此记录。如果至少有一个列不为空,则记录存在于索引中。
说明:ORACLE在其索引结构中不存空值,因此对IS NULL条件的查询采用的是全表扫描的方式,而不可能使用索引,且对这种条件的查询不存在改写形式。但是对IS NOT NULL的条件则可用其它方法代替的。
对于IS NOT NULL的条件可以如下的等价写法:
对字符型 列名 > ‘ ’ (空格串)
对数值型 列名 > 0
31. 查询优化实例及分析——操作符优化IN操作
用IN写出来的SQL的优点是比较容易写及清晰易懂,但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL是有区别的。
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
说明:在业务密集的SQL当中尽量不采用IN操作符,可以用OR或 EXISTS代替。
NOT IN操作
此操作是强列推荐不使用的,因为它不能应用表的索引 。
说明:可以用NOT EXISTS 或(外连接+判断为空)代替。
LIKE操作符
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但通配符(%)在搜寻词首出现,是不会使用索引,会降低查询速度。当通配符出现在字符串其他位置时,优化器就能利用索引。
select * from serv where name like ‘PHS%’; --使用范围索引
select * from serv where name like ‘%PHS%’; --全表扫描
32. 查询优化实例及分析——操作符优化> 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,有的情况下可以对它进行优化。
例:表table1共有100万条记录,一个number型字段A并建索引。
A=0 30万条
A=1 30万条
A=2 39万条
A=3 1万条
低效率:
SELECT * FROM TABLE1 WHERE A > 2;
高效率:
SELECT * FROM TABLE1 WHERE A >= 3;
执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
说明:在使用 > (大于)操作时,应尽量的考虑用>=(大于等于)来代替