常见SQL语句优化

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

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

3、D 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCN; 例2(效率低)：SELECT * FROM EMP E , LOCATION L , CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 2000; 2022年5月16日Oracle SQL语句优化5/25WHERE子句的连接顺序 u采用自下而上的顺序解析WHERE子句 ；u表之间连接必须写在其他WHERE条件之前； u那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。

4、 n 例1(低效)：SELECT FROM EMP EWHERE SAL 50000 AND JOB = MANAGERAND 25 ( SELECT COUNT(*) FROM EMPWHERE MGR=E.EMPNO);例2(高效)：SELECT FROM EMP EWHERE 25 50000 AND JOB = MANAGER; 2022年5月16日Oracle SQL语句优化6/25连接的次序u如果A表与B表存在多对一或一对一的关系，则下面的语句有区别： n下面SQL低效：n下面语句高效：Select A.* from A, B where A.CITY = B.CITYSelect

5、 * from A where A.CITY in (select B.City from B)2022年5月16日Oracle SQL语句优化7/25内容提要uFROM子句与WHERE子句顺序u几个不推荐与推荐方法u实体视图与查询重写u新SQL语句使用建议u编码人员与表结构的理解2022年5月16日Oracle SQL语句优化8/25不推荐语句-不规范用法u不用“*”来代替所有列名 nSELECT语句中可以用* 来列出该表的所有的列名 nOracle系统会通过查询数据字典来将*”转换成该表的所有列名 (动态问题 )u用TRUNCATE代替DELETE n全表删除可直接用TRUNCATEn在P

6、L/SQL可采用动态实现2022年5月16日Oracle SQL语句优化9/25不推荐语句-动态语句u关于动态语句：关于动态语句： nOracle 在PL/SQL中可用动态； nOracle系统动态在必须用才用，动态就是性能问题；n不建议在4GL等工具大量使用 SQL语句u一般不能用静态才使用动态：一般不能用静态才使用动态： nDROP TABLESPACEnTRUNCATE TABLEn2022年5月16日Oracle SQL语句优化10/25不推荐语句-COMMITu完整性下多用完整性下多用COMMIT语句语句: n确保数据完整前提下,释放程序语句获得的锁nredo log buffer

7、中的空间nORACLE为管理上述3种资源中的内部花费2022年5月16日Oracle SQL语句优化11/25不推荐语句-少用LIKE u字符串的匹配：n要少用 like %.%(不以 % 开头 ) ；n当对于 like %. 的 (不以 % 结尾)；2022年5月16日Oracle SQL语句优化12/25不推荐语句-少用ORDER BY u如要对结果进行排序，可考虑几种情况 ：n必须排序吗？；n结果集多大；n导致临时表空间的使用；n尽可能在内存完成排序nALTER SESSION SET SORT_AREA_SIZE=2048000;2022年5月16日Oracle SQL语句优化13/2

8、5不推荐语句-视图嵌套 uOracle视图的嵌套问题：nOracle 允许创建视图时子查询可以是基表，也可是视图；n理论上视图可嵌套视图16层；n当视图可嵌套层数超过3层以上时，性能下降非常严重；2022年5月16日Oracle SQL语句优化14/25不推荐语句-SYSDATE uOracle内置函数SYSDATE产生意些开销 ：nSYSDATE可在循环开始时用，在循序尽量避免，如下面量个例子：2022年5月16日Oracle SQL语句优化15/25不推荐语句- MOD uOracle有些内置函数开销大，MOD是一个 ：n在IF 中使用MOD，就产生不必要的开销n/2022年5月16日Or

9、acle SQL语句优化16/25推荐方法-DECODE函数 uOracle 所有版本都支持DECODE ：nDECODE表示译码，可快速将某个列的值转换成对应的结果；nDECODE比IF THEN高效；n2022年5月16日Oracle SQL语句优化17/25推荐方法-巧用ROWIDuOracle ROWID可立即确定行的位置 ：n下面例子用一般方法对数据行更新：-使用 empid 列进行更新的例子：DECLARECURSOR cur_employee IS SELECT empno,sal FROM EMP ;Lv_new_salary_num NUMBER;BEGINStop_watc

10、h.start_timer;FOR cur_rec IN cur_employee LOOP-确定工资增长Lv_new_salary_num := cur_rec.salary ;UPDATE emp SET salary =lv_new_salary_numWHERE employee_id = cur_rec.employee.id;END LOOP;COMMIT;Stop_watch.stop_timer;END;/2022年5月16日Oracle SQL语句优化18/25推荐方法-巧用ROWIDuOracle ROWID可立即确定行的位置 ：n用ROWID对表的相应行进行更新：-使用

11、empid 列进行更新的例子：DECLARECURSOR cur_employee IS SELECT empno,sal ,ROWID FROM EMP ;Lv_new_salary_num NUMBER;BEGINStop_watch.start_timer;FOR cur_rec IN cur_employee LOOP-确定工资增长Lv_new_salary_num := cur_rec.salary ;UPDATE emp SET salary =lv_new_salary_numWHERE employee_id = cur_rec.ROWID;END LOOP;COMMIT;St

12、op_watch.stop_timer;END;/2022年5月16日Oracle SQL语句优化19/25推荐方法-变量与类型 u变量类型与表的列的类型差异 ：n避免变量类型与表的列的类型不同带来的转换问题；n不要定义了变量但不使用；uOracle新变量类型：n用BINARY_DOUBLE 替代NUMBER类型；n用BINARY_FLOAT 替代NUMBER类型2022年5月16日Oracle SQL语句优化20/25推荐方法-自制临时表 uOracle 要从一个经过多次修改的表与另外进行关联，可能因为关键的表的数据过于分散而效率低下，下面方法可能是一个选择 ：n创建一个表空间；n将关联的创

13、建为一个新的表存储在新表空间里；n用这个临时的表进行关联查询n2022年5月16日Oracle SQL语句优化21/25推荐方法-临时表的使用 u如要将一些中间结果在会话中使用，不要采用永久表，要用临时表 ：n在会话中有效；n数据可自动存入临时表空间；n不影响永久表空间；n2022年5月16日Oracle SQL语句优化22/25推荐方法-尽可能用MINUS uMINUS可替代NOT IN：n有时用MINUS 可比NOT IN 要快；n当子查询的结果较大时，不要用NOT IN；2022年5月16日Oracle SQL语句优化23/25内容提要uFROM子句与WHERE子句顺序u几个不推荐与推荐

14、方法u实体视图与查询重写实体视图与查询重写u新SQL语句使用建议u编码人员与表结构的理解2022年5月16日Oracle SQL语句优化24/25实体视图与查询重写u使用实体视图查询重写：n 为了使用实体视图，优化器要重写查询语句；n QUERY_REWRITE_ENABLED =TRUE ,且使用CBO时 ，当发出对基表的查询，Oracle会自动判断是否能利用这个基表的所有指定了ENABLE QUERY REWRITE语句的实体视图； n 如果可以且根据统计信息判断通过查询实体视图代价更小，则Oracle自动重写查询语句，通过查询实体视图得到正确的结果。 n QUERY REWRITE权限允

15、许用户启用实体视图n DBMS_OLAP包有个选项使用实体视图25/25实体视图与查询重写-例子CREATE MATERIALIZED VIEW sales_summary TABLESPACE sales_ts PARALLEL (DEGREE 4) BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITEAS SELECT s.zip, duct_type, sum(s.amount) FROM sales s, product p WHERE duct_id = duct_id GROUP BY s.zip, p.p

16、roduct_type;26/25实体视图与查询重写-例子u执行查询：u检查执行计划为：SELECT s.zip, duct_type, sum(s.amount) FROM sales s, product pWHERE duct_id = duct_idGROUP BY s.zip, duct_type;OPERATION NAME- -SELECT STATEMENT TABLE ACCESS FULL SALES_SUMMARY2022年5月16日Oracle SQL语句优化27/25内容提要uFROM子句与WHERE子句顺序u几个不推荐与推荐方

17、法u实体视图与查询重写u新新SQL语句使用建议语句使用建议u编码人员与表结构的理解2022年5月16日Oracle SQL语句优化28/259i/10g 新WITH 子句uselect 语句前先用with来查询出相应的数据块：n 9i/10g允许用with 为一个子查询块分配一名字然后在SELECT语句使用该名字。Oracle 优化器将该名字当成内联视图或临时表来看待:2022年5月16日Oracle SQL语句优化29/25用新SQL语句-MERGE uOracle 10g新增加语句 ：nINSERT 插入多表nMERGE语句实现更新与插入n2022年5月16日Oracle SQL语句优化3

18、0/25用新SQL语句-MERGEuMERGE语句的UPDATE和INSERT从句：n 9i 的MERGE语句要求指定Insert和Update从句- 使用merge语句加WHERE从句可实现更新与插入操作：SQLMERGE INTO products p USING newproducts npON ( duct_id = duct_id )WHEN MATCHED THEN UPDATE SET duct_name= duct_name,p.category = np.category WHERE p.category = DVD-WHEN NOT

19、 MATCHED THEN INSERT VALUES (duct_name, duct_name, np.category ) WHERE np.category != BOOKS ; SQLselect * from productsPRODUCT_ID PRODUCT_NAME CATEGORY- - -1501 VIVITAR 35MM ELECTRNCS 1502 OLMPUS CAMERA ELECTRNCS 1600 PLAY GYM TOYS 1601 LAMAZE TOYS 1666 HARRY POTTR TOYS2022年5月16日Oracle SQL语句优化31/25表结构与开发人员 u要理解表与表对应的索引的结构 ：n可用DBMS_METADATA包列出表结构与约束及索引的结构n用DBMS_METADATA.GET_DDL也可列出其它对