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