Oracle+SQL优化第五弹(暂时告一段落)
|
接上一篇; 41 用 UNION-ALL 替换 UNION ( 如果有可能的话) 当 SQL 语句需要 UNION 两个查询结果集合时,这两个结果集合会以 UNION-ALL 的方式被 合并,然后在输出最终结果前进行排序. 如果用 UNION ALL 替代 UNION,这样排序就不是必要了. 效率就会因此得到提高. 举例: 低效： SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’ UNION ALL SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录. 因此各位还是要从业务需求分析使用 UNION ALL 的可行性. UNION 将对结果集合排序,这个操作会使用到 SORT_AREA_SIZE 这块内存. 对于这 块内存的优化也是相当重要的. 下面的 SQL 可以用来查询排序的消耗量 Select substr(name,1,25) "Sort Area Name",substr(value,15) "Value" 42 使用提示(Hints) 对于表的访问,可以使用两种 Hints. FULL 和 ROWID FULL hint 告诉 ORACLE 使用全表扫描的方式访问指定表. 例如: SELECT /*+ FULL(EMP) */ * FROM EMP WHERE EMPNO = 7893;
通常,你需要采用 TABLE ACCESS BY ROWID 的方式特别是当访问大表的时候,使用这种方式,你需要知道 ROIWD 的值或者使用索引. 如果一个大表没有被设定为缓存(CACHED)表而你希望它的数据在查询结束是仍然停留在 SGA 中,你就可以使用 CACHE hint 来告诉优化器把数据保留在 SGA 中. 通常 CACHE hint 和 FULL hint 一起使用. 例如: SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ * FROM WORK;
例如: SELECT /*+ INDEX(LODGING) */ LODGING FROM LODGING WHERE MANAGER = ‘BILL GATES’;
的优化器是 CBO,优化器就可能忽略索引. 在这种情况下,你可以用 INDEX hint 强制 ORACLE 使 用该索引. ORACLE hints 还包括 ALL_ROWS,FIRST_ROWS,RULE,USE_NL,USE_MERGE,USE_HASH 等等. 使用 hint,表示我们对 ORACLE 优化器缺省的执行路径不满意,需要手工修改. 这是一个很有技巧性的工作. 我建议只针对特定的,少数的 SQL 进行 hint 的优化. 对ORACLE 的优化器还是要有信心(特别是 CBO)
ORDER BY 子句只在两种严格的条件下使用索引. ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序. ORDER BY 中所有的列必须定义为非空. WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列.
表 DEPT 包含以下列: DEPT_CODEPKNOT NULL DEPT_DESCNOT NULL DEPT_TYPENULL
SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE
SORT ORDER BY TABLE ACCESS FULL
SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0
TABLE ACCESS BY ROWID ON EMP INDEX RANGE SCAN ON DEPT_IDX 译者: ORDER BY 也能使用索引! 这的确是个容易被忽视的知识点. 我们来验证一下: SQL> select * from emp order by empno; Execution Plan ---------------------------------------------------------- 0SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' 21 INDEX (FULL SCAN) OF 'EMPNO' (UNIQUE)
当比较不同数据类型的数据时,ORACLE 自动对列进行简单的类型转换. 假设 EMPNO 是一个数值类型的索引列. SELECT … FROM EMP WHERE EMPNO = ‘123’
SELECT … FROM EMP WHERE EMPNO = TO_NUMBER(‘123’)
现在,假设 EMP_TYPE 是一个字符类型的索引列. SELECT … FROM EMP WHERE EMP_TYPE = 123
SELECT … FROM EMP WHERE TO_NUMBER(EMP_TYPE)=123 因为内部发生的类型转换,这个索引将不会被用到! 译者: 为了避免 ORACLE 对你的 SQL 进行隐式的类型转换,最好把类型转换用显式表现出来. 注意 当字符和数值比较时,ORACLE 会优先转换数值类型到字符类型. 45 需要当心的 WHERE 子句 某些 SELECT 语句中的 WHERE 子句不使用索引. 这里有一些例子. 在下面的例子里,‘!=’ 将不使用索引. 记住,索引只能告诉你什么存在于表中,而不能告诉 你什么不存在于表中. 不使用索引: SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT !=0;
SELECT ACCOUNT_NAME FROM TRANSACTION WHERE AMOUNT >0;
不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;
SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = ‘AMEX’ AND ACCOUNT_TYPE=’ A’;
FROM TRANSACTION WHERE AMOUNT + 3000 >5000;
SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE AMOUNT > 2000 ; 下面的例子中,相同的索引列不能互相比较,这将会启用全表扫描. 不使用索引: SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);
SELECT ACCOUNT_NAME,AMOUNT FROM TRANSACTION WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);
如果一定要对使用函数的列启用索引,ORACLE 新的功能: 基于函数的索引(Function-Based Index) 也许是一个较好的方案. CREATE INDEX EMP_I ON EMP (UPPER(ename)); /*建立基于函数的索引*/ SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /*将使用索引*/ 46 连接多个扫描 如果你对一个列和一组有限的值进行比较,优化器可能执行多次扫描并对结果进行合并连接. 举例: SELECT * FROM LODGING WHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’);
SELECT * FROM LODGING WHERE MANAGER = ‘BILL GATES’ OR MANAGER = ’KEN MULLER’;
后两组记录以连接(CONCATENATION)的形式被组合成一个单一的集合. Explain Plan : 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)
本节和第 37 节似乎有矛盾之处. 47 CBO 下使用更具选择性的索引 基于成本的优化器(CBO,Cost-Based Optimizer)对索引的选择性进行判断来决定索引的使用 是否能提高效率. 如果索引有很高的选择性,那就是说对于每个不重复的索引键值,只对应数量很少的记录. 比如,表中共有 100 条记录而其中有 80 个不重复的索引键值. 这个索引的选择性就是 80/100 = 0.8 . 选择性越高,通过索引键值检索出的记录就越少. 如果索引的选择性很低,检索数据就需要大量的索引范围查询操作和 ROWID 访问表的 操作. 也许会比全表扫描的效率更低.
下列经验请参阅: a.如果检索数据量超过 30%的表中记录数.使用索引将没有显著的效率提高. b.在特定情况下,使用索引也许会比全表扫描慢,但这是同一个数量级上的 区别. 而通常情况下,使用索引比全表扫描要块几倍乃至几千倍! 48 避免使用耗费资源的操作 带有 DISTINCT,UNION,MINUS,INTERSECT,ORDER BY 的 SQL 语句会启动 SQL 引擎执行耗费资源的排序(SORT)功能. DISTINCT 需要一次排序操作,而其他的至少需要执行两次 排序. 例如,一个 UNION 查询,其中每个查询都带有 GROUP BY 子句,GROUP BY 会触发嵌入排序 (NESTED SORT) ; 这样,每个查询需要执行一次排序,然后在执行 UNION 时,又一个唯一排序 (SORT UNIQUE)操作被执行而且它只能在前面的嵌入排序结束后才能开始执行. 嵌入的排序的深 度会大大影响查询的效率. 通常,带有 UNION,INTERSECT 的 SQL 语句都可以用其他方式重写.
如果你的数据库的 SORT_AREA_SIZE 调配得好,使用 UNION,INTERSECT 也 是可以考虑的,毕竟它们的可读性很强 49 优化 GROUP BY 提高 GROUP BY 语句的效率,可以通过将不需要的记录在 GROUP BY 之前过滤掉.下面两个 查询返回相同结果但第二个明显就快了许多.
SELECT JOB,AVG(SAL) FROM EMP GROUP JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’
SELECT JOB,AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP JOB 本节和 14 节相同. 可略过. 50 使用日期 当使用日期是,需要注意如果有超过 5 位小数加到日期上,这个日期会进到下一天! 例如: 1. SELECT TO_DATE(‘01-JAN-93’+.99999) FROM DUAL;
’01-JAN-93 23:59:59’
SELECT TO_DATE(‘01-JAN-93’+.999999) FROM DUAL;
’02-JAN-93 00:00:00’
虽然本节和 SQL 性能优化没有关系,但是作者的功力可见一斑 51 使用显式的游标(CURSORs) 使用隐式的游标,将会执行两次操作. 第一次检索记录,第二次检查 TOO MANY ROWS 这个 exception . 而显式游标不执行第二次操作. 52 优化 EXPORT 和 IMPORT 使用较大的 BUFFER(比如 10MB,10,240,000)可以提高 EXPORT 和 IMPORT 的速度. ORACLE 将尽可能地获取你所指定的内存大小,即使在内存不满足,也不会报错.这个值至少要 和表中最大的列相当,否则列值会被截断. 译者: 可以肯定的是,增加 BUFFER 会大大提高 EXPORT,IMPORT 的效率. (曾经碰到过一个 CASE,增加 BUFFER 后,IMPORT/EXPORT 快了 10 倍!) 作者可能犯了一个错误: “这个值至少要和表中最大的列相当,否则列值会被截断. “ 其中最大的列也许是指最大的记录大小. 关于 EXPORT/IMPORT 的优化,CSDN 论坛中有一些总结性的贴子,比如关于 BUFFER 参数, COMMIT 参数等等,详情请查. 53 分离表和索引 总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于 ORACLE 内 部系统的对象存放到 SYSTEM 表空间里. 同时,确保数据表空间和索引表空间置于不同的硬盘上.
“同时,确保数据表空间和索引表空间置与不同的硬盘上.”可能改为如下更为准确 “同时,确 保数据表空间和索引表空间置与不同的硬盘控制卡控制的硬盘上.” （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |