全表扫描效率是最低的，比如没有创建索引，或者创建了索引，但是没有正确的去使用。
创建索引的目的就是为了使查询语句迅速的定位到数据记录。
我们有2个工作需要去做：
第一，如何正确的创建索引，就是在什么情况下，哪些字段应该创建索引，应该创建什么类型的索引。
第二，如何正确的使用索引，就是创建索引之后，你的sql写法会导致索引失效，那就没有意义了。
下面的写法，会导致索引失效：
避免在索引列上使用NOT通常，我们要避免在索引列上使用NOT，NOT会产生在和在索引列上使用函数相同的影响。当ORACLE遇到NOT，它就会停止使用索引转而执行全表扫描。
1
2
3
4
5
6
7
/*低效SQL: (这里，不使用索引)*/
SELECT * FROM DEPT
WHERE NOT DEPT_CODE = 0
/*高效SQL: (这里，使用索引)*/
SELECT * FROM DEPT
WHERE DEPT_CODE > 0
用 >= 替代 >如果DEPTNO上有一个索引
1
2
3
4
5
6
7
/*高效SQL*/
SELECT * FROM EMP
WHERE DEPTNO >=4
/*低效SQL*/
SELECT * FROM EMP
WHERE DEPTNO >3
用Union替换OR（适用于索引列）通常情况下，用UNION替换WHERE子句中的OR将会起到较好的效果。对索引列使用OR将造成全表扫描。注意，以上规则只针对多个索引列有效。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/*高效SQL*/
SELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE LOC_ID = 10
UNIONS
ELECT LOC_ID , LOC_DESC , REGION
FROM LOCATION
WHERE REGION = 'MELBOURNE'
/*低效SQL*/
SELECT LOC_ID,LOC_DESC,REGION
FROM LOCATION
WHERE LOC_ID = 10
OR REGION = 'MELBOURNE'
用IN替换OR
1
2
3
4
5
6
7
8
9
/*低效SQL*/
SELECT * FROM LOCATION
WHERE LOC_ID = 10
OR LOC_ID = 20
OR LOC_ID = 30
/*高效SQL*/
SELECT * FROM LOCATION
WHERE LOC_ID in （10，20,30）
避免在索引列上使用is null和is not null避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。
1
2
3
4
5
6
7
/*低效SQL：（索引失效）*/
SELECT * FROM DEPARTMENT
WHERE DEPT_CODE IS NOT NULL;
/*高效SQL：（索引有效）*/
SELECT * FROM DEPARTMENT
WHERE DEPT_CODE >=0;
避免改变索引列的类型
当比较不同数据类型的数据时， ORACLE自动对列进行简单的类型转换
1
2
3
4
5
6
7
8
9
/*假设EMP_TYPE是一个字符类型的索引列.*/
SELECT *
FROM EMP
WHERE EMP_TYPE = 123
/*这个语句被ORACLE转换为:*/
SELECT *
FROM EMP
WHERE TO_NUMBER(EMP_TYPE)=123
几种不能使用索引的WHERE子句（1）下面的例子中，‘!=’ 将不使用索引 ，索引只能告诉你什么存在于表中，而不能告诉你什么不存在于表中。
1
2
3
4
5
6
7
8
9
/*不使用索引*/
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT !=0；
/*使用索引*/
SELECT ACCOUNT_NAME
FROM TRANSACTION
WHERE AMOUNT > 0；
| （2）下面的例子中，‘ | ’是字符连接函数。就象其他函数那样，停用了索引。 |
1
2
3
4
5
6
7
8
9
10
/*不使用索引*/
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）下面的例子中，‘+’是数学函数。就象其他数学函数那样，停用了索引。
1
2
3
4
5
6
7
8
9
/*不使用索引*/
SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT + 3000 >5000；
/*使用索引*/
SELECT ACCOUNT_NAME，AMOUNT
FROM TRANSACTION
WHERE AMOUNT > 2000 ;
（4）下面的例子中，相同的索引列不能互相比较，这将会启用全表扫描。
1
2
3
4
5
6
7
8
9
/*不使用索引*/
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, ’%’)
优化GROUP BY提高GROUP BY语句的效率，可以通过将不需要的记录在GROUP BY之前过滤掉。
1
2
3
4
5
6
7
8
9
10
11
/*低效SQL*/
SELECT JOB,AVG（SAL）FROM EMP
GROUP BY JOB
HAVING JOB = 'PRESIDENT''
OR JOB = 'MANAGER'
/*高效SQL*/
SELECT JOB,AVG（SAL）FROM EMP
WHERE JOB = 'PRESIDENT'
OR JOB = 'MANAGER'
GROUP BY JOB
使用日期当使用日期时，需要注意如果有超过5位小数加到日期上，这个日期会进到下一天!
1
2
3
4
5
6
7
8
9
SELECT TO_DATE（'01-JAN-93'+.99999）
FROM DUAL
结果：
'01-JAN-93 23:59:59'
SELECT TO_DATE（'01-JAN-93'+.999999）
FROM DUAL
结果：
'02-JAN-93 00:00:00'
参考：
https://mp.weixin.qq.com/s/QgVeE_RFTUs4btM9RTmw0Q