五、对象设计和创建规范
建议1： 每个表的字段数目通常不要超过35个，最多不超过50个。同时，表中一条记录所有字段的长度不能超过数据库的DB_BLOCK_SIZE大小 (8192)
宽表的处理往往需要耗费更多的IO等资源，如果超过，可以创建多个小表的方式处理；如有特殊情况确实需要创建多字段的宽表，则按实际情况与数据库设计审核方讨论后决定。
建议2： 根据实际业务情况设置合理的字符型(VARCHAR2)字段数据长度
越长的字符的处理，需要带动更多的数据块的读写，耗费更多的IO，需要谨慎设计字符型字段的长度。
在设计表结构时，只存储数字的字段定义成数字类型，只存储字符的字段定义成字符类型，只存储日期的字段定义成日期类型，以减少使用过程中的数据类型转换。
规范2： 不允许使用大对象类型字段
不允许使用LONG，LOB等大对象类型，若字段有记录大数据的情况，建议将数据保存到文件，然后字段里记录文件的路径。
表和字段必须有中文注释，注释采用COMMENT ON的形式，如：
COMMENT ON TABLE SECURITYDATA.SEC_USER IS ‘用户信息表’;
COMMENT ON COLUMN SECURITYDATA.SEC_USER.UM_CODE IS ‘用户UM代码’;
规范4： 新建表时，约束的位置：非空约束，CHECK约束直接跟在字段后面；主键、外键、唯一键约束放在 CREATE TABLE括号之外，在创建表后采用ALTER TABLE的方式添加，并且添加约束时要使用USING INDEX指定主外键索引。
规范5： 除日志表、临时表外，其它新建表中，必须有数据创建人，创建时间，修改人，修改时间这4个字段，四个必须字段异动的内容建议通过程序代码实现，不建议TRIGGER方式实现（影响DB性能），如果通过程序实现，除程序外的若有其它途径的数据修改，则开发人员要保证正确维护这四个字段的值。
当新增数据时，可以使用程序对CREATED_DATE和UPDATED_DATE字段插入当前时间，如果没有指定插入这两个字段，系统默认会存入当前时间的时间戳。
当更新数据时，CREATED_DATE字段不允许修改，系统需确保UPDATED_DATE字段更新成当前时间戳。
UPDATED_DATE字段上需要创建索引。
字段名及数据类型、长度须与下面的保持一致：
创建人 CREATED_BY VARCHAR2(50);
创建时间 CREATED_DATE TIMESTAMP DEFAULT SYSTIMESTAMP;
修改人 UPDATED_BY VARCHAR2(50);
修改时间 UPDATED_DATE TIMESTAMP DEFAULT SYSTIMESTAMP;
规范6： 对于通过UM登陆系统或者直接用脚本来操作数据的情况，CREATED_BY，UPDATED_BY必须插入员工UM编码
建议3： 业务表不允许硬删除数据，统一采用打删除标记的方式实现软删除；如果有特殊情况确实需要删除业务数据，需要与DBA团队一起协商解决方案。
规范7： 除临时表、日志表以外，其它表都必须有主键，如果因业务逻辑无法建立主键，则新加一个ID字段来做主键
ID通常用序列（SEQUENCE）生成。
规范8： 所有外键上都必须创建索引
若没有对外键建立索引，则在对父表操作时，会对子表产生锁。
规范9： 表的设计必须遵循第一范式，尽量达到第二范式及第三范式
即不允许字段出现二义性；例如，表中有这样的字段，字段的值是由几位数字组成的代码，第一位表示客户类型，第二位表示渠道类型……，这种设计不符合第一范式，不允许有这样的情况。
规范10： 创建表时必须指定表的初始事务槽，值统一为6。
例如：
CREATE TABLE SEC_USER
(
CREATED_BY VARCHAR2(100),
CREATED_DATE DATE
) INITRANS 6;
规范11： CREATE TABLE语句参数不能包含STORAGE选项，不能包含NOLOGGING选项
因为DBA已经在表空间设置存储参数，不需要在表一级设置存储参数；如果设置成了NOLOGGING, 会影响数据库的灾备和恢复。
例如：以下建表语句，红色部分不能包含。
CREATE TABLE SEC_USER
(
CREATED_BY VARCHAR2(100),
CREATED_DATE DATE,
UPDATED_BY VARCHAR2(100),
UPDATED_DATE DATE,
UM_CODE VARCHAR2(4)
) INITRANS 6
TABLESPACE TBS_SECURITY01
PCTFREE 10
MAXTRANS 255
STORAGE
(
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
)
NOLOGGING;
规范12： 创建主键时必须先创建索引，再创建主键。
先创建唯一索引，再基于该索引创建主键。
在删除主键时若遗忘指定keep index，则删除主键的同时会将主键索引也一并删除。若仍然有SQL需使用该索引时就会引发性能问题，而且要重建该索引也可能需要花费较长时间。
因此需采用先创建索引再创建主键的方式，避免上述情况的发生。
以SEC_USER表为例，现在要在ID字段创建主键：
COL_NAME | TYPE | IS_NULL |
USERID | NUMBER | N |
DATE_BIRTH | DATE | Y |
则：
是否正确 | 举例 |
错误 | ALTER TABLE SEC_USER ADD CONSTRAINT PK_SEC_USER_USERID PRIMARY KEY(USERID); |
正确 | CREATE UNIQUE INDEX PK_SEC_USER_USERID ON SEC_USER(USERID); ALTER TABLE SEC_USER ADD CONSTRAINT PK_SEC_USER_USERID PRIMARY KEY(USERID) USING INDEX PK_SEC_USER_USERID; |
规范13： 对同一个表ADD/DROP/MODIFY多个字段时，每种情况需写成一个sql语句，既表达清晰，又能保证在报错的情况下可重复执行
例如：表SECURITYDATA.SEC_USER，加A、B、C三个字段，sql语句为：
ALTER TABLE SECURITYDATA.SEC_USER ADD(A VARCHAR2(20),B NUMBER(2),C VARCHAR2(3));
删除主键的注意事项：先创建索引，后创建主键（用USING INDEX INDEX_NAME指定使用该索引）的情况下，删除主键的时候无法同时删除索引。
所以在删除主键的时候，如果确定需要删除该主键使用的索引，则使用如下语句（增加DROP INDEX INDEX_NAME）同时删除索引：
ALTER TABLE SECURITYDATA.SEC_USER DROP CONSTRAINT PK_SEC_USER_USERID DROP INDEX PK_SEC_USER_USERID;
规范14： 修改表时，如果增加有DEFAULT值的列或者修改字段的非空属性，必须先做ddl增加字段，再做dml更新值，然后再做ddl增加字段的DEFAULT属性或非空属性。
例如：对于某个table，增加一个有DEFAULT值的列，不允许直接使用如下ddl来实现：
ALTER TABLE SECURITYDATA.SEC_USER ADD IS_LBS VARCHAR2(6) DEFAULT 'YES';
需要改为ddl,dml和ddl三个脚本：
ALTER TABLE SECURITYDATA.SEC_USER ADD IS_LBS VARCHAR2(6);
UPDATE SECURITYDATA.SEC_USER SET IS_LBS='YES' WHERE ……; (该DML语句需要支持断点续做和分段提交）
ALTER TABLE SECURITYDATA.SEC_USER MODIFY IS_LBS DEFAULT 'YES';
修改表的某字段为 not null，应该先将该列值为空的记录update为非空，再增加not null约束。
例如：对于某个table，修改字段A为 not null ：
UPDATE SECURITYDATA.SEC_USER SET A=’’ WHERE A IS NULL ……; （该DML语句需要支持断点续做和分段提交）
ALTER TABLE SECURITYDATA.SEC_USER MODIFY (A NOT NULL) ADD CONSTRAINT NN_TEST_A CHECK(A IS NOT NULL);
对表增加有default值且not null的列，应该先做ddl增加字段，再做dml更新值。
例如：对于某个table，增加一个有default值且not null的列，需要将如下ddl：
ALTER TABLE SECURITYDATA.SEC_USER ADD IS_LBS VARCHAR2(6) DEFAULT ‘YES' CONSTRAINT NN_TEST1_IS_LBS NOT NULL;
改为ddl,dml和ddl三个脚本：
ALTER TABLE SECURITYDATA.SEC_USER ADD IS_LBS VARCHAR2(6);
UPDATE SECURITYDATA.SEC_USER SET IS_LBS='YES' WHERE …...; (该DML语句需要支持断点续做和分段提交 )
ALTER TABLE SECURITYDATA.SEC_USER MODIFY (IS_LBS DEFAULT ‘YES’ NOT NULL) ADD CONSTRAINT NN_TEST1_IS_LBS CHECK (IS_LBS IS NOT NULL);
对于表数据的备份，参见《DML数据修改备份管理规范》。
规范15： 将表的DML权限授权给代码用户下的package，procedure，function使用时，必须直接赋权给代码用户，而不能通过角色授权
规范16： 除使用视图需要外，不允许使用with grant option方式赋权
例如：
是否正确 | 举例 | 说明 |
错误 | GRANT CONNECT,RESOURCT TO SECURITYDATA WITH GRANT OPTION; | |
正确 | GRANT CONNECT,RESOURCE TO SECURITYDATA; |
例如：
步骤 | 是否正确 | 举例 |
1 | 创建表 | -- CREATE TABLE CREATE TABLE IBDDATA.IBD_ELEC_CLIENT ( PARTYNO VARCHAR2(12) NOT NULL, ECIFNO VARCHAR2(12), CREATED_BY VARCHAR2(20), UPDATED_BY VARCHAR2(20), DATE_CREATED DATE NOT NULL DEFAULT SYSDATE, DATE_UPDATED DATE ) INITRANS 6; |
2 | 添加表注释 | -- ADD COMMENTS ON TABLE COMMENT ON TABLE IBDDATA.IBD_ELEC_CLIENT IS ‘电子账户用户’; |
3 | 添加列注释 | --ADD COMMENTS ON COLUMN COMMENT ON COLUMN IBDDATA.IBD_ELEC_CLIENT.PARTYNO IS ‘客户号’; |
4 | 创建主键唯一索引 | CREATE UNIQUE INDEX IBDDATA.IBD_ELEC_CLIENT_PARTYNO ON IBDDATA.IBD_ELEC_CLIENT(PARTYNO) INITRANS 16; |
5 | 创建普通索引 | CREATE INDEX IBDDATA.IBD_ELEC_CLIENT_ECIFNO ON IBDDATA.IBD_ELEC_CLIENT(ECIFNO) INITRANS 16; |
6 | 创建主外键及唯一约束 | ALTER TABLE IBDDATA.IBD_ELEC_CLIENT ADD CONSTRAINT PK_IBD_ELEC_CLIENT_PARTYNO PRIMARY KEY(PARTYNO) |
7 | 授权 | -- GRANT/REVOKE OBJECT PRIVILEGES GRANT SELECT,INSERT,UPDATE,DELETE ON IBDDATA.IBD_ELEC_CLIENT TO IBDOPR; GRANT SELECT,UPDATE ON IBDDATA.IBD_ELEC_CLIENT TO R_IBDOPR_DML; GRANT SELECT ON IBDDATA.IBD_ELEC_CLIENT TO R_IBDDATA_DML; GRANT SELECT ON IBDDATA.IBD_ELEC_CLIENT TO R_IBDDATA_QRY; |
8 | 创建同义词 | -- CREATE SYNONYM CREATE OR REPLACE PUBLIC SYNONYM IBD_ELEC_CLIENT FOR IBDDATA.IBD_ELEC_CLIENT; |
为了提高查询效率，可以根据不同的数据特点，为表建立适当的普通索引，如B-Tree索引/位图索引/反向索引/函数索引/降序索引/分区索引等。
以下为最常用的B-Tree索引的实例：
CREATE INDEX SECURITYDATA.IDX_SEC_USER_BRANCH_CODE ON SECURITYDATA.SEC_USER(BRANCH_CODE) INITRANS 16;
建议4： 关于索引的几个数量要求
1.一个表上的索引（主键索引和外键索引除外）不能超过5个。如果表上的索引过多，应评估该表的索引设计是否合理。
2. 一个字段不能被4个以上（包括4个）索引引用。如果某字段被4个以上索引引用，则需要考虑建立在该表上的索引设计是否合理。
3. 组合索引（主键/唯一约束除外）包含的字段应不超过3个。如果超过3个，则其后的字段需要进行评估。
以上对索引的评估，需开发DBA进行组织，并有评估报告。
规范17： 创建索引时必须指定索引的初始事务槽，值统一为16。
例如：
CREATE INDEX OWNER.IDX_NAME ON OWNER.TABLE(ID) INITRANS 16;
规范18： 删除索引的语句必须单独写在一个文件中，对索引的新建、重命名等操作的语句需写在另外的文件中。
建议5： 对大表(size>100MB)或者以后数据量会一直增长的表都应该建立索引
如果一个表中存在大量数据，而且单次查询需要很多时间的话，应该为该表建立适当的索引。
建议6： 创建索引要选择查询频率高的字段
应对数据库中执行频率高的SQL语句所涉及的表和字段建立索引，这里的字段包括两表join连接的字段、where子句里面的谓词条件字段。
规范19： 创建索引要选择数据区分度高的字段
选择索引字段，首先考虑查询数据区分度是否高，如果区分度很高则适合创建索引，如果不高则不适合创建索引。
比如某个表的记录数是1000条，而该表的索引列有900个不同的值（有100个是相同或是空）。这样索引的区分度为900/1000为0.9 。在这种情况下使用索引的效果会比较好，最好的索引的区分度是1.0（如主键索引），即该表的索引列的值都完全不同。相反，如果一个表记录数是1000，而索引列只有5个不同的值，则索引的区分度很差（只有0.005）。这样使用全表扫描要比采用索引好。
规范20： 禁止为大数据类型创建索引
Varchar2类型的字段宽度超过200的，不要建立索引，除此之外的其他类型大数据字段需要建立索引需要进行评估。
规范21： 组合索引字段的顺序，对于组合索引，最常用的字段放在前面，同等常用的字段，再按区分度，区分度高的放在前面。对于有等值条件和范围条件的组合索引，等值条件要放在前面。
对现有表（已部署且有大量数据）创建索引的步骤：
1．开发人员需先在开发环境执行预期使用索引的查询语句，获取查询时间，对执行计划进行评估。并将创建索引脚本通过邮件的方式提交给开发DBA。
2．开发DBA将脚本在开发环境部署，并通知开发人员。
3．开发人员再次进行查询测试，获取查询时间，对执行计划进行评估。并与开发DBA协商进行性能评估，将评估结果写入创建索引脚本。若是新增疑难或复杂的索引，开发DBA评估后不能确定的，可以提交申请找运营DBA一起评估。
4．开发DBA将该索引删除。
例如：
脚本模板 | 举例 | 说明 |
创建索引脚本模板 | --------------------------- --索引性能评估 --------------------------- --对现有表（已部署且有数据）创建索引 --数据量： --使用前时间: --使用后时间： --性能提高： % CREATE INDEX SECURITYDATA.IDX_SEC_USER_USERID ON SECURITYDATA.SEC_USER(USERID) INITRANS 16; | |
删除索引脚本模板 | DROP INDEX SECURITYDATA.IDX_SEC_USER_USERID; |
规范22： 所有可以被引用的对象都应该建同义词
表、视图、包、序列等可被引用的数据库对象，都应该创建同义词。特殊情况下不创建，需要提交开发DBA评估确认。
规范23： 一般情况下必须使用公共同义词，有冲突的情况才考虑使用私有同义词。
如有特殊情况，需提交开发DBA评估确认。
规范24： 创建同义词时禁止使用or replace关键字
为避免在未经审核的情况下覆盖已经存在的同义词，禁止在创建同义词脚本中直接使用or replace关键字。如果确实需要覆盖现有的同义词，应该分别提供删除同义词脚本和创建同义词脚本。
规范25： 删除不再使用的数据库对象时，必须删除关联的同义词。
删除不再使用的数据库对象时，如果不同时删除关联到该对象的同义词，则时间长了会产生大量冗余无用的同义词，并可能造成引用错误。
规范26： 同义词所指向的对象名称必须正确且存在。
由于创建同义词时，下层数据对象不存在也不会报错，因此务必保证同义词所指向的对象名称正确，且是确实存在的。
例如：
脚本模板 | 举例 | 说明 |
创建同义词脚本模板 | CREATE PUBLIC SYNONYM SEC_USER FOR SECURITYDATA.SEC_USER; | |
删除同义词脚本模板 | DROP PUBLIC SYNONYM SEC_USER; |
Ø 引用优先级：本用户schema对象名 > 私有同义词 > 公共同义词；
Ø 创建同义词时，并不需要下层数据对象一定存在，也无需对象访问权限；
Ø 删除同义词并不会删除其指向的对象；
Ø 禁止对package内部数据对象创建同义词；
Ø 若要在本用户的schema下创建私有同义词，你必须拥有CREATE SYNONYM权限；要想在其他用户的schema下创建私有同义词，必须拥有CREATE ANY SYNONYM权限；要想创建公共同义词，必须拥有CREATE PUBLIC SYNONYM系统权限。
建议7： create sequence 建议放在一行里，其它的minvalue、maxvalue、start with、increment by、cache、cycle、noorder选项建议各自单独一行，如下所示：
CREATE SEQUENCE SECURITYDATA.SEC_USER_USERID_SEQ
MINVALUE 1
MAXVALUE 999999999999
START WITH 1
INCREMENT BY 1
CACHE 1000
CYCLE
NOORDER;
其中：
(1) minvalue用于指定sequence的最小值。
(2) maxvalue用于指定sequence的最大值。
(3) start with用于指定sequence产生的第一个值。
(4) increment by用于指定步长。
(5) cache 用于让sequence预生成一些序列号cache在内存中。
(6) cycle 用于指定sequence到达最大值时从最小值又开始循环。
(7) noorder 用于指定sequence产生唯一的但不一定连续的序列号。
规范27： 删除sequence时必须带上属主的名字。
例如：
DROP SEQUENCE SECURITYDATA.SEC_USER_USERID_SEQ;
规范28： 要求所有的sequence都必须指定为循环模式（cycle）。
对使用循环的sequence，必须要考虑使用sequence值的字段是否作为主键或者其上是否有唯一性约束，如果是，则需要采取措施防止字段值出现重复的情况。
因特殊原因必须使用非循环模式(nocycle)的sequence，在指定其最大值时，必须要结合业务的量进行考虑，必须要保证业务量不会超过sequence的最大值，同时开发人员需要提供为什么要使用非循环(nocycle)模式及业务量是否会超过sequence的最大值的说明，由开发dba审核。
规范29： 要求所有的sequence都使用cache选项。
采用缓存（cache）技术，是为了减少对产生sequence值的等待。
在exclusive模式数据库中（单节点数据库）所有sequence的cache值最低为20；如果是单节点数据库架构转换成RAC数据库，CACHE值应该为原来的1.5倍，即RAC数据库每个节点的sequence cache值最低为30。
规范30： sequence的最大值不能超过字段宽度。
超过字段长度，得到的sequence值无法插入。
规范31： 创建sequence时，必须指定minvalue、maxvalue、start with、increment by、cache的值。
maxvalue的值要求设为全为9的数字，例如：maxvalue 999999999999(12位) 而不要设成maxvalue 911111。
规范32： 创建sequence的用户与使用此sequence的表的属主相同。
例如： pol_info表保单号字段所使用的序列，其属主就是pol_info表的属主lifedata，即表的属主和sequence的属主为同一个用户。
建议8： 建议使用noorder选项，如果业务对产生序列号的顺序有要求就用order。
在rac模式数据库中，使用noorder会减少节点之间数据交换，从而提高性能。