在含有子查询的SQL语句中,要特别注意减少对表的查询.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;
No10：IN与EXIST
先说IN他相当对inner table执行一个个带有distinct的子查询语句，然后得到的查询结果集再与outer table进行连接，当然连接的方式和索引的使用仍然同于普通的两表连接。select * from T1 where x in (select y from T2);可以转换成如下select * from T1,(select distinct y from T2) T2where T1.x=T2.y;再说exists实际上exists相当于对outer table进行全表扫描，用从中检索到的每一行与inner table做循环匹配输出相应的符合条件的结果，其主要开销是对outer table的全表扫描（full scan），而连接方式是nested loop方式。可以写成select * from T1 where exists (select NULL from T2 where T2.y=T1.x);转换成for cursor1 in (select * from T1)loopif (exists (select NULL from T2 where T2.y=cursor1.x))then 返回匹配的记录;end if;end loop;通过上面的解释，现在很容易明白当T2数据量巨大且索引情况不好(大量重复值等)，则不宜使用产生对T2的distinct检索而导致系统开支巨大的IN操作，反之当T1表数据量巨大(不受索引影响)而T2表数据较少且索引良好则不宜使用引起T1全表扫描的EXISTS操作
No11：NOT IN, NOT EXIST
在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.
No12：使用表连接连替换EXIST
通常来说 , 采用表连接的方式比EXISTS更有效率SELECT ENAME FROM EMP E WHERE EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’); (更高效) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = ‘A’ ;
存在数据类型隐形转换的,如： select * from staff_member where staff_id=’123’； 列上有数学运算的，如： select * from staff_member where salary*2<10000; 使用不等于(<> )运算的，如： select * from staff_member where dept_no<>2001;记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 使用substr字符串函数的，如： select * from staff_member where substr(last_name,1,4)=’FRED’; ‘%’通配符在第一个字符的，如: select * from staff_member where first_name like ‘%DON’; 字符串连接(||)的，如： select * from staff_member where first_name||’’=’DONALD’ 避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引 ．对于单列索引，如果列包含空值，索引中将不存在此记录. 对于复合索引，如果每个列都为空，索引中同样不存在此记录. 如果至少有一个列不为空，则记录存在于索引中． 通常， 我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响. 当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描. 如果一定要对使用函数的列启用索引, ORACLE新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/
3．多个索引情况下的选择
当SQL语句的执行路径可以使用分布在多个表上的多个索引时, ORACLE会同时使用多个索引并在运行时对它们的记录进行合并, 检索出仅对全部索引有效的记录.在ORACLE选择执行路径时,唯一性索引的等级高于非唯一性索引. 然而这个规则只有当WHERE子句中索引列和常量比较才有效.如果索引列和其他表的索引类相比较. 这种子句在优化器中的等级是非常低的.如果不同表中两个相同等级的索引将被引用, FROM子句中表的顺序将决定哪个会被率先使用. FROM子句中最后的表的索引将有最高的优先级.如果相同表中两个想同等级的索引将被引用, WHERE子句中最先被引用的索引将有最高的优先级.举例: DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME, FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = ‘A’;这里,DEPTNO索引将被最先检索,然后同EMP_CAT索引检索出的记录进行合并. 执行路径如下: TABLE ACCESS BY ROWID ON EMP AND-EQUAL INDEX RANGE SCAN ON DEPT_IDX INDEX RANGE SCAN ON CAT_IDX当ORACLE无法判断索引的等级高低差别,优化器将只使用一个索引,它就是在WHERE子句中被列在最前面的.
4．自动选择索引
如果表中有两个以上（包括两个）索引，其中有一个唯一性索引，而其他是非唯一性．在这种情况下，ORACLE将使用唯一性索引而完全忽略非唯一性索引． 举例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326 AND DEPTNO = 20 ; 这里，只有EMPNO上的索引是唯一性的，所以EMPNO索引将用来检索记录．TABLE ACCESS BY ROWID ON EMP INDEX UNIQUE SCAN ON EMP_NO_IDX
5．等式比较和范围比较
当WHERE子句中有索引列, ORACLE不能合并它们,ORACLE将用范围比较. 举例: DEPTNO上有一个非唯一性索引,EMP_CAT也有一个非唯一性索引. SELECT ENAME FROM EMP WHERE DEPTNO > 20 AND EMP_CAT = ‘A’; 这里只有EMP_CAT索引被用到,然后所有的记录将逐条与DEPTNO条件进行比较. 执行路径如下: TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON CAT_IDX 3．组合索引总是使用索引的第一个列如果索引是建立在多个列上, 只有在它的第一个列(leading column)被where子句引用时,优化器才会选择使用该索引.
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 
