SQL优化常用方法46

1. 连接多个扫描
   如果你对一个列和一组有限的值进行比较, 优化器可能执行多次扫描并对结果进行合并连接. 举例:

SELECT * FROM LODGING WHERE MANAGER IN (‘BILL GATES','KEN MULLER');
优化器可能将它转换成以下形式
SELECT * FROM LODGING WHERE MANAGER = ‘BILL GATES' OR MANAGER = 'KEN MULLER';
当选择执行路径时, 优化器可能对每个条件采用LODGING$MANAGER上的索引范围扫描. 返回的ROWID用来访问LODGING表的记录 (通过TABLE ACCESS BY ROWID 的方式). 最后两组记录以连接(CONCATENATION)的形式被组合成一个单一的集合. Explain Plan :
SELECT STATEMENT Optimizer=CHOOSE
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)