SQL开发规范

当前保险项目的SQL开发规范.

>>SQL开发的一般流程<<
表结构->索引->历史数据->目标->SQL书写思路->开发->调试
在进行程序开发前，不要急于马上编码,要先研究表结构，特别要研究表内历史数据和表内数据的填写方式，在动手之前还必须事先研究表的索引，如索引不足应先加索引再开发，这样可减少所开发程序的错误率和运行性能问题，提高其适应性。

>>常用数据库工具及限制使用技术<<
数据库相关工具:
为需要写SQL的开发人员提供方便的、图形化的客户端软件，可以用来执行SQL，看到表结构、索引等等，如TOAD, PL/SQL Develop软件。
限制使用:
这里说的限制使用是指如果能有其他的技术途径，就不要使用如下的Oracle技术，包括：DBLink， Trigger，用Java编写的存储过程等内容；

>>禁止使用的SQL语句<<
1．严格禁止跨物理数据库关联查询，无论表内记录数多少。
如：查统计系统业务员工号以及其对应的统计机构代码

	select a.empno, b.deptno 
		from remotedb@onhdrb:empno a , statistics@onhdrb:tjywbm b 
		where a.deptno=b.ywbm;

2．严格禁止3个表(含3个表)以上的关联查询，无论表内记录数多少。

select  	processtype, ds_process.ds_process_id, ds_process.createperson,
            ds_process.startdatetime, dsb_apply.dsb_apply_id, ds_activity.executeorg,
    	ds_activity.step
from ds_activity, ds_process, dsb_apply 
where   	ds_process.ds_process_id=dsb_apply.ds_process_id and
    	ds_process.processtype=3 and ds_process.nextactivitytype=2 and
    	ds_process.createperson=3394 and
    	ds_activity.ds_activity_id=ds_process.ds_activity_id order by
    	dsb_apply.dsb_apply_id desc

以上语句严格禁止,只要有一表超过100万记录，一般10分钟内查询出的可能性不大。性能与表间书写次序有很大关系。
3．严格禁止两表均超过5000条以上记录的两个表的关联查询。
两表均超过5000条以上记录的关联表查询要填定<<关联SQL语句编写审批表>>，经审批同意才能按审批同意的语句使用（关联字段也需建立在索引基础上）。
性能分析表明，选择一个好的表联接顺序是非常重要的：
当在WHERE子句中有多个表联接时，WHERE子句中排在最后的表应当是返回行数可能最少的表，有过滤条件的子句应放在WHERE子句中的最后。
4．关于事务提交的处理方法
提交事务原则上应在后台程序进行，严格禁止C/S结构、B/S结构类系统在前台处理程序中提交离散性、等待性事务。由于长事务容易造成数据库死锁，所以事务开始与事务结束之间，应集中编写,一次性提交，且禁止有select语句存在。(但可以有开发工具本身的不引起数据库操作的函数和语句，也可有PB自身的自动提交功能)。

>>不建议使用的SQL语句<<
1．关于多用户并发抢占唯一流水号的处理多用户并发抢号机制建议采用“共享-抢占-重试”的方式进行，不建议采用“抢点-锁定-解锁”的方式进行。

2. 关于统计程序“抽取”语句与“推送”语句的处理。
原则上不建议采用向源表采用正向“抽取“数据的方式，而应采取通过一次扫描源表向多个目的表”推送“填写数据的方式来开发统计、汇总类程序，可有效提高运行效率

3. 大量的排序操作影响系统性能，所以尽量减少order by,group by,distinct等排序操作；如必须使用排序操作，排序应建立在有索引的列上.同时不建议使用关联查询的多字段排序.

4. 不建议使用SELECT *：SELECT语句中应写出要选择的全部列名，增强语句可读性，避免不必要的选择；SELECT * 增加了对所有字段的依赖，当表增加了字段后，有可能发生错误；此外还可能增加了数据的流量，查询了一些实际不需要的字段；

5. SQL语句中除字符串中必须大写的内容外，全部小写，包括关键字，因为大小写结合需要 输入大写字母速度慢。（相同的SQL语句是在SQL缓冲区中读取，SQL分析器不用对SQL语句重新分析产生执行计划，系统响应时间大大减少）。

>>索引的使用<<
1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';
2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
3. 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了
正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 这时函数索引起作用了
4. 以下使用会使索引失效，应避免使用；
a. 使用 <> 、not in 、not exist、!=
b. like "%_" 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。
d. 字符型字段为数字时在where条件里不添加引号.
e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
5. 不要将空的变量值直接与比较运算符（符号）比较。
如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。
6. 不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。
7. 将索引所在表空间和数据所在表空间分别设于不同的磁盘chunk上，有助于提高索引查询的效率。
8. Oracle默认使用的基于代价的SQL优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数据库查询时不使用索引或使用错误的索引。
一般来说，Oracle的自动任务里面会包含更新统计信息的语句，但如果表数据发生了比较大的变化（超过20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新统计信息比较耗费系统资源，建议在系统空闲时执行。
9. Oracle在进行一次查询时，一般对一个表只会使用一个索引.
因此，有时候过多的索引可能导致Oracle使用错误的索引，降低查询效率。例如某表有索引1（Policyno）和索引2（classcode），如果查询条件为 policyno = ‘xx’ and classcode = ‘xx’，则系统有可能会使用索引2，相较于使用索引1，查询效率明显降低。
10. 优先且尽可能使用分区索引。