SQL|Oracle SQL性能优化最常用的40条建议( 五 ) sql

SELECT STATEMENT Optimizer=CHOOSE
0 TABLE ACCESS (FULL) OF 'MULTIINDEXUSAGE‘
很明显, 当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
29. 使用UNION ALL替代UNION
当SQL语句需要UNION两个查询结果集合时，这两个结果集合会以UNION-ALL的方式被合并，然后在输出最终结果前进行排序。
如果用UNION ALL替代UNION，这样排序就不是必要了，效率就会因此得到提高。由于UNION ALL的结果没有经过排序，而且不过滤重复的记录，因此是否进行替换需要根据业务需求而定。
30. 对UNION的优化
由于UNION会对查询结果进行排序，而且过滤重复记录，因此其执行效率没有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%'
31. 避免改变索引列的类型
当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换。
/*假设EMP_TYPE是一个字符类型的索引列.*/
SELECT*
FROM EMP
WHERE EMP_TYPE = 123
/*这个语句被ORACLE转换为:*/
SELECT*
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
因为内部发生的类型转换，这个索引将不会被用到。几点注意：

当比较不同数据类型的数据时，ORACLE自动对列进行简单的类型转换。

如果在索引列上面进行了隐式类型转换，在查询的时候将不会用到索引。

注意当字符和数值比较时，ORACLE会优先转换数值类型到字符类型。

为了避免ORACLE对SQL进行隐式的类型转换，最好把类型转换用显式表现出来。

32. 使用提示（Hints）

FULL hint 告诉ORACLE使用全表扫描的方式访问指定表。

ROWID hint 告诉ORACLE使用TABLE ACCESS BY ROWID的操作访问表。

CACHE hint 来告诉优化器把查询结果数据保留在SGA中。

INDEX Hint 告诉ORACLE使用基于索引的扫描方式。

其他的Oracle Hints

ALL_ROWS。
FIRST_ROWS。
RULE。
USE_NL。
USE_MERGE。
USE_HASH 等等。

这是一个很有技巧性的工作。建议只针对特定的，少数的SQL进行hint的优化。
33. 几种不能使用索引的WHERE子句
1）下面的例子中，‘!=’ 将不使用索引。
索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。
/*不使用索引*/
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0；
/*使用索引*/
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT > 0；
2）下面的例子中，‘||’是字符连接函数。
就象其他函数那样，停用了索引。
/*不使用索引*/
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'；
3）下面的例子中，‘+’是数学函数。
就象其他数学函数那样，停用了索引。
/*不使用索引*/
SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000；
/*使用索引*/
SELECT ACCOUNT_NAME，AMOUNT
FROMTRANSACTION
WHEREAMOUNT>2000;
（4）下面的例子中，相同的索引列不能互相比较，这将会启用全表扫描。
/*不使用索引*/
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, ’%’)
34. 连接多个扫描