1、合适的oracle优化器 （rule、cost、choose）
2、访问表的方式
a、全表扫描
b、rowid访问 rowid记录的数据的物理地址信息，而oracle采用索引实现数据及其存放物理位置rowid之间的关联，故基于该些索引的查询就可以提高性能
3、共享sql 满足一下3个条件
a、字符级比较 ： 被执行语句必须和共享池中语句完全相同
b、两语句所指对象相同
c、两语句须使用相同名字的绑定变量
4、有效率的表名顺序 （基于rule优化器才有效）
oracle解析器从右到左顺序处理from中的表名，因此最右边必须选择记录数最少的表（基础表），oracle处理多表时，先扫表基础表对其进行排序，然后继续第二个表，最后将第二个表检索的记录与第一个表中合适的记录进行合并
5、where子句中条件的连接顺序
oracle采用自下而上解析where子句，因此，表间连接必须写在其他where条件前，那些可以过滤掉最大数量记录的条件必须写在where子句末尾
6、select避免使用' * '
解析过程中，oracle会将*以此转换成所有列名，该工作通过查询数据字典完成，将耗费时间
7、减少访问DB的次数
执行SQL时,ORACLE做了许多工作：解析sql、估算索引利用率、绑定变量、读数据块等，因此减少访问DB次数，可减少DB的工作量
8、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,0)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%'
9、整合简单、无关联的数据库访问（效率提升，但可读性大降，须权衡利弊）
SELECT NAME
FROM EMP
WHERE EMP_NO = 1234;
SELECT NAME
FROM DPT
WHERE DPT_NO = 10 ;
SELECT NAME
FROM CAT
WHERE CAT_TYPE = ‘RD';
合并成为：
SELECT E.NAME , D.NAME , C.NAME
FROM CAT C , DPT D , EMP E,DUAL X
WHERE NVL(‘X',X.DUMMY) = NVL(‘X',E.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',D.ROWID(+))
AND NVL(‘X',X.DUMMY) = NVL(‘X',C.ROWID(+))
AND E.EMP_NO(+) = 1234
AND D.DEPT_NO(+) = 10
AND C.CAT_TYPE(+) = ‘RD';
10、删除重复记录（使用rowid）
DELETE FROM EMP E
WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X
WHERE X.EMP_NO = E.EMP_NO);
11、TRUNCATE 代替DELETE
通常情况，回滚段用来存放可恢复信息，若没有COMMIT事务，ORACLE将数据恢复到执行删除命令前的状态
而TRUNCATE不会产生任何REDO信息，回滚段也不再存放任何可恢复信息，数据不能恢复，故资源很少调用，执行时间短
12、尽量多使用COMMIT 会及时释放资源，故需求会减少，性能也能得到提升（同时也要考虑对事务完整性的影响）
释放的资源：
a、回滚段上用于恢复数据的信息
b、程序SQL获得的锁
c、redo log buffer 中的空间
d、管理上述3种的内部花费
13、计算记录条数
COUNT(*)
COUNT(1)
COUNT(索引列) 最快
14、Where子句替换HAVING子句
HAVING只会在检索出所有记录之后才对结果集进行过滤，这里存在排序，总计等操作。（HAVING中的条件一般用于对集合函数的比较，如count（）等，而一般的条件应写入WHERE子句中）
WHERE子句会限制记录的数目，以减少上述方面的开销
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
GROUP BY REGION
HAVING REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
高效
SELECT REGION，AVG(LOG_SIZE)
FROM LOCATION
WHERE REGION != ‘SYDNEY'
AND REGION != ‘PERTH'
GROUP BY REGION
15、减少对表的查询
含有子查询的SQL中，注意减少对表的查询
低效
SELECT TAB_NAME
FROM TABLES
WHERE TAB_NAME = ( SELECT TAB_NAME
FROM TAB_COLUMNS
WHERE VERSION = 604)
AND DB_VER= ( SELECT DB_VER
FROM TAB_COLUMNS
WHERE VERSION = 604)
高效
SELECT TAB_NAME
FROM TABLES
WHERE (TAB_NAME,DB_VER)
= ( SELECT TAB_NAME,DB_VER)
FROM TAB_COLUMNS
WHERE VERSION = 604) //多列子查询来减少对表的查询次数
UPDATE EMP
SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),
SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020;
高效:
UPDATE EMP
SET (EMP_CAT, SAL_RANGE)
= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)
FROM EMP_CATEGORIES)
WHERE EMP_DEPT = 0020; //使用子查询更新数据
16、通过内部函数提高SQL效率
SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)
FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H
WHERE H.EMPNO = E.EMPNO
AND H.HIST_TYPE = T.HIST_TYPE
GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;
通过调用下面的函数可以提高效率.
FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2
AS
TDESC VARCHAR2(30);
CURSOR C1 IS
SELECT TYPE_DESC
FROM HISTORY_TYPE
WHERE HIST_TYPE = TYP;
BEGIN
OPEN C1;
FETCH C1 INTO TDESC;
CLOSE C1;
RETURN (NVL(TDESC,'?'));
END; //使用游标返回指定某HIST_TYPE的记录的TYPE_DESC字段值
FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2
AS
ENAME VARCHAR2(30);
CURSOR C1 IS
SELECT ENAME
FROM EMP
WHERE EMPNO=EMP;
BEGIN
OPEN C1;
FETCH C1 INTO ENAME;
CLOSE C1;
RETURN (NVL(ENAME,'?'));
END; //使用游标返回指定某EMPNO的记录的ENAME字段值
SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),
H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)
FROM EMP_HISTORY H
GROUP BY H.EMPNO , H.HIST_TYPE;
17、使用表的别名Alias
当SQL语句连接多表时，使用别名并将其前缀于每个Column上，可以减少解析时间、减少那些由Column歧义引起的语法错误
18、用EXISTS替代IN
许多基于基础表的查询中，为满足一个条件，往往需对另一个进行联接，使用EXISTS(或NOT EXISTS)通常可提高查询的效率
低效:
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')
19、用NOT EXISTS替代NOT IN
在子查询中，NOT IN子句会执行一个内部排序和合并，无论何种情况下，NOT IN是最低效（因为它执行了一个全表遍历）
可以改用外连接或NOT EXISTS
低效
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');
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
