《常见SQL语句优化讲述》由会员分享，可在线阅读，更多相关《常见SQL语句优化讲述（33页珍藏版）》请在金锄头文库上搜索。
1、Oracle Database 几个常见SQL优化 *Oracle SQL语句优化2/25 内容提要 uFROM子句与WHERE子句顺序 u几个不推荐与推荐方法 u实体视图与查询重写 u新SQL语句使用建议 u编码人员与表结构的理解 *Oracle SQL语句优化3/25 FROM多表的顺序 uORACLE的解析器按照从右到左的顺序 uFROM子句中写在最后的表(基础表 driving table)将被最先处理 ； u选择记录条数最少的表作为基础表 n -表 TAB1 有16,384 数据行 -表 TAB2 有10 个数据行 -方法1(最佳)： -选择TAB2作为基础表： select cou
2、nt(*) from tab1 , tab2 where . . . -方法2(不佳)： -选择TAB2作为基础表 ： select count(*) from tab2,tab1 where . . . *Oracle SQL语句优化4/25 FROM多表的顺序 uFROM子句后三个表的情况： n 例如：EMP表描述了LOCATION表和CATEGORY表的交集。 则EMP的顺序是关键：(这里EMP 是交叉表) 例1(效率高)： SELECT * FROM LOCATION L , CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000
3、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; *Oracle SQL语句优化5/25 WHERE子句的连接顺序 u采用自下而上的顺序解析WHERE子句 ； u表之间连接必须写在其他WHERE条件之前； u那些可以过滤掉最大数量记录的条件必须写在 WHERE子句的末尾。 n 例1(低
4、效)： SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 ( SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); 例2(高效)： SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER; *Oracle SQL语句优化6/25 连接的次序 u如果A表与B表存在多对一或一对一的关系，则 下面的语句有区别： n下面SQL低效： n下面语句高效： Select A.* from A, B where A.CITY = B.CITY Select *
5、from A where A.CITY in (select B.City from B) *Oracle SQL语句优化7/25 内容提要 uFROM子句与WHERE子句顺序 u几个不推荐与推荐方法 u实体视图与查询重写 u新SQL语句使用建议 u编码人员与表结构的理解 *Oracle SQL语句优化8/25 不推荐语句-不规范用法 u不用“*”来代替所有列名 nSELECT语句中可以用* 来列出该表的所有的列名 nOracle系统会通过查询数据字典来将*”转换成该表的 所有列名 (动态问题 ) u用TRUNCATE代替DELETE n全表删除可直接用TRUNCATE n在PL/SQL可采用
6、动态实现 *Oracle SQL语句优化9/25 不推荐语句-动态语句 u关于动态语句： nOracle 在PL/SQL中可用动态； nOracle系统动态在必须用才用，动态就是性能 问题； n不建议在4GL等工具大量使用 SQL语句 u一般不能用静态才使用动态： nDROP TABLESPACE nTRUNCATE TABLE n *Oracle SQL语句优化10/25 不推荐语句-COMMIT u完整性下多用COMMIT语句: n确保数据完整前提下,释放程序语句获得的锁 nredo log buffer 中的空间 nORACLE为管理上述3种资源中的内部花费 *Oracle SQL语句优
7、化11/25 不推荐语句-少用LIKE u字符串的匹配： n要少用 like %.%(不以 % 开头 ) ； n当对于 like %. 的 (不以 % 结尾)； *Oracle SQL语句优化12/25 不推荐语句-少用ORDER BY u如要对结果进行排序，可考虑几种情况 ： n必须排序吗？； n结果集多大； n导致临时表空间的使用； n尽可能在内存完成排序 nALTER SESSION SET SORT_AREA_SIZE=2048000; *Oracle SQL语句优化13/25 不推荐语句-视图嵌套 uOracle视图的嵌套问题： nOracle 允许创建视图时子查询可以是基表，也可是
8、 视图； n理论上视图可嵌套视图16层； n当视图可嵌套层数超过3层以上时，性能下降非常 严重； *Oracle SQL语句优化14/25 不推荐语句-SYSDATE uOracle内置函数SYSDATE产生意些开销 ： nSYSDATE可在循环开始时用，在循序尽量避免， 如下面量个例子： DECLAREDECLARE Lv_current_date DATE;Lv_current_date DATE; BEGINBEGIN Stop_watch.start_timer;Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOPFOR
9、lv_count_num IN 1 . 10000 LOOP Lv_current_date := TRUNC(SYSDATE);Lv_current_date := TRUNC(SYSDATE); END LOOP;END LOOP; Stop_watch.stop_timer;Stop_watch.stop_timer; END;END; / / DECLAREDECLARE Lv_current_date DATE Lv_current_date DATE := TRUNC(SYSDATE):= TRUNC(SYSDATE); ; Lv_final_date DATE;Lv_final_
10、date DATE; BEGINBEGIN Stop_watch.start_timer;Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOPFOR lv_count_num IN 1 . 10000 LOOP Lv_final_dateLv_final_date := := Lv_current_dateLv_current_date END LOOP;END LOOP; Stop_watch.stop_timer;Stop_watch.stop_timer; END;END; / / *Oracle SQL语句优化15/25
11、不推荐语句- MOD uOracle有些内置函数开销大，MOD是一个 ： n在IF 中使用MOD，就产生不必要的开销 nBEGINBEGIN Stop_watch.start_timer;Stop_watch.start_timer; FOR lv_count_num IN 1 . 10000 LOOPFOR lv_count_num IN 1 . 10000 LOOP IF MOD(lv_count_num,1000) = 0 THEN;IF MOD(lv_count_num,1000) = 0 THEN; DBMS_OUTPUT.PUT_LINE(DBMS_OUTPUT.PUT_LINE(
12、 Hit 1000;TotalHit 1000;Total: : |lv_count_num) |lv_count_num) END LOOP;END LOOP; Stop_watch.stop_timer;Stop_watch.stop_timer; END;END; / / DECLAREDECLARE Lv_count_inc_num PLS_INTEGER := 0 ;Lv_count_inc_num PLS_INTEGER := 0 ; BEGINBEGIN Stop_watch.start_timer;Stop_watch.start_timer; FOR lv_count_num
13、 IN 1 . 10000 LOOPFOR lv_count_num IN 1 . 10000 LOOP Lv_count_inc_num := Lv_count_inc_num +1;Lv_count_inc_num := Lv_count_inc_num +1; IF lv_count_num=1000 THEN;IF lv_count_num=1000 THEN; DBMS_OUTPUT.PUT_LINE(DBMS_OUTPUT.PUT_LINE( Hit 1000;TotalHit 1000;Total: : |lv_count_num) |lv_count_num) Lv_count
14、_inc_num := 0;Lv_count_inc_num := 0; END IF;END IF; END LOOP;END LOOP; Stop_watch.stop_timer;Stop_watch.stop_timer; END;END; / / *Oracle SQL语句优化16/25 推荐方法-DECODE函数 uOracle 所有版本都支持DECODE ： nDECODE表示译码，可快速将某个列的值转换成对 应的结果； nDECODE比IF THEN高效； n select sid,serial#,username,select sid,serial#,username, DE
15、CODE(command,DECODE(command, 0,0, NoneNone , , 2,2, InsertInsert , , 3,3, SelectSelect , , 6,6, UpdateUpdate , , 7,7, DeleteDelete , , 8,8, DropDrop , , OtherOther ) cmmand) cmmand from v$session where username is not null;from v$session where username is not null; *Oracle SQL语句优化17/25 推荐方法-巧用ROWID
16、uOracle ROWID可立即确定行的位置 ： n下面例子用一般方法对数据行更新： -使用 empid 列进行更新的例子： DECLARE CURSOR cur_employee IS SELECT 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;
