Oracle语句优化53个规则详解(05)

文章作者 100test 发表时间 2007:07:02 12:16:08
来源 100Test.Com百考试题网

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）
src="/oracle/js/wxgg_oracle.js">