用索引提高效率
选择最有效率的表名顺序(ORACLE 的解析器按照从右到左的顺序处理 FROM 子句中
的表名,FROM 子句中写在最后的表(基础表 driving table)将被最先处理)
WHERE 子句中的连接顺序：ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个
原理,表之间的连接必须写在其他 WHERE 条件之前, 那些可以过滤掉最大数量记录的条
件必须写在 WHERE 子句的末尾
SELECT 子句中避免使用 ‘ * ‘：需要写全查询的字段列名
提高访问效率：
a) 避免在索引列上使用 IS NULL 和 IS NOT NULL 操作
b) 用 UNION 替换 OR (适用于索引列)
c) 分离表和索引
总是将你的表和索引建立在不同的表空间内(TABLESPACES). 决不要将不属于 ORACLE 内
部系统的对象存放到SYSTEM表空间里. 同时,确保数据表空间和索引表空间置于不同的
硬盘上（通过指定表空间的方式可以实现这个要求）.
d) 共享 SQL 语句
e) WHERE 子句中的连接顺序．
示例 1.(低效,执行时间 156.3 秒)
SELECT * FROM EMP E
WHERE SAL > 50000
AND JOB = ‘MANAGER' AND 25 < (SELECT COUNT(*) FROM EMP
WHERE MGR=E.EMPNO);
示例 2(高效,执行时间 10.6 秒)
SELECT … FROM EMP E
WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO)
AND SAL > 50000
AND JOB = ‘MANAGER';
1
2
3
4
f) SELECT 子句中避免使用 ‘ * ‘
g) 使用 DECODE 函数来减少处理时间
例如:
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0020
AND ENAME LIKE ‘SMITH%';
SELECT COUNT(*),SUM(SAL)
FROM EMP
WHERE DEPT_NO = 0030
AND ENAME LIKE ‘SMITH%';
你可以用 DECODE 函数高效地得到相同结果
SELECT COUNT(DECODE(DEPT_NO,0020,'X',NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%';
h) 用 TRUNCATE 替代 DELETE
i) 尽量多使用 COMMIT
j) 用 Where 子句替换 HAVING 子句
例如:
低效:
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
高效
SELECT REGION,AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
GROUP BY REGION
(注意: HAVING 中的条件一般用于对一些集合函数的比较,如 COUNT( ) 等等. 除此而外,
一般的条件应该写在 WHERE 子句中)
k) 用 EXISTS 替代 IN
低效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC = ‘MELB')
高效:
SELECT *
FROM EMP (基础表)
WHERE EMPNO > 0
AND EXISTS (SELECT ‘X'
FROM DEPT
WHERE DEPT.DEPTNO = EMP.DEPTNO
AND LOC = ‘MELB')
l) 用 NOT EXISTS 替代 NOT IN
例如:
SELECT …
FROM EMP
WHERE DEPT_NO NOT IN (SELECT DEPT_NO
FROM DEPT
WHERE DEPT_CAT='A');
为了提高效率.改写为:
(方法一: 高效,左外连接)
SELECT ….
FROM EMP A,DEPT B
WHERE A.DEPT_NO = B.DEPT(+)
AND B.DEPT_NO IS NULL
AND B.DEPT_CAT(+) = ‘A'
(方法二: 最高效)
SELECT ….
FROM EMP E
WHERE NOT EXISTS (SELECT ‘X'
FROM DEPT D
WHERE D.DEPT_NO = E.DEPT_NO
AND DEPT_CAT = ‘A');
---------------------