SQL规范指导书.doc

1、Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal use only, pr opr ietar y and confidential. 1 SQL 规范指导书 目录 1 环境 . 2 2 高效的 SQL 语句 . 2 2.1 SQL优化方法 .2 2.1.1 驱动表 . 2 2.1.2 Where 语句顺序的效率 . 3 2.2 SQL命令的使用秘诀 .5 2.2.1 DECODE 的使用 . 5 2.2.2 表的别名的使用 . 5 2.2.3 去掉重复行 .

2、 6 2.2.4 表的行计数 . 6 2.2.5 用 WHERE 语句替换 HAVING 语句的使用 . 6 2.2.6 使用表连接替代 EXISTS 使用 . 6 2.2.7 使用 EXISTS 替代表连接 . 6 2.2.8 使用 EXISTS 代替 DISTINCT语句 . 7 2.2.9 使用 NOT EXISTS 代替 NOT IN 语句 . 7 2.2.10 使用 union all 代替 union 语句 . 7 2.2.11 使用 Union 和 IN代替 OR语句 . 7 3 高效索引 . 8 3.1 索引的使用 .8 3.2 增加索引 .9 4 解析表的使用方法 . 9 4

3、.1 SQLTRACE .9 4.1.1 参数表的生成 . 9 4.1.2 AUTOTRACE 的用法 . 9 4.1.3 AUTOTRACE 功能的使用 . 9 4.2 TKPROF .10 4.2.1 初始化 . 10 4.2.2 SQL Trace 开始 . 10 4.2.3 实例 . 10 4.2.4 SQL Trace 结束 . 10 4.2.5 内容变换 . 10 4.2.6 内容确认 . 10 Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal u

4、se only, pr opr ietar y and confidential. 2 1 环境 Oracle 8.1.7 2 高效的 sql语句 2.1 Sql 优化方法 RBO(rule-based optimizer) CBO 2.1.1 驱动表 1） 2张行数不一致的表连接 表 TAB1行数： 16,384行 表 TAB2行数： 1行 SELECT COUNT(*) FROM TAB2, TAB1; SELECT COUNT(*) FROM TAB1, TAB2; 2） 3张表连接 SELECT * FROM EMP E, LOC L, CAT C WHERE E.emp_no BET

5、WEEN 1000 AND 2000 AND E.cat_no = C.cat_no AND E.locn = L.locn; SELECT * FROM LOC L, CAT C, EMP E WHERE E.emp_no BETWEEN 1000 AND 2000 AND E.cat_no = C.cat_no AND E.locn = L.locn; Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal use only, pr opr ietar y an

6、d confidential. 3 2.1.2 Where语句 顺序 的效率 1) 使用索引引起的 where语句效率 使用 AND语句时行数多的放在前面 SELECT * FROM emp E WHERE emp_sal 50000 AND emp_type = MANAGER AND 25 50000 AND emp_type = MANAGER; 使用 OR语句时，行数多的放在后面 SELECT * FROM emp E WHERE 25 50000 AND emp_type = MANAGER); SELECT * FROM emp E WHERE (emp_sal 50000 AND

7、 emp_type = MANAGER) OR 25 0; （ 3） TRUNC SELECT acc_name, trans_date, amount FROM transaction WHERE TRUNC(trans_date) = TRUNC(SYSDATE); SELECT acc_name, trans_date, amount FROM transaction WHERE trans_date BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + .99999; （ 4） | SELECT acc_name, trans_date, amount

8、 FROM transaction Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal use only, pr opr ietar y and confidential. 5 WHERE acc_name | acc_type = AMEXA; SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name = AMEX AND acc_type = A; （ 5）运算 SELECT ac

9、c_name, trans_date, amount FROM transaction WHERE amount + 3000 ( SELECT MIN(X.rowid) FROM emp X WHERE X.emp_no = E.emp_no ); 2.2.4 表的行计数 SELECT COUNT(有索引的列 ) FROM TRANS; SELECT COUNT(*) FROM TRANS; SELECT COUNT(1) FROM TRANS; 2.2.5 用 WHERE语句替换 HAVING语句的使用 SELECT region, AVG(loc_size) FROM location

10、GROUP BY region HAVING region != SYDNEY AND region != PERTH; SELECT region, AVG(loc_size) FROM location WHERE region != SYDNEY AND region != PERTH; GROUP BY region 2.2.6 使用表连接替代 EXISTS使用 SELECT emp_name FROM emp WHERE (emp_cat, sal_range) = (SELECT MAX(category), MAX(sal_range) FROM emp_categories)

11、AND emp_dept = 0020; SELECT emp_name FROM emp WHERE emp_cat = (SELECT MAX(category) FROM emp_categories) AND sal_range = (SELECT MAX(sal_range) FROM emp_categories) AND emp_dept = 0020; 2.2.7 使用 EXISTS替代表连接 SELECT . FROM dept D, emp E WHERE E.dept_no = D.dept_no AND E.emp_type = MANAGER AND D.dept_c

12、at = A; SELECT . FROM emp E WHERE EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_no AND dept_cat = A) Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal use only, pr opr ietar y and confidential. 7 AND E.emp_type = MANAGER; SELECT . FROM dept D, emp E WH

13、ERE E.dept_no = D.dept_no AND (E.emp_type = MANAGER OR D.dept_cat = A); SELECT . FROM emp E WHERE E.emp_type = MANAGER; OR EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_no AND dept_cat = A); 2.2.8 使用 EXISTS代替 DISTINCT语句 SELECT DISTINCT dept_no, dept_name FROM dept D, emp E WHERE D.dept_no = E.d

14、ept_no; SELECT dept_no, dept_name FROM dept D WHERE EXISTS ( SELECT X FROM emp E WHERE E.dept_no = D.dept_no); 2.2.9 使用 NOT EXISTS代替 NOT IN语句 SELECT . FROM emp WHERE dept_no NOT IN ( SELECT dept_no FROM dept WHERE dept_cat = A); SELECT . FROM emp E WHERE NOT EXISTS ( SELECT X FROM dept WHERE dept_no

15、 = E.dept_no AND dept_cat = A); 2.2.10 使用 union all 代替 union 语句 Union - 进行排序 Union All - 不排序 2.2.11 使用 Union和 IN代替 OR语句 Loc_no, region上有索引 SELECT . Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal use only, pr opr ietar y and confidential. 8 FROM location

16、WHERE loc_id = 10 OR region = MELBOURNE; SELECT . FROM location WHERE loc_id = 10 UNION ALL SELECT . FROM location WHERE region = MELBOURNE; SELECT . FROM location WHERE loc_id = 10 OR loc_id = 20 OR loc_id = 30; SELECT . FROM location WHERE loc_id IN (10, 20, 30); 3 高效索引 3.1 索引的使用 使用索引时，要考虑以下因素： 1）

17、 索引列的计算 2） 索引列的增加 3） 索引列不要用 NOT 4） 索引中空值的使用 IS NULL, IS NOT NULL 5） 索引列的数据类型的变换 EMP_TYPE为 varchar2类型 ，下列语句使用索引 SELECT . FROM emp WHERE emp_type = 123; SELECT . FROM emp WHERE TO_NUMBER(emp_type) = 123; SELECT . FROM emp WHERE emp_type = 123; Copyr ight Suzsoft Company Limited. All Rights Reser ved.

18、This infor mation is for inter nal use only, pr opr ietar y and confidential. 9 3.2 增加索引 增加索引要考虑多种 因素，要考虑对更新、插入的影响等。 1） 频繁查询不超过大表 1%15%的场合。 2） 列的不同值 3） 列 4） 列 5） 同表 4 解析表的使用方法 4.1 SQLTRACE 4.1.1 参数表的生成 $sqlplus s / SQL$ORACLE_HOME/rdbms/admin/utlxplan.sql SQL commit SQL exit 4.1.2 AUTOTRACE的用法 afe57

19、gc2/users/home/ino 29 sqlplus sys/* SQL $ORACLE_HOME/sqlplus/admin/plustrce.sql SQL drop role plustrace; ： SQL grant plustrace to ops$ino; SQL exit 4.1.3 AUTOTRACE功能的使用 afe57gc2/oracle/product/8.1.7 37 sqlplus / SQL set autotrace on SQL select * from wcattori; 执行计划 - 0 SELECT STATEMENT Optimizer=CHO

20、OSE 1 0 TABLE ACCESS (FULL) OF WCATTORI 統計 - 0 recursive calls 12 db block gets 4 consistent gets 0 physical reads 0 redo size 4779 bytes sent via SQL*Net to client 314 bytes received via SQL*Net from client Copyr ight Suzsoft Company Limited. All Rights Reser ved. This infor mation is for inter nal

21、 use only, pr opr ietar y and confidential. 10 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL 4.2 TKPROF 4.2.1 初始化 afe57gc2/oracle/product/8.1.7 40 sqlplus / SQL ALTER SESSION SET TIMED_STATISTICS = true; SQL ALTER SESSION SET MAX_DUMP_FILE_SIZE = 500; 4.2.2

22、 SQL Trace开始 SQL ALTER SESSION SET SQL_TRACE = true; 4.2.3 实例 SQL SELECT * FROM WCATTORI; 4.2.4 SQL Trace结束 SQL ALTER SESSION SET SQL_TRACE = false; SQL exit 4.2.5 内容变换 afe57gc2/users/home/ino 44 su Password: # chmod 777 /oracle/product/8.1.7/rdbms/log/ora_14186_wca1.trc # exit afe57gc2/users/home/i

23、no 46 tkprof $ORACLE_HOME/rdbms/log/ora_14186_wca1.trc output.txt TKPROF: Release 8.1.7.2.0 - Production on 木 Apr 18 15:55:17 2002 (c) Copyright 2000 Oracle Corporation. All rights reserved. 備考： 出力先 SQL column name format A20 SQL column value format A30 SQL select name, value from v$parameter where name like %dump_dest; NAME VALUE - - background_dump_dest ?/rdbms/log user_dump_dest ?/rdbms/log core_dump_dest ?/dbs SQL 4.2.6 内容确认 afe57gc2/users/home/ino 47 cat output.txt (一部抜粋 )