1、 SQL 编写规范1.书写格式示例代码：存储过程 SQL 文书写格式例selectc.dealerCode,round(sum(c.submitSubletAmountDLR + c.submitPartsAmountDLR + c.submitLaborAmountDLR) / count(*), 2) as avg,decode(null, x, xx, CNY)from (selecta.dealerCode,a.submitSubletAmountDLR,a.submitPartsAmountDLR,a.submitLaborAmountDLRfrom SRV_TWC_F awhere
2、 (to_char(a.ORIGSUBMITTIME, yyyy/mm/dd) = Date Range(start)and to_char(a.ORIGSUBMITTIME, yyyy/mm/dd) 1)union allselectb.dealerCode,b.submitSubletAmountDLR,b.submitPartsAmountDLR,b.submitLaborAmountDLRfrom SRV_TWCHistory_F bwhere (to_char(b.ORIGSUBMITTIME, yyyy/mm/dd) = Date Range(start)and to_char(b
3、.ORIGSUBMITTIME,yyyy/mm/dd) 1) cgroup by c.dealerCodeorder by avg desc;Java source 里的 SQL 字符串书写格式例strSQL = “insert into Snd_FinanceHistory_Tb “+ “(DEALERCODE, “+ “REQUESTSEQUECE, “+ “HANDLETIME, “+ “JOBFLAG, “+ “FRAMENO, “+ “INMONEY, “+ “REMAINMONEY, “+ “DELETEFLAG, “+ “UPDATECOUNT, “+ “CREUSER, “+
4、“CREDATE, “+ “HONORCHECKNO, “+ “SEQ) “+ “values (“ + draftInputDetail.dealerCode + “, “+ “ + draftInputDetail.requestsequece + “, “+ “sysdate, “+ “07, “+ “ + frameNO + “, “+ requestMoney + “, “+ remainMoney + “, “+ “0, “+ “0, “+ “ + draftStruct.employeeCode + “, “+ “sysdate, “+ “ + draftInputDetail.
5、honorCheckNo + “, “+ index + “)“;1). 缩进对于存储过程文件，缩进为 8 个空格对于 Java source 里的 SQL 字符串，不可有缩进，即每一行字符串不可以空格开头2). 换行1.Select/From/Where/Order by/Group by 等子句必须另其一行写2.Select 子句内容如果只有一项，与 Select 同行写3.Select 子句内容如果多于一项，每一项单独占一行，在对应 Select 的基础上向右缩进 8 个空格（Java source 无缩进）4.From 子句内容如果只有一项，与 From 同行写5.From 子句内容如
6、果多于一项，每一项单独占一行，在对应 From 的基础上向右缩进 8 个空格（Java source 无缩进）6.Where 子句的条件如果有多项，每一个条件占一行，以 AND 开头，且无缩进7.(Update)Set 子句内容每一项单独占一行，无缩进8.Insert 子句内容每个表字段单独占一行，无缩进；values 每一项单独占一行，无缩进9.SQL 文中间不允许出现空行10.Java source 里单引号必须跟所属的 SQL 子句处在同一行，连接符（“+“）必须在行首3). 空格1.SQL 内算数运算符、逻辑运算符连接的两个元素之间必须用空格分隔2. 逗号之后必须接一个空格3. 关键字
7、、保留字和左括号之间必须有一个空格2.不等于统一使用“ 是等价的，都代表不等于的意义。为了统一，不等于一律使用“ 12). 增加记录状态字段数据库中的每张表基本都有以下字段：DELETEFLAG、UPDATECOUNT 、CREDATE、CREUSER、UPDATETIME 、UPDATEUSER要注意在对标进行操作时必须考虑以下字段插入一条记录时要置 DELETEFLAG=0, UPDATECOUNT=0, CREDATE=sysdate, CREUSER=登录 User查询一条记录时要考虑 DELETEFLAG，如果有可能对此记录作更新时还要取得 UPDATECOUNT 作同步检查修改一条
8、记录时要置 UPDATETIME=sysdate, UPDATEUSER=登录 User, UPDATECOUNT=(UPDATECOUNT+1) mod 1000,删除一条记录时要置 DELETEFLAG=13). 历史表数据库里部分表还存在相应的历史表，比如 srv_twc_f 和 srv_twchistory_f在查询数据时除了检索所在表之外，还必须检索相应的历史表，对二者的结果做 Union（或 Union All）8.用执行计划分析 SQL 性能EXPLAIN PLAN 是一个很好的分析 SQL 语句的工具，它可以在不执行SQL 的情况下分析语句通过分析，我们就可以知道 ORACLE
9、 是怎样连接表，使用什么方式扫描表（索引扫描或全表扫描） ，以及使用到的索引名称按照从里到外，从上到下的次序解读分析的结果EXPLAIN PLAN 的分析结果是用缩进的格式排列的，最内部的操作将最先被解读，如果两个操作处于同一层中，带有最小操作号的将首先被执行目前许多第三方的工具如 PLSQL Developer 和 TOAD 等都提供了极其方便的 EXPLAIN PLAN 工具PG 需要将自己添加的查询 SQL 文记入 log，然后在 EXPLAIN PLAN 中进行分析，尽量减少全表扫描ORACLE SQL 性能优化系列1.选择最有效率的表名顺序(只在基于规则的优化器中有效)ORACLE
10、的解析器按照从右到左的顺序处理 FROM 子句中的表名，因此FROM 子句中写在最后的表（基础表 driving table）将被最先处理在 FROM 子句中包含多个表的情况下，必须选择记录条数最少的表作为基础表当 ORACLE 处理多个表时，会运用排序及合并的方式连接它们首先，扫描第一个表（FROM 子句中最后的那个表)并对记录进行排序；然后扫描第二个表（FROM 子句中最后第二个表)；最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并例如:表 TAB1 16,384 条记录表 TAB2 5 条记录选择 TAB2 作为基础表 (最好的方法)select count(*) from
11、 tab1,tab2 执行时间 0.96 秒选择 TAB2 作为基础表 (不佳的方法)select count(*) from tab2,tab1 执行时间 26.09 秒如果有 3 个以上的表连接查询，那就需要选择交叉表（intersection table）作为基础表，交叉表是指那个被其他表所引用的表例如:EMP 表描述了 LOCATION 表和 CATEGORY 表的交集SELECT *FROM LOCATION L,CATEGORY C,EMP EWHERE E.EMP_NO BETWEEN 1000 AND 2000AND E.CAT_NO = C.CAT_NOAND E.LOCN
12、= L.LOCN将比下列 SQL 更有效率SELECT *FROM EMP E ,LOCATION L ,CATEGORY CWHERE E.CAT_NO = C.CAT_NOAND E.LOCN = L.LOCNAND E.EMP_NO BETWEEN 1000 AND 20002.WHERE 子句中的连接顺序ORACLE 采用自下而上的顺序解析 WHERE 子句根据这个原理，表之间的连接必须写在其他 WHERE 条件之前，那些可以过滤掉最大数量记录的条件必须写在 WHERE 子句的末尾例如：(低效 ,执行时间 156.3 秒)SELECT *FROM EMP EWHERE SAL 5000
13、0AND JOB = MANAGERAND 25 50000AND JOB = MANAGER;3.SELECT 子句中避免使用 *当你想在 SELECT 子句中列出所有的 COLUMN 时，使用动态 SQL 列引用*是一个方便的方法，不幸的是，这是一个非常低效的方法实际上，ORACLE 在解析的过程中，会将*依次转换成所有的列名这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间4.减少访问数据库的次数当执行每条 SQL 语句时，ORACLE 在内部执行了许多工作：解析 SQL 语句，估算索引的利用率，绑定变量，读数据块等等由此可见，减少访问数据库的次数，就能实际上减少 ORACLE
14、的工作量例如：以下有三种方法可以检索出雇员号等于 0342 或 0291 的职员方法 1 (最低效)SELECT EMP_NAME, SALARY, GRADEFROM EMPWHERE EMP_NO = 342;SELECT EMP_NAME, SALARY, GRADEFROM EMPWHERE EMP_NO = 291;方法 2 (次低效)DECLARECURSOR C1 (E_NO NUMBER) ISSELECT EMP_NAME,SALARY,GRADEFROM EMPWHERE EMP_NO = E_NO;BEGINOPEN C1(342);FETCH C1 INTO ,;OPE
15、N C1(291);FETCH C1 INTO ,;CLOSE C1;END;方法 2 (高效)SELECT A.EMP_NAME, A.SALARY, A.GRADE,B.EMP_NAME, B.SALARY, B.GRADEFROM EMP A, EMP BWHERE A.EMP_NO = 342AND B.EMP_NO = 291;5.使用 DECODE 函数来减少处理时间使用 DECODE 函数可以避免重复扫描相同记录或重复连接相同的表例如：SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0020AND ENAME LIKE SMITH
16、%;SELECT COUNT(*), SUM(SAL)FROM EMPWHERE DEPT_NO = 0030AND ENAME LIKE SMITH%;你可以用 DECODE 函数高效地得到相同结果SELECT COUNT(DECODE(DEPT_NO, 0020, X, NULL) D0020_COUNT,COUNT(DECODE(DEPT_NO, 0030, X, NULL) D0030_COUNT,SUM(DECODE(DEPT_NO, 0020, SAL, NULL) D0020_SAL,SUM(DECODE(DEPT_NO, 0030, SAL, NULL) D0030_SALFR
17、OM EMPWHERE ENAME LIKE SMITH%;X 表示任何一个字段类似的，DECODE 函数也可以运用于 GROUP BY 和 ORDER BY 子句中6.用 Where 子句替换 HAVING 子句避免使用 HAVING 子句， HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作如果能通过 WHERE 子句限制记录的数目，那就能减少这方面的开销例如：低效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONGROUP BY REGIONHAVING REGION REGION != SYDNEYAND REGION
18、 != PERTH高效SELECT REGION, AVG(LOG_SIZE)FROM LOCATIONWHERE REGION REGION != SYDNEYAND REGION != PERTHGROUP BY REGION7.减少对表的查询在含有子查询的 SQL 语句中，要特别注意减少对表的查询例如：低效SELECT TAB_NAMEFROM TABLESWHERE TAB_NAME = (SELECT TAB_NAMEFROM TAB_COLUMNSWHERE VERSION = 604)AND DB_VER = (SELECT DB_VERFROM TAB_COLUMNSWHERE
19、 VERSION = 604)高效SELECT TAB_NAMEFROM TABLESWHERE (TAB_NAME, DB_VER) = (SELECT TAB_NAME, DB_VERFROM TAB_COLUMNSWHERE VERSION = 604)Update 多个 Column 例子：低效UPDATE EMPSET EMP_CAT = (SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE = (SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;高效UPDA
20、TE EMPSET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT = 0020;8.使用表的别名(Alias)当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个Column 上这样可以减少解析的时间并减少那些由 Column 歧义引起的语法错误9.用 EXISTS 替代 IN在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接在这种情况下，使用 EXISTS(或 NOT EXISTS)通常将提高查询的效率低效SELEC
21、T *FROM EMP ( 基础表 )WHERE EMPNO 0AND DEPTNO IN (SELECT DEPTNOFROM DEPTWHERE LOC = MELB)高效SELECT *FROM EMP ( 基础表 )WHERE EMPNO 0AND EXISTS (SELECT XFROM DEPTWHERE DEPT.DEPTNO = EMP.DEPTNOAND LOC = MELB)10.用 NOT EXISTS 替代 NOT IN在子查询中，NOT IN 子句将执行一个内部的排序和合并无论在哪种情况下，NOT IN 都是最低效的，因为它对子查询中的表执行了一个全表遍历为了避免使用
22、 NOT IN，我们可以把它改写成外连接(Outer Joins)或 NOT EXISTS例如：SELECT FROM EMPWHERE DEPT_NO NOT IN (SELECT DEPT_NOFROM DEPTWHERE DEPT_CAT = A);为了提高效率改写为高效SELECT FROM EMP A, DEPT BWHERE A.DEPT_NO = B.DEPT(+)AND B.DEPT_NO IS NULLAND B.DEPT_CAT(+) = A最高效SELECT FROM EMP EWHERE NOT EXISTS (SELECT XFROM DEPT DWHERE D.DE
23、PT_NO = E.DEPT_NOAND DEPT_CAT = A);11.用表连接替换 EXISTS通常来说，采用表连接的方式比 EXISTS 更有效率例如：SELECT ENAMEFROM EMP EWHERE EXISTS (SELECT XFROM DEPTWHERE DEPT_NO = E.DEPT_NOAND DEPT_CAT = A);更高效SELECT ENAMEFROM DEPT D, EMP EWHERE E.DEPT_NO = D.DEPT_NOAND DEPT_CAT = A;12.用 EXISTS 替换 DISTINCT当提交一个包含多表信息（比如部门表和雇员表）的查
24、询时，避免在SELECT 子句中使用 DISTINCT，一般可以考虑用 EXIST 替换例如：低效SELECT DISTINCT DEPT_NO, DEPT_NAMEFROM DEPT D, EMP EWHERE D.DEPT_NO = E.DEPT_NO高效SELECT DEPT_NO, DEPT_NAMEFROM DEPT DWHERE EXISTS (SELECT XFROM EMP EWHERE E.DEPT_NO = D.DEPT_NO);EXISTS 使查询更为迅速，因为 RDBMS 核心模块将在子查询的条件一旦满足后，立刻返回结果13.用索引提高效率索引是表的一个概念部分，用来提
25、高检索数据的效率。实际上，ORACLE使用了一个复杂的自平衡 B-tree 结构通常，通过索引查询数据比全表扫描要快。当 ORACLE 找出执行查询和Update 语句的最佳路径时， ORACLE 优化器将使用索引同样，在联结多个表时使用索引也可以提高效率。另一个使用索引的好处是，它提供了主键（primary key）的唯一性验证除了那些 LONG 或 LONG RAW 数据类型，你可以索引几乎所有的列通常在大型表中使用索引特别有效，当然，在扫描小表时，使用索引同样能提高效率虽然使用索引能得到查询效率的提高，但是我们也必须注意到它的代价索引需要空间来存储，也需要定期维护，每当有记录在表中增减或
26、索引列被修改时，索引本身也会被修改这意味着每条记录的 INSERT、DELETE 、UPDATE 将为此多付出 4、5 次的磁盘 I/O因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢ORACLE 对索引有两种访问模式：1). 索引唯一扫描（INDEX UNIQUE SCAN）大多数情况下, 优化器通过 WHERE 子句访问 INDEX例如：表 LODGING 有两个索引：建立在 LODGING 列上的唯一性索引LODGING_PK 和建立在 MANAGER 列上的非唯一性索引LODGING$MANAGERSELECT *FROM LODGINGWHERE LODGI
27、NG = ROSE HILL;在内部，上述 SQL 将被分成两步执行：首先，LODGING_PK 索引将通过索引唯一扫描的方式被访问，获得相对应的 ROWID；然后通过 ROWID 访问表的方式执行下一步检索如果被检索返回的列包括在 INDEX 列中，ORACLE 将不执行第二步的处理（通过 ROWID 访问表）因为检索数据保存在索引中，单单访问索引就可以完全满足查询结果2). 索引范围查询(INDEX RANGE SCAN)适用于两种情况:1. 基于唯一性索引的一个范围的检索2. 基于非唯一性索引的检索例 1SELECT LODGINGFROM LODGINGWHERE LODGING LI
28、KE M%;WHERE 子句条件包括一系列值，ORACLE 将通过索引范围查询的方式查询 LODGING_PK由于索引范围查询将返回一组值，它的效率就要比索引唯一扫描低一些例 2SELECT LODGINGFROM LODGINGWHERE MANAGER = BILL GATES;这个 SQL 的执行分两步，LODGING$MANAGER 的索引范围查询（得到所有符合条件记录的 ROWID） ，通过 ROWID 访问表得到 LODGING 列的值由于 LODGING$MANAGER 是一个非唯一性的索引，数据库不能对它执行索引唯一扫描WHERE 子句中，如果索引列所对应的值的第一个字符由通配
29、符（WILDCARD）开始，索引将不被采用SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE HANMAN;在这种情况下，ORACLE 将使用全表扫描14.避免在索引列上使用计算WHERE 子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描例如：低效SELECT FROM DEPTWHERE SAL * 12 25000;高效SELECT FROM DEPTWHERE SAL 25000/12;请务必注意，检索中不要对索引列进行处理，如：TRIM，TO_DATE，类型转换等操作，破坏索引，使用全表扫描，影响 SQL 执行效率15.避免在索引
30、列上使用 IS NULL 和 IS NOT NULL避免在索引中使用任何可以为空的列，ORACLE 将无法使用该索引对于单列索引，如果列包含空值，索引中将不存在此记录；对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中如果唯一性索引建立在表的 A 列和 B 列上，并且表中存在一条记录的 A,B值为(123,null)，ORACLE 将不接受下一条具有相同 A,B 值（ 123,null）的记录插入如果所有的索引列都为空，ORACLE 将认为整个键值为空，而空不可能等于空，因此你可以插入 1000 条具有相同键值的记录，当然它们都是空！因为空值不存
31、在于索引列中，所以 WHERE 子句中对索引列进行空值比较将使 ORACLE 停用该索引低效（索引失效）SELECT FROM DEPARTMENTWHERE DEPT_CODE IS NOT NULL16.使用 UNION-ALL 和 UNION当 SQL 语句需要 UNION 两个查询结果集合时，这两个结果集合会以UNION-ALL 的方式被合并，然后在输出最终结果前进行排序如果用 UNION ALL 替代 UNION，这样排序就不是必要了，效率就会因此得到提高需要注意的是，UNION ALL 将重复输出两个结果集合中相同记录，因此还是要从业务需求分析使用 UNION ALL 的可行性关于
32、索引下列经验请参考:1). 如果检索数据量超过 30%的表中记录数，使用索引将没有显著的效率提高2). 在特定情况下，使用索引也许会比全表扫描慢，但这是同一个数量级上的差距；而通常情况下，使用索引比全表扫描要快几倍乃至几千倍！其他具体内容请参考ORACLE SQL 性能优化系列 17.使用 PrepareStatement在同一个方法中，当循环使用 SQL 文时，为了提高性能，请使用 PreparedStatement。注意，仅限使用于少数的模块。方法如下：? PreparedStatement stmt= conn.prepareStatement(“select a from TABLE_A where b=? c=?“);for(? )? stmt.setInt(1, varB);? stmt.setString(2, varC);? ResultSet rst = stmt.executeQuery();
