oracle优化等深度学习资料 学习的速度来拿啊 ...........................................

[fov]

包括：数据库设计方法.doc,Database Design.ppt
Oracle的SQL语句执行效率问题查找与解决方法.doc
Oracle数据库设计要做到五戒.txt,
oracle性能优化总结.doc,
SQL语句全优化.doc,
数据库设计中的14个实用技巧.doc,
优化Oracle库表设计的若干方法.doc,
怎样看懂Oracle的执行计划.doc等
更多在
http://download.csdn.net/source/1824001

开始发个帖子少个东西弥补
其中一个文档 oracle性能优化总结.doc,

1. 选用适合的ORACLE优化器
ORACLE的优化器共有3种: a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)
设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖. 为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性. 如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器. 在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.
2. 访问Table的方式
ORACLE 采用两种访问表中记录的方式:
a. 全表扫描 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描,这样的访问方式是效率最低的.
b. 通过ROWID访问表 你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.
3. 共享SQL语句
为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它 和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询. 数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE 提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句. 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须 完全相同(包括空格,换行等). 共享的语句必须满足三个条件:
A. 字符级的比较: 当前被执行的语句和共享池中的语句必须完全相同. 例如:
SELECT * FROM EMP;
和下列每一个都不同
SELECT * from EMP;
Select * From Emp;
SELECT * FROM EMP;
B. 两个语句所指的对象必须完全相同: 例如:
用户 对象名 如何访问
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
考虑一下下列SQL语句能否在这两个用户之间共享.
SQL 能否共享 原因
select max(sal_cap) from sal_limit 不能 每个用户都有一个private synonym - sal_limit, 它们是不同的对象
select count(*) from work_city where sdesc like 'NEW%' 能 两个用户访问相同的对象public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id 不能 用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同
C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables) 例如： 第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;

select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;
4. 选择基础表
所谓基础表是被最先被访问的表,通常是以全表扫描的方式被访问,由于优化器不同,SQL语句的基础表的选择也不同.
1.COST优化器会检查SQL中每个表的物理大小,索引状态,然后选择花费最底的执行路径.他会自己选择基础表
2.RULE优化器,在所有连接条件都有索引对应的前提下,选择FROM子句中最后那个表作为基础表.
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并. 例如: 表 TAB1 16,384 条记录 表 TAB2 1 条记录。 选择TAB2作为基础表 (最好的方法) ：
select count(*) from tab1,tab2
执行时间0.96秒； 选择TAB2作为基础表 (不佳的方法)：
select count(*) from tab2,tab1
执行时间26.09秒。
如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表. 例如: EMP表描述了LOCATION表和CATEGORY表的交集.
SELECT * FROM LOCATION L , CATEGORY C, EMP E
WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN
将比下列SQL更有效率
SELECT * FROM EMP E , LOCATION L , CATEGORY C
WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000
5. WHERE子句中的连接顺序．
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾. 例如: (低效,执行时间156.3秒)：
SELECT … FROM EMP E
WHERE SAL > 50000 AND JOB = 'MANAGER' AND 25 50000 AND JOB = 'MANAGER';
6. SELECT子句中避免使用 ' * '
当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用 '*' 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.
7. 减少访问数据库的次数
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量. 例如, 以下有三种方法可以检索出雇员号等于342或291的职员.
方法1 (最低效)
SELECT EMP_NAME , SALARY , GRADE FROM EMP
WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;
方法2 (次低效)
DECLARE CURSOR C1 (E_NO NUMBER) IS SELECT EMP_NAME,SALARY,GRADE
FROM EMP
WHERE EMP_NO = E_NO;
BEGIN OPEN C1(342);
FETCH C1 INTO …,..,.. ; ….. OPEN C1(291);
FETCH C1 INTO …,..,.. ;
CLOSE C1; END;
方法3 (高效)
SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE
FROM EMP A,EMP B
WHERE A.EMP_NO = 342 OR B.EMP_NO = 291;
注意: 在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200
8. 使用DECODE函数来减少处理时间
使用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%';
类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.
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';
上面的3个查询可以被合并成一个:
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);

[fov]

下面还有40条 太多了

[sweetBug]

谢谢，mark先！

[hailang1118]

留名，回家研究

[fov]

11-20

11. 用TRUNCATE替代DELETE
当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是 恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
12. 尽量多使用COMMIT
只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少: COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费
13. 计算记录条数
和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO) (译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)
14. 用Where子句替换HAVING子句
避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. 例如:
低效:
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子句中)
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 多个Column 例子:
低效:
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;
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;
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;
函数是执行在数据库服务器端的,所以他的执行效率当然要比在客户端执行的速度要快的多.所以一般较复杂业务的SQL操作都用存储过程或者函数来完成.
17. 使用表的别名(Alias)
当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误. (译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)
18. 用EXISTS替代IN
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 因为EXISTS只返回true or false不用返回结果集,而IN和NOT 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')
(译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)
19. 用NOT EXISTS替代NOT IN
在子查询中,NOT IN子句将执行一个内部的排序和合并,会降低效率. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或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');
20. 用表连接替换EXISTS
通常来说 , 采用表连接的方式比EXISTS更有效率
SELECT ENAME
FROM EMP E
WHERE EXISTS (SELECT 'X' FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = 'A');
(更高效)
SELECT ENAME
FROM DEPT D,EMP E
WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = 'A' ;
这个很好理解,因为前一个在where子句中使用了子查询,第二个没有使用子查询,前面有介绍.而上一节EXISTS的高效只是针对与IN来说的
(译者按: 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)

[fov]

21-40


21. 用EXISTS替换DISTINCT
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT这样的高消耗的执行语句,尽量使用其他方法替换.比如使用EXISTS.即使在EXISTS的WHERE子句中使用子查询也比DISTINCT的效率要高. 例如:
低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME
FROM DEPT D,EMP E
WHERE D.DEPT_NO = E.DEPT_NO
高效:
SELECT DEPT_NO,DEPT_NAME
FROM DEPT D
WHERE EXISTS ( SELECT 'X' FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.
22. 用索引提高效率
用索引来提高效率,是最普遍的提高效率的方法,但是同时带来的弊端就是降低空间效率,通过索引可以避免全表扫描,并且它提供了主键的唯一性验证.(LONG和LONG RAW类型字段是不能作为索引的).通常大型表中使用索引特别有效.
但是,索引提高的执行效率是用空间效率换来的.索引需要空间来存储.也需要定期维护.每当有记录在表中增加或者删减的时候都会修改索引本身,这也就意味着每次执行INSERT,DELETE,UPDATE将多付出几次磁盘的I/O操作.因为索引需要额外的存储空间和处理,所以那些不必要的索引往往也会影响查询的执行效率.
索引的操作
ORACLE对索引的访问有两种:
A.索引唯一扫描(INDEX UNIQUE SCAN)
在一个表中有两个索引,在列C1上建立一个唯一性索引INDEX_KEY和C2列的一个非唯一性索引INDEX_01,在执行如下语句的时候
select * from table where C1=1;
这个时候唯一性索引INDEX_KEY将被访问,获得响应的ROWID然后通过ROWID访问的方式进行下一步检索.如果被索引返回的列包含于INDEX列中,那么ORACLE是不会通过ROWID访问表的.如:
select C1 ,... from table where C1=1;
B.索引的范围查询(INDEX RANGE SCAN)
基于一个范围的检索和基于一个非唯一性索引的检索.这两种情况适用索引的范围查询.
select C1 from table where C1 like '?';
where子句包含一系列值,ORACLE将通过索引范围查询的方式查询唯一性索引INDEX_KEY,由于索引范围查询将返回一组值,他的效果就要比索引唯一扫描低.
select C1 from table where C2=3;
这个SQL首先按照非唯一性索引进行索引范围查询(得到所有符合条件记录的ROWID),然后通过ROWID访问表,得到C1的值.由于C2是一个非唯一索引,所以数据库不进行索引唯一扫描.由于返回列C1不在INDEX_01中,所以在索引范围查询后会进行一个ROWID访问表的操作.
PS:WHERE子句中如果索引列对应的值有匹配符开始,那么索引将不被使用.
23. 识别'低效执行'的SQL语句
用下列SQL工具找出低效SQL:
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0 AND BUFFER_GETS > 0 AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS <0
order by size="+2" color="blue">
24. 使用TKPROF 工具来查询SQL性能状态
SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统. 设置SQL TRACE在会话级别: 有效 ALTER SESSION SET SQL_TRACE TRUE 设置SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录
(译者按: 这一节中,作者并没有提到TKPROF的用法, 对SQL TRACE的用法也不够准确, 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 大家可以参考ORACLE手册来了解具体的配置. )
25. 用EXPLAIN PLAN 分析SQL语句
EXPLAIN PLAN 是一个很好的分析SQL语句的工具,它甚至可以在不执行SQL的情况下分析语句. 通过分析,我们就可以知道ORACLE是怎么样连接表,使用什么方式扫描表(索引扫描或全表扫描)以及使用到的索引名称. 你需要按照从里到外,从上到下的次序解读分析的结果. EXPLAIN PLAN分析的结果是用缩进的格式排列的, 最内部的操作将被最先解读, 如果两个操作处于同一层中,带有最小操作号的将被首先执行. NESTED LOOP是少数不按照上述规则处理的操作, 正确的执行路径是检查对NESTED LOOP提供数据的操作,其中操作号最小的将被最先处理.
译者按: 通过实践, 感到还是用SQLPLUS中的SET TRACE 功能比较方便. 举例:
SQL> list 1 SELECT * 2 FROM dept, emp 3* WHERE emp.deptno = dept.deptno
SQL> set autotrace traceonly /*traceonly 可以不显示执行结果*/
SQL> / 14 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 NESTED LOOPS 2 1 TABLE ACCESS (FULL) OF 'EMP' 3 1 TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' 4 3 INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE) Statistics ---------------------------------------------------------- 0 recursive calls 2 db block gets 30 consistent gets 0 physical reads 0 redo size 2598 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processed
通过以上分析,可以得出实际的执行步骤是:
1. TABLE ACCESS (FULL) OF 'EMP'
2. INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
3. TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
4. NESTED LOOPS (JOINING 1 AND 3)
注: 目前许多第三方的工具如TOAD和ORACLE本身提供的工具如OMS的SQL Analyze都提供了极其方便的EXPLAIN PLAN工具.也许喜欢图形化界面的朋友们可以选用它们.
这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用 HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。
另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。
26. 多个平等的索引
在SQL语句包含多个表时,且使用分布在表上的多个索引时,ORACLE会同时使用多个索引并运行时对他们的记录进行合并,检索出仅对全部索引有效的记录.
唯一性索引的等级是最高的,但是,只有当索引在WHERE子句中与常量比较的时候才有效,如果与其他表的索引相比较,那么这个子句在优化器中等级是最低的.
如果不同表的两个同等级的索引被引用,那么将有FROM子句中表的顺序决定,既FROM最后面的表的索引优先级最高.
如果同表的两个相同等级的索引被引用,那么WHERE子句中最先被引用的索引将有最高优先级.
select name from emp where age=25(非唯一性索引) and g_id='1001'(非唯一性索引)这个时候优化器会先访问索引age然后在与索引g_id的结果集进行合并.
27. 等式比较和范围比较
当WHERE子句中存在索引列的比较时,ORACLE是不会优先对进行比较的索引进行访问的.所以尽量避免对索引列进行范围比较.进行范围比较的索引访问级别很低.即使是唯一性索引也是一样.
28. 强制索引失效
如果存在两个以上相同级别的索引,可以使用强制命令ORACLE只使用其中一个(检索出的记录数量少)
select emp.name from emp where age = 25 and g_id||''='a';--->>好奇怪的语法.
29. 避免对索引进行计算
在WHERE子句中对索引进行计,ORACLE就不会对索引进行访问,而直接对表进行全表扫描.这个规则非常实用.
30. 索引的自动选择规则
如果一个表中有很多索引,但是只有一个是唯一性索引,那么ORACLE只会对唯一性索引进行访问而完全忽略其他非唯一性索引.......
31.避免对索引使用NOT
这会让ORACLE忽略索引而使用全表扫描
32.用>=代替>
两者的区别在于,>=比>少检索一些记录,从而节约执行扫描表所用时间.
33.用UNION替代OR
通常情况下UNION替代OR会起到提升效率的作用,因为OR将会造成全表扫描,但是这只适用于多个索引列,如果有列没有被索引,那么会事如其反.
如果坚持使用or的话,那么将返回记录最少的索引列写在最前面可以提高效率.
34.用IN替换OR
对于这个用法,褒贬不一,也有说实际效率差不多的.这个可能根据检索的数据量多少有关吧.待验证.
35.避免在索引列上使用IS NULL OR IS NOT NULL
这会造成索引不被使用。
36.使用索引的第一个列
如果索引建立在多个列上,那么只有在第一个列被where引用的时候,ORACLE才回选择使用该索引.这是非常重要的规则,如果仅使用第二个列,那么ORACLE会忽略该索引而进行全表扫描.
同时,如果表记录不是很多的情况下,ORACLE优化器会忽略索引而直接进行表扫描.
37.用UNION-ALL替换UNION
当使用UNION的时候,这两个结果集会以UNION-ALL的方式合并,然后对输出结果进行排序,而用UNION-ALL的话,排序就可以避免了.
38. HINTS
HINTS分FULL,ROWID两种,FULL为全表扫描,后者则使用TABLE ACCESS BY ROWID访问表.
一般情况下,我们将采用ROWID方式,特别是大表.这种方式需要知道ROWID或者索引,当没有设定缓存切希望表数据在查询结束后保留,那么就需要在SGA中使用CACHE HINT来告诉优化器将数据保存在SGA中.
39. ORDER BY对索引的要求
ORDER BY要求ORFER BY子句中所有的列必须包含在相同的索引中并保持在索引中的排列顺序.且必须定义为非空列.
正是由于ORDER BY使用索引的苛刻要求,所以很少有人注意到ORDER BY是可以使用索引的.
40.避免改变索引列的类型
当比较不同数据类型的数据的时候,ORACLE会进行简单的类型转换,但是这样的类型转换是需要代价的,需要消耗掉一部分ORACLE的处理时间,但是如果在索引列上发生这样的使用,那么索引将不会被访问.会引起FULL表访问.
41.关于WHERE的注意
'!='将导致火印不会被使用,索引只能告诉我们什么存在于表中,不能告诉我们什么不存在于表中 (貌似很笨的说).
'||'是字符连接函数,他也将会使索引失效.
' + '也将停用索引.
相同的索引不能相互比较,这将导致全表扫描.

[fov]

D

[phw362757820]

Mark and thanks!

[fov]

d