写出高性能SQL语句方法(下)

1、用 >= 替代 > ; <= 替代 <

（高效）SELECT * FROM EMP E WHERE E.DEPT_NO >= 6;

（低效）SELECT * FROM EMP E WHERE E.DEPT_NO >6;

两者的区别在于，前者DBMS将直接跳到第一个DEPT_NO等于6的记录而后者将首先定位到DEPT_NO=6的记录并且向前扫描到第一个DEPT_NO大于6的记录。

2、用UNION替换OR（适用于索引列）

通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全资料表扫描。（注：以上规则只针对多个索引列有效）。

如果有column没有被索引，查询效率可以会因为你没有选择OR而降低。如下例子中，LOC_ID和REGION上都建有索引。

（高效）

SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10

UNION

SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE REGION = ‘MELBOURNE’;

（低效）

SELECT LOC_ID,LOC_DESC,REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = ‘MELBOURNE’

如果你坚持要用OR，那就需要返回记录最少的索引列写在最前面。

3、用IN来替换OR

这是一条简单易记的规则，但是实际的执行效果还须检验，在ORACLE8i下，两者的执行路

径似乎是相同的。例如：

（高效）SELECT * FROM LOCATION WHERE LOC_IN IN( 10, 20, 30 );

（低效）SELECT * FROM LOCATION WHERE LOC_IN = 10 OR LOC_ID = 20 OR LOC_ID = 30;

4、避免在索引列上使用IS NULL 和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。对于单列索引，如果列包含空值，索引中将不存在此记录，对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中。如唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为（123，null）,ORACLE将不接受下一条具有相同A，B值为（123，null）的记录插入。然而如果所有的索引列都为空，ORACLE将认为整个键值为空，而空不等于空。因此你可以插入1000条具有相同键值的记录，当然它们都是空！因为空值不存在于索引列中，所以WHERE子句中对索引列进行宿舍比较，将使ORACLE使用该索引。例如：

（高效（索引有效））SELECT * FROM DEPARTMENT WHERE DEPT_CODE >= 0;

（低效（索引失效））SELECT * FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL;

5、尽量使用索引的第一个列

如果索引是建立在多个列上，只有在它的第一个列（leading column）被where子句引用时，优化器才会选择使用该索引。这也是一条简单面重要的规则，当用索引的第十个列时，优化器使用了全资料表扫描而忽略了索引。

6、用UNION ALL替换UNION（如果有可能的话）

当SQL语句需要UNION两个查询结果集合时，这两个结果集合会心UNION ALL的方式被合并，然后在输出最终结果前进行排序。如果用UNION ALL替代UNION，这样排序就还是必要了。效率就会因此等到提高。需要注意的是，UNION ALL将重复输出两个结果集合中相同记录。因此各位还是要从业务需求分析使用UNION ALL的可行性。UNION将对结果集合排序，这个操作会使用到SORT_AREA_SIZE这个块记意体。对于这个块记意体的优化也是相当重要的。下面的SQL可以用来查询排序的消耗量。

（高效）SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS

WHERE TO_CHAR( TRAN_DATE, ’YYYYMMDD’) = ‘20211001’ 

UNION ALL

SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS

WHERE TO_CHAR( TRAN_DATE , ’YYYYMMDD’)= ‘20211001’

（低效）SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS

WHERE TO_CHAR( TRAN_DATE, ‘YYYYMMDD’ ) = ‘20211001’

UNION

SELECT ACCT_NUM,BALANCE_AMT FROM DEBIT_TRANSACTIONS

WHERE WHERE TO_CHAR( TRAN_DATE , ’YYYYMMDD’)= ‘20211001’

7、用WHERE替代ORDER BY

ORADER BY 子句只在两种严格的条件下使用索引。

a、表中的所有列必须包含丰相同的索引中并保持在索引中的排列顺序

b、表中的所有列必须定将为非空。

WHERE子句使用的索引和ORDER BY 子句中所使用的索引不能并列。例如：

表DEPT包含以下列：

DEPT_CODE PK NOT NULL

DEPT_DESC NOT NULL

DEPT_TYPE NULL

高效（使用索引）SELECT DEPT_CODE FROM DEPT WHERE DEPT_TYPE > 0;

低效（索引不被使用）SELECT DEPT_CODE FROM DEPT ORDER BY DEPT_TYPE;

8、避免改变索引列的类型

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

假设EMPNO是一个类型的索引列。

SELECT * FROM EMP WHERE EMPNO = ‘123’;

实际上，经过ORACLE类型转换，语句转化为：

SELECT * FROM EMP WHERE EMPNO = TO_NUMBER(‘123’);

幸运的是，类型转换没有发生在索引列上，索引的用途没有被改变。

现在假设EMP_TYPE是一个字符类型的索引列。

SELECT * FROM EMP WHERE EMP_TYPE = 123;

这个语句被ORACLE转换为：

SELECT * FROM EMP WHERE TO_NUMBER(EMP_TYPE) = 123;

因为内部发生的类型转换，这个索引将不会被用到。避免ORACLE对你的SQL进行隐式的类型转换，最好把转换用显式表现出来。注意当字符类型和数值类型比较时，ORACLE会优先转换数值类型别到字符类型。

9、需要当心的WHERE语句

某些SELECT语句中的WHERE子句不使用索引。这里有一些例子。

1. ‘ ！= ’将不使用索引。记住，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。
2. ‘ || ’是字符转接函数。和其它函数一样，会停用索引。
3. ‘ + ’是数学函数。和其它数学函数一样，会停用索引。
4. 相同的索引列不能互相比较，这将会启用全资料表扫描。

 10、如果检索资料量超过30%的表中记录数。使用索引将没有显著的效率提高。

在特定情况下，使用索引也许会比全资料表扫描慢，但这是同一个数量级上的区别。而通常情况下，使用索引比全资料表扫描要快几倍及至数千倍！

11、避免使用耗费资源的操作：

带有DISTINCT，UNION，MINUS，INTERSECT，ORDER BY 的SQL语句会启动SQL引擎，执行耗费资源的排序（SORT）功能。DISTINCT需要一次排序操作，而其它的至少需要执行两次排序。通常，带有UNION，MINUS，INTERSECT的SQL语句都可以用其它方式重写。如果你的资料库的SORT_AREA_SIZE调配得好，使用UNION，MINUS，INTERSECT也是可以考虑的，毕竟它们的可读性很强。

12、优化GROUP BY

提高GROUP BY语句的效率，可以通过将不需要的记录在GROUPBY之前过滤掉，下面两个查询返回相同结果但第一个明显较快。

（高效）SELECT JOB,AVG( SAL ) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP BY JOB;

（低效）SELECT JOB,AVG( SAL ) FROM EMP GROUP BY JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’;

以上是写出高性能的SQL语句的一些方法，如各位学者有其它更好的方法，欢迎大家评论...

此文不全，后续有待维护