SQL编写给大家的一些小建议.doc

ERP開發類文件SQL編寫給大家的一些小建議1、 SQL書寫A、推荐書寫方案：SQL关键字统一采用小写，表名，字段名统一采用大写，统一不加表的所有者，另外，所有的分隔间隔均只能有一个空格字符。B、代碼格式好的代碼格式，有利于自己未來檢查也有利于以後別人修改CREATE OR REPLACE PROCEDURE procedure_name (parameter_name OUT datatype ,parameter_name OUT datatype., ai_returnOUT integer, ai_useerror OUT integer, ai_syserror OUT integer, avc_errtext OUT varchar2) AS /* 作者: xxx 日期: 2007-01-12 描述: 功能描述 参数: 输入输出参数说明 */ 内部变量定义 BEGIN- 大写 sql Statement EXCEPTION WHEN others THEN ai_syserror:= SQLCODE; ai_useerror:= 自定义的错误 avc_errtext := SQLERRM(ai_useerror);或 avc_errtext := *错误！;-业务不符 ai_return := -1; ROLLBACK WORK; return;2、 SQL語法分析與建議使用下面就一些在大家的SQL中經常會看到的操作符說明：IN和NOT IN操作符 强烈不推荐使用從oracle內部執行步驟來講，in 會執行轉換的動作，而not in則不會應用到表的索引推荐：用NOT EXISTS 或（外连接+判断为空）方案代替 操作符不等于操作符是永远不会用到索引的，因此对它的处理只会進行table full scan即全表掃描推荐：用其它相同功能的操作运算代替，如a0 改为 a0 or a0 a 改为 aIS NULL 或IS NOT NULL判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。推荐：用其它相同功能的操作运算代替，如a is not null 改为 a0 或a等 及 2与A=3的效果就有很大的区别了，因为A2时ORACLE会先找出为2的记录索引再进行比较，而A=3时ORACLE则直接找到=3的记录索引LIKELIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE %5400% 这种查询不会引用索引，这个条件会产生全表扫描。而LIKE B5400%则会引用范围索引,性能肯定大大提高UNIONUNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：select * from A1unionselect * from A2这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。推荐：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回select * from A1union allselect * from A2*(檢索所有記錄)SELECT子句中請避免使用*当在SELECT子句中列出所有的列时,使用动态SQL列引用 * 是一个方便的方法.但这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间3、 WHERE后面的条件顺序影响A、WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如Select * from a1 where name = ck% and use_bz=1 (高效)Select * from A1 where use_bz=1 and name = ck% (低效)假設以上两个SQL中name及use_bz两个字段都没有索引，所以执行的时候都是全表扫描，第一条SQL的name = ck%条件在记录集内比率为99%，而use_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行name及use_bz的比较，而在进行第二条SQL的时候0.5%条记录进行name及use_bz的比较，所以可以得出第二条SQL的CPU占用率明显比第一条低。B、SQL的where后面条件的原则：越接近结果的条件优先级最高,顺序为按优先级从高到低,以减轻CPU占用率ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.例如:(低效)SELECT FROM EMP E WHERE SAL 50000 AND JOB = MANAGER AND 25 (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效)SELECT FROM EMP E WHERE 25 50000 AND JOB = MANAGER;4、 選擇最有效率的表順序A、Oracle 解析器按從右到左的順序處理FROM子名中表，因此FROM子句中寫在最後的表(基礎表)將被最先處理，在FORM多表查詢時，必須選擇記錄條數最少的表作為基礎表B、Oracle處理多表原理：使用排序及合并方式連接他們，先掃描第一個表（FORM子句中最後的那個表）并對記錄進行排序，再掃描第二個表（FORM子句後的倒數第二個表），最後將所有從第二個表中檢索出的記錄與第一個表合適的記錄進行合并如： 表A1 1,656,241條記錄 表A2 80條記錄則選擇A2作為基礎表是最好的方式如果用A2作為基礎表， Select count(*) from A1,A2 執行時間約1.5S如果用A1作為基礎表 Select count(*) from A2,A1 執行時間約57sC、如果有3個以上的表連接查詢，就要選取交叉表作為基礎表。交叉表是指被其他表所引用的表如：EMP表中描述了loc表和cat表的交集，則(高效) 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.LOC_N=L.LOC_N (低效) SELECT * FROM EMP E， LOC L， CAT C WHERE E.CAT_NO=C.CAT_NO AND E.LOC_NO=L.LOC_NO AND E.EMP_NO BETWEEN 1000 AND 2000 D、盡量減少對表的查詢操作 (低效) SELECT TAB_NAME FROM TABLES WHERE TAB_NAME=( SELECT TAB_NAME FROM TAB_COL WHERE VER=1) AND DB_VER=(SELECT DB_VER FROM TAB_COLWHERE VER=1) (高效) SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER)= (SELECT TAB_NAME,DB_VER FROM TAB_COL WHERE VER=1)5、 重復記錄的調用,多采用變量形