Oracle指导学习.ppt

1、指导学习,SQL 查询性能优化,2,6-1合理使用索引,创建索引的根本目的是:提高查询效率 使用原则如下: 1.在条件表达式中经常用到的不同值较多的列上建立索引 2.在不同值少的列上不要创建索引或位图索引. 比如：在员工表的性别列上只有“男”或者“女”两个不同值，如果建立索引，不但不会提高查询效率，反而会降低更新速度 3.在经常进行连接，但是没有指定为外键的列上建立索引,3,6-1合理使用索引,在频繁进行排序或分组的列上建立索引 如果待排序的列有多个，可以建立组合索引 如：order by D,E 那要注意：在建立索引时 D列和E列和排序后面的顺序要相同 如果条件表达式中经常对某个列应用某个函

2、数后指定查询条件，则应建立函数索引,4,6-1合理使用索引,下面情况的SQL，不会用到列上的索引，应尽量避免使用： 存在数据类型的隐形转换时 列上有数学运算时 在索引上只有sal的值，没有sal*2的值,Select * from emp where empno = 123;,Select * from emp where sal*2 1000;,Select * from emp where sal1000/2 ;,5,6-1合理使用索引,使用不等于()运算时 使用substr字符串函数时 %通配符在第一个字符时 字符串连接( | )时,Select * from emp where dep

3、tno 10;,Select * from emp where substr(ename,1,3)=SMI;,Select * from emp where ename like%th;,Select * from emp where ename | abc = SMITHabc;,6,6-2避免或简化排序,应当简化或避免对大型表进行重复的排序 以下是需要排序的情况 SQL中包含Group By子句 SQL中包含Order By子句 SQL中包含Distinct子句 SQL中包含Minus或Union子句 in子句中的SQL子查询,7,6-2避免或简化排序,以下情况不能有效地利用索引 待排序列

4、没有全部建立索引 例如：order by D,E 而在表中只在D列上创建了索引 Group by 或者 Order by 子句中列的顺序与索引的列的顺序不同 Create index i_emp on emp(empno,ename) Order by ename,empno 排序的列来自不同的表(索引不能跨表) 为了避免不必要的排序，就要正确地增建索引，合理地 合并数据库表。如果排序不可避免，那么应当试图简化 它，如缩小排序的列的范围等,8,6-3消除对大表的扫描,在连接查询中，对表的顺序存取可能对查询效率产生致命的影响。避免这种情况的主要方法就是对连接的列进行索引。例如有两个表，学生表(学

5、号、姓名、年龄)和选课表(学号、课程号、成绩)。如果两个表要做连接，就要在”学号”这个连接字段上建立索引。,9,6-3消除对大表的扫描,使用并集来避免顺序存取。尽管在所有的检查列上都有索引，但某些形式的 where 子句强迫数据库使用顺序存取。例如 尽管我们在B和C列上都建立了索引，但是在上面语句中，优化器还是使用顺序存取方式扫描整个表。因为这个语句要检索的是分离的行的集合，可改为如下语句,Select * from abc where a10 or b10;,Select * from abc where a10 Union Select * from abc where b10,10,6-

6、4避免困难的通配符匹配,复杂的通配可能导致耗时的查询。例如 即使在zipcode字段上建立了索引，在这种情况下也还是采用顺序扫描的方式。应改成,Select * from customer where zipcode like 98_ _ _;,Select * from customer where zipcode =98000 and zipcode 99000,11,6-5调整子查询的性能,非关联子查询 非关联子查询时子查询只会执行一次，而且结果集是已经排 序完毕的，并保存在一个Oracle的临时段中，其中的每一个 记录在返回时都会被父查询所引用。在子查询返回大量记录 的情况下，将这些结

7、果集排序，以及将临时数据段进行排序 会增加大量的系统开销。 select emp_name from emp_number where emp_id in (select emp_id from emp_func);,12,6-5调整子查询的性能,关联子查询 对返回到父查询的记录来说，子查询会每行执行一次。因此必须保证子查询尽可能用到索引。关联子查询的系统开销更高。 select emp_name from emp_number where emp_id in (select emp_id from emp_func where emp_number.emp_id = emp_func.emp

8、_id); 查询嵌套的层次越多，效率越低，因此应当尽量避免子查 询。如果子查询不可避免，那以要在子查询中过滤掉尽可能 多的行。,13,6-6EXISTS和IN运算符,带IN的关联子查询是多余的，因为IN子句和子查询中相关操作的功能是一样的。如 为非关联子查询指定EXISTS子句是不适当的，因为这样会产生笛卡尔积。如,Select emp_name from emp_member where emp_id in (select emp_id from emp_func where emp_member.emp_id = emp_func.emp_id);,Select emp_name from

9、 emp_member Where exists(select emp_id from emp_func);,14,6-6EXISTS和IN运算符,尽量不要使用NOT IN 子句，虽然使用MINUS子句要进行两次查询，使用MINUS子句依然比 NOT IN 子句快,Select emp_name from emp_member where emp_id in (select emp_id from emp_member minus Select emp_id from emp_func where func_id like 81% );,15,通过索引提高查询效率,declare record

10、count integer := 1000000; - 100万条记录 begin for i in 1.recordcount loop insert into dumpy(id, name, rand) values ( i, dbms_random.string(x, 8), abs(dbms_random.random) / 100.0); if mod(i, 1000) = 0 then commit; - 每插入1000条提交 end if; end loop; end; 数据添加成功后，查询Rand值介于1000到2000之间的行。 添加索引，比较优化前后查询速度的变化。 提示:

11、执行命令 SET TIMING ON,可以显示每次语句执行时间,向dumpy表中添加100万行记录，其中ID列是有序的整数，Name 是随机的字符串，Rand列是随机的数值 create table dumpy ( id number(10), name varchar2(10), rand number(10,2) );,16,代码分析,Select d.* from dept d where exists(select e.deptno from emp e where e.deptno=d.deptno),Select * from dept where deptno in(select

12、 deptno from emp);,比较下列二个查询哪个效率更高些？为什么? 如果emp表有100万条记录，两个查询的效果又会如何?,17,代码分析,用Dumpy表的ID代表deptno，那么第一个查询可以改写为:,SELECT * FROM dept WHERE deptno IN (SELECT id FROM dumpy);,第二个查询应该怎么改写？,SELECT d.* FROM dept d WHERE EXISTS (SELECT e.id FROM dumpy e WHERE e.id = d.deptno);,CREATE INDEX IND_DUMPY_ID ON DUMP

13、Y(ID);,添加索引，对比优化前后的效率,18,去掉表中的重复数据,方法一(效率最低 包含了 NOT IN),DELETE FROM dumpy WHERE id IN (SELECT id FROM dumpy GROUP BY id HAVING COUNT(*) 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM dumpy GROUP BY id HAVING COUNT(*) 1);,19,去掉表中的重复数据,方法二 方法三,DELETE FROM dumpy d WHERE d.rowid (SELECT MIN(x.rowid) FROM dumpy x WHERE d.id=x.id);,DELETE FROM dumpy WHERE ROWID IN (SELECT A.ROWID FROM dumpy A,dumpy B WHERE A.id=B.id AND A.ROWID B.ROWID);,20,分页查询,方法一 适合于小数据量 方法二 速度较稳定 推荐,SELECT * FROM ( SELECT ROWNUM rn, d.* FROM dumpy d WHERE