SQL规范指导书.doc

SQL 规范指导书 目录目录 1 1环境环境 .4 2 2高效的高效的 SQL 语句语句.4 2.1SQL优化方法.4 2.1.1驱动表 4 2.1.2Where 语句顺序的效率 5 2.2SQL 命令的使用秘诀7 2.2.1DECODE 的使用7 2.2.2表的别名的使用8 2.2.3去掉重复行.8 2.2.4表的行计数.8 2.2.5用 WHERE 语句替换 HAVING 语句的使用 8 2.2.6使用表连接替代 EXISTS 使用8 2.2.7使用 EXISTS 替代表连接 .9 2.2.8使用 EXISTS 代替 DISTINCT 语句.9 2.2.9使用 NOT EXISTS 代替 NOT IN 语句 9 2.2.10使用 union all 代替 union 语句10 2.2.11使用 Union 和 IN 代替 OR 语句10 3 3高效索引高效索引 .10 3.1索引的使用.10 3.2增加索引.11 4 4解析表的使用方法解析表的使用方法 .11 4.1SQLTRACE.11 4.1.1参数表的生成11 4.1.2AUTOTRACE 的用法 .11 4.1.3AUTOTRACE 功能的使用12 4.2TKPROF 12 4.2.1初始化 12 4.2.2SQL Trace 开始12 4.2.3实例12 4.2.4SQL Trace 结束12 4.2.5内容变换.12 4.2.6内容确认.13 1 1 环环境境 Oracle 8.1.7 2 2 高高效效的的 sql 语语句句 2.1Sql优优化化方方法法 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_noBETWEEN 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_noBETWEEN 1000 AND 2000 AND E.cat_no= C.cat_no AND E.locn= L.locn; 2.1.2Where语句顺序的效率 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 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 FROM transaction WHERE acc_name | acc_type = AMEXA; SELECT acc_name, trans_date, amount FROM transaction WHERE acc_name = AMEX AND acc_type = A; （5）运算 SELECT acc_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 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) 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_cat = A; SELECT . FROM emp E WHERE EXISTS ( SELECT X FROM dept WHERE dept_no = E.dept_no AND dept_cat = A) AND E.emp_type = MANAGER; SELECT . FROM dept D, emp E WHERE 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.dept_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 = 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 . FROM location 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 高高效效索索引引 3.1索索引引的的使使用用 使用索引时，要考虑以下因素： 1）索引列的计算 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; 3.2增增加加索索引引 增加索引要考虑多种因素，要考虑对更新、插入的影响等。 1）频繁查询不超过大表 1%15%的场合。 2）列的不同值 3）列 4）列 5）同表 4 4 解解析析表表的的使使用用方方法法 4.1SQLTRACE 4.1.1参数表的生成 $sqlplus s / SQL$ORACLE_HOME/rdbms/admin/utlxplan.sql SQL commit SQL exit 4.1.2AUTOTRACE的用法 afe57gc2/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.3AUTOTRACE功能的使用 afe57gc2/oracle/product/8.1.7 37 sqlplus / SQL set autotrace on SQL select * from wcattori; 执行计划 - 0 SELECT STATEMENT Optimizer=CHOOSE 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 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed SQL 4.2TKPROF 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.2SQL Trace开始 SQL ALTER SESSION SET SQL_TRACE = true; 4.2.3实例 SQL SELECT * FROM WCATTORI; 4.2.4SQL 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/ino 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 colu