[b]转载自其它网站[/b]
数据库开发规范
命名规范
表：同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义，长度以不超过15个字符为宜(避免超过20)
字段名：表达其实际含义的英文单词或简写，不要使用类似VALUE1这种无意义的字段名
索引：表名称_字段名_IND
约束：表名称_PK（或者PK_表名称），表名称_UK（或者UK_表名称）,务必保持一致
触发器：表名称_A(After)B(Before)I(Insert)U(Update)D(Delete)_TRG，如果是同步触发器以 sync作为前缀：sync_表名_trg，函数过程：采用动词+名词的形式表达其含义
常用数据类型
NUMBER(p,s):固定精度数字类型。
NUMBER:不固定精度数字类型，当不确定数字的精度时使用，PK通常使用此类型。
DATE:当仅需要精确到秒时，选择DATE而不是TIMESTAMP类型。
TIMESTAMP:扩展日期类型，不建议使用。
VARCHAR2:变长字符串，最长4000个字节，
CHAR:定长字符串，除非是CHAR(1),否则不要使用。
CLOB:当超过4000字节时使用，但是要求这个字段必须单独创建到一张表中，然后有PK与主表关联。此类型应该尽量控制使用。
表：
a)如果有必要，总是包含两个日期字段:gmt_create(创建日期),gmt_modified(修改日期)。
b)尽可能使用简单数据类型，不要使用类似数组或者嵌套表这种复杂类型。
c)必须要有主键，且尽量不要使用存在实际意义的字段做主键。
d)需要join的字段，数据类型保持绝对一致。
e)允许适当冗余，以提高性能，但是必须考虑数据同步的情况。
f)当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表（主要是为了性能考虑）。
PK约束：
a)PK最好是无意义的，由Sequence产生的ID字段，不建议使用组合PK。如果除了PK以外，还存在其他的唯一约束，可以创建UK。
b)不要试图通过创建唯一索引来达到唯一约束的效果，不管是pk还是unique约束（实际上我们严格控制建立unique index，因为一旦建立，这个索引的的唯一约束性将永远无法在不影响索引的情况下被摘除），必须显式创建普通索引和约束，而不是仅仅创建一个NOT NULL+UNIQUE INDEX的组合（即先create一个以约束名命名的index（普通索引，而不是带约束的索引），然后创建一个约束，并 using index ...,这样做的好处就是当需要摘掉约束的时候可以做到不影响索引）。
c)当摘除约束的时候，为了确保不影响到index，最好加上keep index参数。
d)PK字段不能被更新。
e)外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制。
f)当万不得已必须使用外健的话，必须在外健列创建INDEX。
索引
a)Bitmap索引通常不适合我们的环境。
b)索引根据实际SQL，由DBA创建。
c)索引不是万能的，很多情况下索引并不能解决问题。
d)严格控制创建带约束的索引，所有的约束效果都通过显示创建约束然后再using 一个已经创建好的普通索引来实现
sql编写规范
1.避免在where子句中对字段施加函数，这样将导致索引失效，比如：
select * from member where to_char(gmt_create,'yyyymmdd')='20070101';
原因：在建立index的时候是根据字段来建立的，也就是说oracle在inidex的时候是索引的字段的值，如果提供给oracle的是一个需要经过函数处理的比较，oracle就没办法通过索引中的索引键值来进行相应的比较，所以就不会走到索引上
2.避免在SQL中发生隐式类型转换
如：
select * from table where id='123'; -- # 这里ID是NUMBER型,会造成oracle将id先转换成varchar类型再比较，造成索引失效
select * from table where gmt_create = to_char('2000-01-01','yyyy-mm-dd');
-- # 会造成oracle先将gmt_create转换成varchar类型，造成无法走index，这里还有一个需要注意的，就是当和oracle中date 类型的字段比较的时候，最好先通过to_date('2000-01-01','yyyy-mm-dd')函数将传入的值转换一下，即使传入的是java 的date类型也这样做，因为这样也很可能让oracle出现隐式转换
3.全模糊查询无法使用INDEX，应当尽可能避免
select * from table where name like '%jacky%';
4.如果使用Oracle数据库，使用Oracle的外连接，而不是标准的外连接语法
正确：select * from table1 a,table2 b where a.id=b.id ;
错误：select * from table1 a left join on test2 b a.id=b.id;
5.分页语句必须使用三层嵌套的写法（不是10g的数据库不要使用分析函数）
select * from (
select rownum rn,a,* from (
select * from table where 条件 order by 条件
) a where rownum<=100
) where rn>80;
6.使用in的绑定问题
a)使用ibatis的in list绑定，但是必须注意不能超过1000项。
b)使用str2numlist_order,str2varlist_order函数，将一个字符串转换为内存表。
7.判断NULL应该使用IS NULL或者IS NOT NULL,而不是=null或者null
8.sql中的/+ ordered use_nl(member offer)/是hint，用来确定SQL的执行计划，请在 DBA确认后使用。
9.在使用ibatis时候，sqlmap文件编写需要严格遵守以下规范：Ibatis中的SqlMap编写规范
Ibatis中的SqlMap编写规范
select语句中*号的问题
1。表连接时，绝不允许写select * ，否则按照编码错误Bug处理。
2。单表查询，一般情况下可以使用select * ，但以下几种情况禁用：
a、表中包含lob字段（BLOB、CLOB、LONG、LONG RAW等）。
b、表中包含长度较大的字段，如varchar2(1000)以上的字段，但该SQL实际上并不需要取出该字段的值。
c、字段数量很多，但实际要用的字段很少，比如表有50个字段，而你实际只用5个，并且该sql目前没有被重用。
d、DBA要求优化调整的。
严格要求使用正确类型的变量，杜绝oracle做隐式类型转换的情况
1、推荐在sqlmap的变量中指定变量的数据类型，如：select * from iw_user where iw_user_id = #userid:VARCHAR#
2、其中，对于时间类型的字段，必须使用TO_DATE进行赋值（当前时间可直接用sysdate表示），不允许下列这些错误用法：
错误的写法（使用date类型的变量）：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= #dateBegin:date# and trans_dt < #dateEnd:date#
错误的写法（将to_date函数和数字进行算术运算）：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
and trans_dt < to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss') + 1
正确的写法：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
and trans_dt < to_date(#dateEnd:varchar#, 'yyyy-mm-dd hh24:mi:ss') /*或 trans_dt < sysdate */
3、对于变量数据类型错误导致SQL严重性能问题的，按严重的编码错误Bug处理！
绑定变量和替代变量
在Ibatis中：
绑定变量用 #变量名# 表示
替代变量用 $变量名$ 表示
除了特别情况（经由DBA和架构组安全工程师审批）外，都只能使用绑定变量而不得使用替代变量，对于原来order by传入数组的问题，安全工程师已经要求全部更改掉，架构组会提供相应解决方案。
注意几点：
1) 通常，应使用绑定变量，尤其是具体取值变化范围较大的变量，如iw_user_id = #userid#。
2) 取值范围很小（比如枚举字段），并且通常取值会比较固定，在DBA预先同意的情况下使用替代变量，或者干脆使用常量。
3) 当一个绑定变量在实际使用中实际取值总是为某一固定常量时，应当直接使用常量而不是变量，关于这点，可在具体使用时咨询DBA
4) 在order by子句中，通常使用替代变量而不是绑定变量。
5) IN子句，使用"iterate + 数组类型变量"的方式实现绑定变量，例如：
<isNotEmpty prepend="and" property="userIds">
<iterate property="userIds" open="t.creator in (" close=")" conjunction=",">
#userIds[]#
</iterate>
</isNotEmpty>
将生成 t.creator in (:1, :2, :3, :4, :5 ...) 的语句
在字段上加函数的问题
1) 通常，不允许在字段上添加函数或者表达式，如：
错误的写法：
select * from iw_account_log where to_char ( trans_dt, 'yyyy-mm-dd') = '2007-04-04';
select qty from product where p_id + 12 = 168;
正确的写法：
select * from iw_account_log
where trans_dt >= to_date ( '2007-04-04', 'yyyy-mm-dd') and trans_dt < to_date ( '2007-04-05', 'yyyy-mm-dd');
select qty from product where p_id = 168 - 12;
2) 如果是业务要求的除外，但需要在编写时咨询DBA
3) 特别注意，当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数，如
错误的写法（a.id是number类型，而b.operator_number是char类型）：
select count from adm_user a, adm_action_log b where a.id = b.operator_number and a.username = '小钗';
正确的写法：
select count from adm_user a, adm_action_log b where to_char(a.id) = b.operator_number and a.username = '小钗';
select count from adm_user a, adm_action_log b where a.id = to_number(b.operator_number) and a.username = '小钗';
上面两种写法哪个正确？遇到这种情况时必须咨询DBA！
表连接
1) 不使用ANSI连接，如inner join、left join、right join、full outer join，而使用（+）来表示外连接
错误的写法：
select a.*, b.goods_title from iw_account_log a left join beyond_trade_base b on a.TRANS_OUT_ORDER_NO = b.trade_no
where a.trans_code = '6003' and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)
正确的写法：
select a.*, b.goods_title from iw_account_log a, beyond_trade_base b
where a.TRANS_OUT_ORDER_NO = b.trade_no and a.trans_code = '6003'
and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)
表连接分页查询的使用
1、包含排序逻辑的分页查询写法，必须是三层select嵌套：
错误的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE rnum >= :4 AND rnum < :5
正确的写法：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
2、不包含排序逻辑的分页查询写法，则是两层select嵌套，但对rownum的范围指定仍然必须在不同的查询层次指定：
错误的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
) t1 WHERE rnum >= :4 AND rnum <= :5
正确的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= :4
) t1 WHERE rnum >= :5
3、注意下面两种写法的逻辑含义是不同的：
按创建时间排序（倒序），然后再取前10条：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM sell_offer t
WHERE owner_member_id = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= 10
) t2 WHERE rnum >= 1
随机取10条，然后在这10条中按照交易创建时间排序（倒序）：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= 10
ORDER BY gmt_create DESC
) t1 WHERE rnum >= 1
4、先连接后分页与先分页后连接
性能较差：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT a.*, b.receive_fee FROM beyond_trade_base a, beyond_trade_process b
WHERE a.trade_no = b.trade_no AND a.seller_account = :1 AND a.gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND a.gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY a.gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
性能较好：
SELECT /+ ordered use_nl(a,b) */ a., b.receive_fee FROM (
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
) a, beyond_trade_process b
WHERE a.trade_no = b.trade_no
后面这种写法的适用情况：
a、where子句中的查询条件都是针对beyond_trade_base表的（否则得到的结果将不相同）
b、关联beyond_trade_process表时，用的是该表的主键或者唯一键字段（否则将改变结果集的条数）
"<>"、"!="、"not in"、"exsits"和"not exists"的使用规范
1、原则上一般禁止使用"<>"、"!="和"not in"，而应该转换成相应的"="和"in"查询条件
错误的写法：
select a.id,a.subject,a.create_type from product
where status <> 'new' and owner_member_id = :1
正确的写法：
select a.id,a.subject,a.create_type from product
where status in ('auditing','modified','service-delete','tbd','user-delete','wait-for-audit') and owner_member_id = :1
错误的写法：
select a.id,a.subject,a.create_type from product
where create_type not in ('new_order','vip_add') and owner_member_id = :1
正确的写法：
select a.id,a.subject,a.create_type from product
where create_type = 'cust_add' and owner_member_id = :1
2、原则上不允许使用"exsits"和"not exists"查询，应转换成相应的"等连接"和外连接来查询
错误的写法：
select a.id from company a
where not exsits (select 1 from av_info_new b where a.id = b.company_id)
正确的写法：
select a.id from company a,av_info_draft b
where a.id = b.company_id and b.company_id is null
错误的写法：
select count from company a
where exsits (select 1 from av_info_new b where a.id = b.company_id)
正确的写法：
select count from company a,av_info_draft b
where a.id = b.company_id
注：在通过等连接替换exsits的时候有一点需要注意，只有在一对一的时候两者才能较容易替换，如果是一对多的关系，直接替换后两者的结果会出现不一致情况。因为exsits是实现是否存在，他不care存在一条还是多条，而等连接时返回所关联上的所有数据。
3、如有特殊需要无法完成相应的转换，必须在DBA允许的情况下使用"<>"、"!="、"not in"、"exsits"和"not exists"
SQLMAP的其它编写规范
1、对表的记录进行更新的时候，必须包含对gmt_modified字段的更新，并且不要使用dynamic标记，如：
错误的写法：
update BD_CONTACTINFO
<dynamic prepend="set">
......
<isNotNull prepend="," property="gmtModified">
GMT_MODIFIED = #gmtModified:TIMESTAMP#
</isNotNull>
</dynamic>
where ID = #id#
正确的写法（当然，这里更推荐直接更新为sysdate）：
update BD_CONTACTINFO
set GMT_MODIFIED = #gmtModified:TIMESTAMP#
<dynamic>
......
</dynamic>
where ID = #id#
2、不允许在where后添加1=1这样的无用条件，where可以写在prepend属性里，如：
错误的写法：
select count from BD_CONTRACT t where 1=1
<dynamic>
......
</dynamic>
正确的写法：
select count from BD_CONTRACT t
<dynamic prepend="where">
......
</dynamic>
3、对大表进行查询时，在SQLMAP中需要加上对空条件的判断语句，具体可在遇到时咨询DBA，如：
性能上不保险的写法：
select count from iw_user usr
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="userId">
usr.iw_user_id = #userId:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="email">
usr.email = #email:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certType">
usr.cert_type = #certType:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certNo">
usr.cert_no = #certNo:varchar#
</isNotEmpty>
</dynamic>
性能上较保险的写法（防止那些能保证查询性能的关键条件都为空）：
select count from iw_user usr
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="userId">
usr.iw_user_id = #userId:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="email">
usr.email = #email:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certType">
usr.cert_type = #certType:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certNo">
usr.cert_no = #certNo:varchar#
</isNotEmpty>
<isEmpty property="userId">
<isEmpty property="email">
<isEmpty property="certNo">
query not allowed
</isEmpty>
</isEmpty>
</isEmpty>
</dynamic>
另外，对查询表单的查询控制建议使用web层进行控制而不是客户端脚本（JAVASCRIPT/VBSCRIPT）
4、聚合函数常见问题
1) 不要使用count(1)代替count（*）
2) count(column_name)计算该列不为NULL的记录条数
3) count(distinct column_name)计算该列不为NULL的不重复值数量
4) count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(qty),0)来避免返回NULL
5、NULL的使用
1) 理解NULL的含义，是"不确定"，而不是"空"
2) 查询时，使用is null或者is not null
3) 更新时，使用等于号，如：update tablename set column_name = null
6、STR2NUMLIST、STR2VARLIST函数的使用：
1) 适用情况：使用唯一值（或者接近唯一值）批量取数据时
2) 编写规范：a表必须放在from list的第一位，并且必须在select后加上下面的hint
正确的写法：
select /+ ordered use_nl(a,b) */ b.
from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
where a.column_value = b.trade_no;
大批量数据更新分批提交的规范
假设：
源： create table test_A(id number,name varchar2(40),tel varchar2(40),sex char(1));
目标： create table test_B(id number,name varchar2(40),tel varchar2(40),sex char(1));
逻辑： 从test_A找出符合的记录，然后update test_B;
实现过程（这个也可以作为批量提交的模板,方便以后大家使用）：
declare
cnt number := 0;
begin--找出满足条件的记录，循环更新
for i in (select id,name,tel from test_A where name = 'wt') loop
--更新记录，然后记数加1
update test_B set test_B.name = i.name,test_B.Tel = i.tel
where test_B.id = i.id;
cnt := cnt + 1;
--累计到1000条提交一次，也可以是其他参数
if mod(cnt,1000) = 0 then
commit;
dbms_application_info.SET_CLIENT_INFO('have update ' || cnt || ' rows');
end if;
end loop;--最后外面COMMIT一次，防止最后一次未达到1000条，漏掉一批数据
commit;
dbms_application_info.SET_CLIENT_INFO( cnt || ' rows finished!');
end;
产品库ddl变更流程
执行变更时间的约定
变更之前要分析操作对象的访问频繁程度，按照等级考虑变更的时间和方式，非紧急变更，都安排在下班后、晚上空闲时执行。
提交人和审批人，都要严格遵守该约定。
ddl变更的范畴
1.表结构变更(非新增表)
变更之前要列出表的依赖关系，准备重新编译的脚本，变更后马上编译invalid对象。
对于访问程度很高的表，如果有依赖的trigger，按照以下步骤执行：
准备好数据订正的sql->disable trigger->变更->compile trigger->enable trigger->补足、订正这段时间内变化的数据
2.分析统计信息
包括分析表、索引、columns
分析之前一定要把统计信息做好备份(export)，并把回滚的sql写好(delete,import,尤其是列的柱状图，需要使用 dbms_stats包才能删除)，
一旦出现问题，马上执行回滚方案。
3.调整index
对现有的index作调整、或者新建index之前，尽量获得和这个表相关的sql(可以查询v$sqlarea)，在测试库上充分测试新 index对sql的影响。
4.可能导致执行计划突变的变更
比如对表做grant操作，move table，rebuild index等(还有哪些情况？)
都会引起相关sql重新parse，可能导致执行计划突变，操作前后要特别注意影响。
5.系统级别的变更
比如修改系统参数，打开某 event等(还有哪些情况？)。
数据库开发规范
命名规范
表：同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义，长度以不超过15个字符为宜(避免超过20)
字段名：表达其实际含义的英文单词或简写，不要使用类似VALUE1这种无意义的字段名
索引：表名称_字段名_IND
约束：表名称_PK（或者PK_表名称），表名称_UK（或者UK_表名称）,务必保持一致
触发器：表名称_A(After)B(Before)I(Insert)U(Update)D(Delete)_TRG，如果是同步触发器以 sync作为前缀：sync_表名_trg，函数过程：采用动词+名词的形式表达其含义
常用数据类型
NUMBER(p,s):固定精度数字类型。
NUMBER:不固定精度数字类型，当不确定数字的精度时使用，PK通常使用此类型。
DATE:当仅需要精确到秒时，选择DATE而不是TIMESTAMP类型。
TIMESTAMP:扩展日期类型，不建议使用。
VARCHAR2:变长字符串，最长4000个字节，
CHAR:定长字符串，除非是CHAR(1),否则不要使用。
CLOB:当超过4000字节时使用，但是要求这个字段必须单独创建到一张表中，然后有PK与主表关联。此类型应该尽量控制使用。
表：
a)如果有必要，总是包含两个日期字段:gmt_create(创建日期),gmt_modified(修改日期)。
b)尽可能使用简单数据类型，不要使用类似数组或者嵌套表这种复杂类型。
c)必须要有主键，且尽量不要使用存在实际意义的字段做主键。
d)需要join的字段，数据类型保持绝对一致。
e)允许适当冗余，以提高性能，但是必须考虑数据同步的情况。
f)当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表（主要是为了性能考虑）。
PK约束：
a)PK最好是无意义的，由Sequence产生的ID字段，不建议使用组合PK。如果除了PK以外，还存在其他的唯一约束，可以创建UK。
b)不要试图通过创建唯一索引来达到唯一约束的效果，不管是pk还是unique约束（实际上我们严格控制建立unique index，因为一旦建立，这个索引的的唯一约束性将永远无法在不影响索引的情况下被摘除），必须显式创建普通索引和约束，而不是仅仅创建一个NOT NULL+UNIQUE INDEX的组合（即先create一个以约束名命名的index（普通索引，而不是带约束的索引），然后创建一个约束，并 using index ...,这样做的好处就是当需要摘掉约束的时候可以做到不影响索引）。
c)当摘除约束的时候，为了确保不影响到index，最好加上keep index参数。
d)PK字段不能被更新。
e)外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制。
f)当万不得已必须使用外健的话，必须在外健列创建INDEX。
索引
a)Bitmap索引通常不适合我们的环境。
b)索引根据实际SQL，由DBA创建。
c)索引不是万能的，很多情况下索引并不能解决问题。
d)严格控制创建带约束的索引，所有的约束效果都通过显示创建约束然后再using 一个已经创建好的普通索引来实现
sql编写规范
1.避免在where子句中对字段施加函数，这样将导致索引失效，比如：
select * from member where to_char(gmt_create,'yyyymmdd')='20070101';
原因：在建立index的时候是根据字段来建立的，也就是说oracle在inidex的时候是索引的字段的值，如果提供给oracle的是一个需要经过函数处理的比较，oracle就没办法通过索引中的索引键值来进行相应的比较，所以就不会走到索引上
2.避免在SQL中发生隐式类型转换
如：
select * from table where id='123'; -- # 这里ID是NUMBER型,会造成oracle将id先转换成varchar类型再比较，造成索引失效
select * from table where gmt_create = to_char('2000-01-01','yyyy-mm-dd');
-- # 会造成oracle先将gmt_create转换成varchar类型，造成无法走index，这里还有一个需要注意的，就是当和oracle中date 类型的字段比较的时候，最好先通过to_date('2000-01-01','yyyy-mm-dd')函数将传入的值转换一下，即使传入的是java 的date类型也这样做，因为这样也很可能让oracle出现隐式转换
3.全模糊查询无法使用INDEX，应当尽可能避免
select * from table where name like '%jacky%';
4.如果使用Oracle数据库，使用Oracle的外连接，而不是标准的外连接语法
正确：select * from table1 a,table2 b where a.id=b.id ;
错误：select * from table1 a left join on test2 b a.id=b.id;
5.分页语句必须使用三层嵌套的写法（不是10g的数据库不要使用分析函数）
select * from (
select rownum rn,a,* from (
select * from table where 条件 order by 条件
) a where rownum<=100
) where rn>80;
6.使用in的绑定问题
a)使用ibatis的in list绑定，但是必须注意不能超过1000项。
b)使用str2numlist_order,str2varlist_order函数，将一个字符串转换为内存表。
7.判断NULL应该使用IS NULL或者IS NOT NULL,而不是=null或者null
8.sql中的/+ ordered use_nl(member offer)/是hint，用来确定SQL的执行计划，请在 DBA确认后使用。
9.在使用ibatis时候，sqlmap文件编写需要严格遵守以下规范：Ibatis中的SqlMap编写规范
Ibatis中的SqlMap编写规范
select语句中*号的问题
1。表连接时，绝不允许写select * ，否则按照编码错误Bug处理。
2。单表查询，一般情况下可以使用select * ，但以下几种情况禁用：
a、表中包含lob字段（BLOB、CLOB、LONG、LONG RAW等）。
b、表中包含长度较大的字段，如varchar2(1000)以上的字段，但该SQL实际上并不需要取出该字段的值。
c、字段数量很多，但实际要用的字段很少，比如表有50个字段，而你实际只用5个，并且该sql目前没有被重用。
d、DBA要求优化调整的。
严格要求使用正确类型的变量，杜绝oracle做隐式类型转换的情况
1、推荐在sqlmap的变量中指定变量的数据类型，如：select * from iw_user where iw_user_id = #userid:VARCHAR#
2、其中，对于时间类型的字段，必须使用TO_DATE进行赋值（当前时间可直接用sysdate表示），不允许下列这些错误用法：
错误的写法（使用date类型的变量）：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= #dateBegin:date# and trans_dt < #dateEnd:date#
错误的写法（将to_date函数和数字进行算术运算）：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
and trans_dt < to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss') + 1
正确的写法：
select * from iw_account_log
where trans_account = #transaccount:varchar#
and trans_dt >= to_date(#dateBegin:varchar#, 'yyyy-mm-dd hh24:mi:ss')
and trans_dt < to_date(#dateEnd:varchar#, 'yyyy-mm-dd hh24:mi:ss') /*或 trans_dt < sysdate */
3、对于变量数据类型错误导致SQL严重性能问题的，按严重的编码错误Bug处理！
绑定变量和替代变量
在Ibatis中：
绑定变量用 #变量名# 表示
替代变量用 $变量名$ 表示
除了特别情况（经由DBA和架构组安全工程师审批）外，都只能使用绑定变量而不得使用替代变量，对于原来order by传入数组的问题，安全工程师已经要求全部更改掉，架构组会提供相应解决方案。
注意几点：
1) 通常，应使用绑定变量，尤其是具体取值变化范围较大的变量，如iw_user_id = #userid#。
2) 取值范围很小（比如枚举字段），并且通常取值会比较固定，在DBA预先同意的情况下使用替代变量，或者干脆使用常量。
3) 当一个绑定变量在实际使用中实际取值总是为某一固定常量时，应当直接使用常量而不是变量，关于这点，可在具体使用时咨询DBA
4) 在order by子句中，通常使用替代变量而不是绑定变量。
5) IN子句，使用"iterate + 数组类型变量"的方式实现绑定变量，例如：
<isNotEmpty prepend="and" property="userIds">
<iterate property="userIds" open="t.creator in (" close=")" conjunction=",">
#userIds[]#
</iterate>
</isNotEmpty>
将生成 t.creator in (:1, :2, :3, :4, :5 ...) 的语句
在字段上加函数的问题
1) 通常，不允许在字段上添加函数或者表达式，如：
错误的写法：
select * from iw_account_log where to_char ( trans_dt, 'yyyy-mm-dd') = '2007-04-04';
select qty from product where p_id + 12 = 168;
正确的写法：
select * from iw_account_log
where trans_dt >= to_date ( '2007-04-04', 'yyyy-mm-dd') and trans_dt < to_date ( '2007-04-05', 'yyyy-mm-dd');
select qty from product where p_id = 168 - 12;
2) 如果是业务要求的除外，但需要在编写时咨询DBA
3) 特别注意，当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数，如
错误的写法（a.id是number类型，而b.operator_number是char类型）：
select count from adm_user a, adm_action_log b where a.id = b.operator_number and a.username = '小钗';
正确的写法：
select count from adm_user a, adm_action_log b where to_char(a.id) = b.operator_number and a.username = '小钗';
select count from adm_user a, adm_action_log b where a.id = to_number(b.operator_number) and a.username = '小钗';
上面两种写法哪个正确？遇到这种情况时必须咨询DBA！
表连接
1) 不使用ANSI连接，如inner join、left join、right join、full outer join，而使用（+）来表示外连接
错误的写法：
select a.*, b.goods_title from iw_account_log a left join beyond_trade_base b on a.TRANS_OUT_ORDER_NO = b.trade_no
where a.trans_code = '6003' and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)
正确的写法：
select a.*, b.goods_title from iw_account_log a, beyond_trade_base b
where a.TRANS_OUT_ORDER_NO = b.trade_no and a.trans_code = '6003'
and a.trans_account = #transacnt:varchar# and a.trans_dt > to_date(...)
表连接分页查询的使用
1、包含排序逻辑的分页查询写法，必须是三层select嵌套：
错误的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE rnum >= :4 AND rnum < :5
正确的写法：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
2、不包含排序逻辑的分页查询写法，则是两层select嵌套，但对rownum的范围指定仍然必须在不同的查询层次指定：
错误的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
) t1 WHERE rnum >= :4 AND rnum <= :5
正确的写法：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= :4
) t1 WHERE rnum >= :5
3、注意下面两种写法的逻辑含义是不同的：
按创建时间排序（倒序），然后再取前10条：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM sell_offer t
WHERE owner_member_id = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= 10
) t2 WHERE rnum >= 1
随机取10条，然后在这10条中按照交易创建时间排序（倒序）：
SELECT t1.* FROM (
SELECT t.*, ROWNUM rnum FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd') AND ROWNUM <= 10
ORDER BY gmt_create DESC
) t1 WHERE rnum >= 1
4、先连接后分页与先分页后连接
性能较差：
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT a.*, b.receive_fee FROM beyond_trade_base a, beyond_trade_process b
WHERE a.trade_no = b.trade_no AND a.seller_account = :1 AND a.gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND a.gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY a.gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
性能较好：
SELECT /+ ordered use_nl(a,b) */ a., b.receive_fee FROM (
SELECT t2.* FROM (
SELECT t1.*, ROWNUM rnum FROM (
SELECT t.* FROM beyond_trade_base t
WHERE seller_account = :1 AND gmt_create >= TO_DATE (:2, 'yyyy-mm-dd') AND gmt_create < TO_DATE (:3, 'yyyy-mm-dd')
ORDER BY gmt_create DESC
) t1 WHERE ROWNUM <= :4
) t2 WHERE rnum >= :5
) a, beyond_trade_process b
WHERE a.trade_no = b.trade_no
后面这种写法的适用情况：
a、where子句中的查询条件都是针对beyond_trade_base表的（否则得到的结果将不相同）
b、关联beyond_trade_process表时，用的是该表的主键或者唯一键字段（否则将改变结果集的条数）
"<>"、"!="、"not in"、"exsits"和"not exists"的使用规范
1、原则上一般禁止使用"<>"、"!="和"not in"，而应该转换成相应的"="和"in"查询条件
错误的写法：
select a.id,a.subject,a.create_type from product
where status <> 'new' and owner_member_id = :1
正确的写法：
select a.id,a.subject,a.create_type from product
where status in ('auditing','modified','service-delete','tbd','user-delete','wait-for-audit') and owner_member_id = :1
错误的写法：
select a.id,a.subject,a.create_type from product
where create_type not in ('new_order','vip_add') and owner_member_id = :1
正确的写法：
select a.id,a.subject,a.create_type from product
where create_type = 'cust_add' and owner_member_id = :1
2、原则上不允许使用"exsits"和"not exists"查询，应转换成相应的"等连接"和外连接来查询
错误的写法：
select a.id from company a
where not exsits (select 1 from av_info_new b where a.id = b.company_id)
正确的写法：
select a.id from company a,av_info_draft b
where a.id = b.company_id and b.company_id is null
错误的写法：
select count from company a
where exsits (select 1 from av_info_new b where a.id = b.company_id)
正确的写法：
select count from company a,av_info_draft b
where a.id = b.company_id
注：在通过等连接替换exsits的时候有一点需要注意，只有在一对一的时候两者才能较容易替换，如果是一对多的关系，直接替换后两者的结果会出现不一致情况。因为exsits是实现是否存在，他不care存在一条还是多条，而等连接时返回所关联上的所有数据。
3、如有特殊需要无法完成相应的转换，必须在DBA允许的情况下使用"<>"、"!="、"not in"、"exsits"和"not exists"
SQLMAP的其它编写规范
1、对表的记录进行更新的时候，必须包含对gmt_modified字段的更新，并且不要使用dynamic标记，如：
错误的写法：
update BD_CONTACTINFO
<dynamic prepend="set">
......
<isNotNull prepend="," property="gmtModified">
GMT_MODIFIED = #gmtModified:TIMESTAMP#
</isNotNull>
</dynamic>
where ID = #id#
正确的写法（当然，这里更推荐直接更新为sysdate）：
update BD_CONTACTINFO
set GMT_MODIFIED = #gmtModified:TIMESTAMP#
<dynamic>
......
</dynamic>
where ID = #id#
2、不允许在where后添加1=1这样的无用条件，where可以写在prepend属性里，如：
错误的写法：
select count from BD_CONTRACT t where 1=1
<dynamic>
......
</dynamic>
正确的写法：
select count from BD_CONTRACT t
<dynamic prepend="where">
......
</dynamic>
3、对大表进行查询时，在SQLMAP中需要加上对空条件的判断语句，具体可在遇到时咨询DBA，如：
性能上不保险的写法：
select count from iw_user usr
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="userId">
usr.iw_user_id = #userId:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="email">
usr.email = #email:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certType">
usr.cert_type = #certType:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certNo">
usr.cert_no = #certNo:varchar#
</isNotEmpty>
</dynamic>
性能上较保险的写法（防止那些能保证查询性能的关键条件都为空）：
select count from iw_user usr
<dynamic prepend="where">
<isNotEmpty prepend="AND" property="userId">
usr.iw_user_id = #userId:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="email">
usr.email = #email:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certType">
usr.cert_type = #certType:varchar#
</isNotEmpty>
<isNotEmpty prepend="AND" property="certNo">
usr.cert_no = #certNo:varchar#
</isNotEmpty>
<isEmpty property="userId">
<isEmpty property="email">
<isEmpty property="certNo">
query not allowed
</isEmpty>
</isEmpty>
</isEmpty>
</dynamic>
另外，对查询表单的查询控制建议使用web层进行控制而不是客户端脚本（JAVASCRIPT/VBSCRIPT）
4、聚合函数常见问题
1) 不要使用count(1)代替count（*）
2) count(column_name)计算该列不为NULL的记录条数
3) count(distinct column_name)计算该列不为NULL的不重复值数量
4) count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用nvl(sum(qty),0)来避免返回NULL
5、NULL的使用
1) 理解NULL的含义，是"不确定"，而不是"空"
2) 查询时，使用is null或者is not null
3) 更新时，使用等于号，如：update tablename set column_name = null
6、STR2NUMLIST、STR2VARLIST函数的使用：
1) 适用情况：使用唯一值（或者接近唯一值）批量取数据时
2) 编写规范：a表必须放在from list的第一位，并且必须在select后加上下面的hint
正确的写法：
select /+ ordered use_nl(a,b) */ b.
from TABLE(CAST(str2varlist(:1) as vartabletype)) a, beyond_trade_base b
where a.column_value = b.trade_no;
大批量数据更新分批提交的规范
假设：
源： create table test_A(id number,name varchar2(40),tel varchar2(40),sex char(1));
目标： create table test_B(id number,name varchar2(40),tel varchar2(40),sex char(1));
逻辑： 从test_A找出符合的记录，然后update test_B;
实现过程（这个也可以作为批量提交的模板,方便以后大家使用）：
declare
cnt number := 0;
begin--找出满足条件的记录，循环更新
for i in (select id,name,tel from test_A where name = 'wt') loop
--更新记录，然后记数加1
update test_B set test_B.name = i.name,test_B.Tel = i.tel
where test_B.id = i.id;
cnt := cnt + 1;
--累计到1000条提交一次，也可以是其他参数
if mod(cnt,1000) = 0 then
commit;
dbms_application_info.SET_CLIENT_INFO('have update ' || cnt || ' rows');
end if;
end loop;--最后外面COMMIT一次，防止最后一次未达到1000条，漏掉一批数据
commit;
dbms_application_info.SET_CLIENT_INFO( cnt || ' rows finished!');
end;
产品库ddl变更流程
执行变更时间的约定
变更之前要分析操作对象的访问频繁程度，按照等级考虑变更的时间和方式，非紧急变更，都安排在下班后、晚上空闲时执行。
提交人和审批人，都要严格遵守该约定。
ddl变更的范畴
1.表结构变更(非新增表)
变更之前要列出表的依赖关系，准备重新编译的脚本，变更后马上编译invalid对象。
对于访问程度很高的表，如果有依赖的trigger，按照以下步骤执行：
准备好数据订正的sql->disable trigger->变更->compile trigger->enable trigger->补足、订正这段时间内变化的数据
2.分析统计信息
包括分析表、索引、columns
分析之前一定要把统计信息做好备份(export)，并把回滚的sql写好(delete,import,尤其是列的柱状图，需要使用 dbms_stats包才能删除)，
一旦出现问题，马上执行回滚方案。
3.调整index
对现有的index作调整、或者新建index之前，尽量获得和这个表相关的sql(可以查询v$sqlarea)，在测试库上充分测试新 index对sql的影响。
4.可能导致执行计划突变的变更
比如对表做grant操作，move table，rebuild index等(还有哪些情况？)
都会引起相关sql重新parse，可能导致执行计划突变，操作前后要特别注意影响。
5.系统级别的变更
比如修改系统参数，打开某 event等(还有哪些情况？)。
扫一扫
759
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
