常见SQL语句优化.ppt

1、Oracle Database几个常见SQL优化,2020年8月6日,Oracle SQL语句优化,2/25,内容提要,FROM子句与WHERE子句顺序 几个不推荐与推荐方法 实体视图与查询重写 新SQL语句使用建议 编码人员与表结构的理解,2020年8月6日,Oracle SQL语句优化,3/25,FROM多表的顺序,ORACLE的解析器按照从右到左的顺序 FROM子句中写在最后的表(基础表 driving table)将被最先处理 ； 选择记录条数最少的表作为基础表,-表 TAB1 有16,384 数据行 -表 TAB2 有10 个数据行 -方法1(最佳)： -选择TAB2作为基础表： s

2、elect count(*) from tab1 , tab2 where . . . -方法2(不佳)： -选择TAB2作为基础表 ： select count(*) from tab2,tab1 where . . .,2020年8月6日,Oracle SQL语句优化,4/25,FROM多表的顺序,FROM子句后三个表的情况：,例如：EMP表描述了LOCATION表和CATEGORY表的交集。 则EMP的顺序是关键：(这里EMP 是交叉表) 例1(效率高)： SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEE

3、N 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN; 例2(效率低)： 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;,2020年8月6日,Oracle SQL语句优化,5/25,WHERE子句的连接顺序,采用自下而上的顺序解析WHERE子句 ； 表之间连接必须写在其他WHERE条件之前； 那些可以过滤掉最大数量记录的条件

4、必须写在WHERE子句的末尾。,例1(低效)： SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 50000 AND JOB = MANAGER;,2020年8月6日,Oracle SQL语句优化,6/25,连接的次序,如果A表与B表存在多对一或一对一的关系，则下面的语句有区别： 下面SQL低效： 下面语句高效：,Select A.* from A, B where A.CITY = B.CITY,Select * from A where A.CITY in (select B.City from B),2020年8月6日,

5、Oracle SQL语句优化,7/25,内容提要,FROM子句与WHERE子句顺序 几个不推荐与推荐方法 实体视图与查询重写 新SQL语句使用建议 编码人员与表结构的理解,2020年8月6日,Oracle SQL语句优化,8/25,不推荐语句-不规范用法,不用“*”来代替所有列名 SELECT语句中可以用* 来列出该表的所有的列名 Oracle系统会通过查询数据字典来将*”转换成该表的所有列名 (动态问题 ) 用TRUNCATE代替DELETE 全表删除可直接用TRUNCATE 在PL/SQL可采用动态实现,2020年8月6日,Oracle SQL语句优化,9/25,不推荐语句-动态语句,关于

6、动态语句： Oracle 在PL/SQL中可用动态； Oracle系统动态在必须用才用，动态就是性能问题； 不建议在4GL等工具大量使用 SQL语句 一般不能用静态才使用动态： DROP TABLESPACE TRUNCATE TABLE ,2020年8月6日,Oracle SQL语句优化,10/25,不推荐语句-COMMIT,完整性下多用COMMIT语句: 确保数据完整前提下,释放程序语句获得的锁 redo log buffer 中的空间 ORACLE为管理上述3种资源中的内部花费,2020年8月6日,Oracle SQL语句优化,11/25,不推荐语句-少用LIKE,字符串的匹配： 要少用

7、 like %.%(不以 % 开头 ) ； 当对于 like %. 的 (不以 % 结尾)；,2020年8月6日,Oracle SQL语句优化,12/25,不推荐语句-少用ORDER BY,如要对结果进行排序，可考虑几种情况 ： 必须排序吗？； 结果集多大； 导致临时表空间的使用； 尽可能在内存完成排序 ALTER SESSION SET SORT_AREA_SIZE=2048000;,2020年8月6日,Oracle SQL语句优化,13/25,不推荐语句-视图嵌套,Oracle视图的嵌套问题： Oracle 允许创建视图时子查询可以是基表，也可是视图； 理论上视图可嵌套视图16层； 当视图

8、可嵌套层数超过3层以上时，性能下降非常严重；,2020年8月6日,Oracle SQL语句优化,14/25,不推荐语句-SYSDATE,Oracle内置函数SYSDATE产生意些开销 ： SYSDATE可在循环开始时用，在循序尽量避免，如下面量个例子：,DECLARE Lv_current_date DATE; BEGIN Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOP Lv_current_date := TRUNC(SYSDATE); END LOOP; Stop_watch.stop_timer; END; /,DEC

9、LARE Lv_current_date DATE := TRUNC(SYSDATE); Lv_final_date DATE; BEGIN Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOP Lv_final_date := Lv_current_date END LOOP; Stop_watch.stop_timer; END; /,2020年8月6日,Oracle SQL语句优化,15/25,不推荐语句- MOD,Oracle有些内置函数开销大，MOD是一个 ： 在IF 中使用MOD，就产生不必要的开销 ,BEGIN St

10、op_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOP IF MOD(lv_count_num,1000) = 0 THEN; DBMS_OUTPUT.PUT_LINE(Hit 1000;Total: |lv_count_num) END LOOP; Stop_watch.stop_timer; END; /,DECLARE Lv_count_inc_num PLS_INTEGER := 0 ; BEGIN Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOP Lv_cou

11、nt_inc_num := Lv_count_inc_num +1; IF lv_count_num=1000 THEN; DBMS_OUTPUT.PUT_LINE(Hit 1000;Total: |lv_count_num) Lv_count_inc_num := 0; END IF; END LOOP; Stop_watch.stop_timer; END; /,2020年8月6日,Oracle SQL语句优化,16/25,推荐方法-DECODE函数,Oracle 所有版本都支持DECODE ： DECODE表示译码，可快速将某个列的值转换成对应的结果； DECODE比IF THEN高效；

12、 ,select sid,serial#,username, DECODE(command, 0,None, 2,Insert, 3,Select, 6,Update, 7,Delete, 8,Drop, Other) cmmand from v$session where username is not null;,2020年8月6日,Oracle SQL语句优化,17/25,推荐方法-巧用ROWID,Oracle ROWID可立即确定行的位置 ： 下面例子用一般方法对数据行更新：,-使用 empid 列进行更新的例子： DECLARE CURSOR cur_employee IS SELE

13、CT empno,sal FROM EMP ; Lv_new_salary_num NUMBER; BEGIN Stop_watch.start_timer; FOR cur_rec IN cur_employee LOOP -确定工资增长 Lv_new_salary_num := cur_rec.salary ; UPDATE emp SET salary =lv_new_salary_num WHERE employee_id = cur_rec.employee.id; END LOOP; COMMIT; Stop_watch.stop_timer; END; /,2020年8月6日,O

14、racle SQL语句优化,18/25,推荐方法-巧用ROWID,Oracle ROWID可立即确定行的位置 ： 用ROWID对表的相应行进行更新：,-使用 empid 列进行更新的例子： DECLARE CURSOR cur_employee IS SELECT empno,sal ,ROWID FROM EMP ; Lv_new_salary_num NUMBER; BEGIN Stop_watch.start_timer; FOR cur_rec IN cur_employee LOOP -确定工资增长 Lv_new_salary_num := cur_rec.salary ; UPDA

15、TE emp SET salary =lv_new_salary_num WHERE employee_id = cur_rec.ROWID; END LOOP; COMMIT; Stop_watch.stop_timer; END; /,2020年8月6日,Oracle SQL语句优化,19/25,推荐方法-变量与类型,变量类型与表的列的类型差异 ： 避免变量类型与表的列的类型不同带来的转换问题； 不要定义了变量但不使用； Oracle新变量类型： 用BINARY_DOUBLE 替代NUMBER类型； 用BINARY_FLOAT 替代NUMBER类型,2020年8月6日,Oracle SQL

16、语句优化,20/25,推荐方法-自制临时表,Oracle 要从一个经过多次修改的表与另外进行关联，可能因为关键的表的数据过于分散而效率低下，下面方法可能是一个选择 ： 创建一个表空间； 将关联的创建为一个新的表存储在新表空间里； 用这个临时的表进行关联查询 ,2020年8月6日,Oracle SQL语句优化,21/25,推荐方法-临时表的使用,如要将一些中间结果在会话中使用，不要采用永久表，要用临时表 ： 在会话中有效； 数据可自动存入临时表空间； 不影响永久表空间； ,2020年8月6日,Oracle SQL语句优化,22/25,推荐方法-尽可能用MINUS,MINUS可替代NOT IN：

17、有时用MINUS 可比NOT IN 要快； 当子查询的结果较大时，不要用NOT IN；,2020年8月6日,Oracle SQL语句优化,23/25,内容提要,FROM子句与WHERE子句顺序 几个不推荐与推荐方法 实体视图与查询重写 新SQL语句使用建议 编码人员与表结构的理解,2020年8月6日,Oracle SQL语句优化,24/25,实体视图与查询重写,使用实体视图查询重写： 为了使用实体视图，优化器要重写查询语句； QUERY_REWRITE_ENABLED =TRUE ,且使用CBO时 ，当发出对基表的查询，Oracle会自动判断是否能利用这个基表的所有指定了ENABLE QUER

18、Y REWRITE语句的实体视图； 如果可以且根据统计信息判断通过查询实体视图代价更小，则Oracle自动重写查询语句，通过查询实体视图得到正确的结果。 QUERY REWRITE权限允许用户启用实体视图 DBMS_OLAP包有个选项使用实体视图,25/25,实体视图与查询重写-例子,CREATE MATERIALIZED VIEW sales_summary TABLESPACE sales_ts PARALLEL (DEGREE 4) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS SELECT s.zip, duct_t

19、ype, sum(s.amount) FROM sales s, product p WHERE duct_id = duct_id GROUP BY s.zip, duct_type;,26/25,实体视图与查询重写-例子,执行查询： 检查执行计划为：,SELECT s.zip, duct_type, sum(s.amount) FROM sales s, product p WHERE duct_id = duct_id GROUP BY s.zip, duct_type;,OPERATION NAME - - SELE

20、CT STATEMENT TABLE ACCESS FULL SALES_SUMMARY,2020年8月6日,Oracle SQL语句优化,27/25,内容提要,FROM子句与WHERE子句顺序 几个不推荐与推荐方法 实体视图与查询重写 新SQL语句使用建议 编码人员与表结构的理解,2020年8月6日,Oracle SQL语句优化,28/25,9i/10g 新WITH 子句,select 语句前先用with来查询出相应的数据块： 9i/10g允许用with 为一个子查询块分配一名字然后在SELECT语句使用该名字。Oracle 优化器将该名字当成内联视图或临时表来看待:,SQL WITH SU

21、MMARY as (SELECT dname,sum(sal) saltot, 2 round(avg(sal),2) avgsal, 3 count(distinct empno) nbremps 4 from emp join dept 5 on emp.deptno=dept.deptno 6 group by dname 7 ) 8 Select dname,nbremps,avgsal from SUMMARY 9 where saltot ( select sum(saltot)*.25 from SUMMARY ) 10* order by saltot DESC ; DNAME

22、 NBREMPS AVGSAL - - - ACCOUNTING 5 23307.6,2020年8月6日,Oracle SQL语句优化,29/25,用新SQL语句-MERGE,Oracle 10g新增加语句 ： INSERT 插入多表 MERGE语句实现更新与插入 ,2020年8月6日,Oracle SQL语句优化,30/25,用新SQL语句-MERGE,MERGE语句的UPDATE和INSERT从句： 9i 的MERGE语句要求指定Insert和Update从句,- 使用merge语句加WHERE从句可实现更新与插入操作： SQLMERGE INTO products p USING new

23、products np ON ( duct_id = duct_id ) WHEN MATCHED THEN UPDATE SET duct_name= duct_name,p.category = np.category WHERE p.category = DVD - WHEN NOT MATCHED THEN INSERT VALUES (duct_name, duct_name, np.category ) WHERE np.category != BOOKS ; SQLselect * from products PRODUCT_ID PRODUCT_NAME CATEGORY - - - 1501 VIVITAR 35MM ELECTRNCS 1502 OLMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HA