** 39. ** ** 总是使用索引的第一个列 **
如果索引是建立在多个列上 , 只有在它的第一个列 (leading column) 被 where 子句引用时 , 优化器才会选择使用该索引 .
** 译者按 : **
** 这也是一条简单而重要的规则 . ** ** 见以下实例 . **
** SQL> create table multiindexusage ( inda number , indb number , descr varchar2(10)); **
** Table created. **
** SQL> create index multindex on multiindexusage(inda,indb); **
** Index created. **
** SQL> set autotrace traceonly **
** SQL> select * from multiindexusage where inda = 1; **
** Execution Plan **
** ---------------------------------------------------------- **
** 0 SELECT STATEMENT Optimizer=CHOOSE **
** 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'MULTIINDEXUSAGE' **
** 2 1 INDEX (RANGE SCAN) OF 'MULTINDEX' (NON-UNIQUE) **
** SQL> select * from multiindexusage where indb = 1; **
** Execution Plan **
** ---------------------------------------------------------- **
** 0 SELECT STATEMENT Optimizer=CHOOSE **
** 1 0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE' **
** 很明显 , ** ** 当仅引用索引的第二个列时 , ** ** 优化器使用了全表扫描而忽略了索引 **
** 40. ** ** ORACLE ** ** 内部操作 **
当执行查询时 ,ORACLE 采用了内部的操作 . 下表显示了几种重要的内部操作 .
ORACLE Clause
|
内部操作
---|---
ORDER BY
|
SORT ORDER BY
UNION
|
UNION-ALL
MINUS
|
MINUS
INTERSECT
|
INTERSECT
DISTINCT,MINUS,INTERSECT,UNION
|
SORT UNIQUE
MIN,MAX,COUNT
|
SORT AGGREGATE
GROUP BY
|
SORT GROUP BY
ROWNUM
|
COUNT or COUNT STOPKEY
Queries involving Joins
|
SORT JOIN,MERGE JOIN,NESTED LOOPS
CONNECT BY
|
CONNECT BY
** 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,1,15) "Value" **
** from v$sysstat **
** where name like 'sort%' **
** 42. ** ** 使用提示 (Hints) **
对于表的访问 , 可以使用两种 Hints.
FULL 和 ROWID
FULL hint 告诉 ORACLE 使用全表扫描的方式访问指定表 .
例如 :
SELECT /*+ FULL(EMP) */ *
FROM EMP
WHERE EMPNO = 7893;
ROWID hint 告诉 ORACLE 使用 TABLE ACCESS BY ROWID 的操作访问表 .
通常 , 你需要采用 TABLE ACCESS BY ROWID 的方式特别是当访问大表的时候 , 使用这种方式 , 你需要知道 ROIWD 的值或者使用索引 .
如果一个大表没有被设定为缓存 (CACHED) 表而你希望它的数据在查询结束是仍然停留
在 SGA 中 , 你就可以使用 CACHE hint 来告诉优化器把数据保留在 SGA 中 . 通常 CACHE hint 和 FULL hint 一起使用 .
例如 :
SELECT /+ FULL(WORKER) CACHE(WORKER)/ *
FROM WORK;
索引 hint 告诉 ORACLE 使用基于索引的扫描方式 . 你不必说明具体的索引名称
例如 :
SELECT /*+ INDEX(LODGING) */ LODGING
FROM LODGING
WHERE MANAGER = ‘BILL GATES’;
在不使用 hint 的情况下 , 以上的查询应该也会使用索引 , 然而 , 如果该索引的重复值过多而你的优化器是 CBO, 优化器就可能忽略索引 . 在这种情况下 , 你可以用 INDEX hint 强制 ORACLE 使用该索引 .
ORACLE hints 还包括 ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等 .
** 译者按 : **
** ** ** 使用 hint , ** ** 表示我们对 ORACLE ** ** 优化器缺省的执行路径不满意 , ** ** 需要手工修改 . **
** 这是一个很有技巧性的工作 . ** ** 我建议只针对特定的 , ** ** 少数的 SQL ** ** 进行 hint ** ** 的优化 . **
** 对 ORACLE ** ** 的优化器还是要有信心 ( ** ** 特别是 CBO) **