一哥们整理的Oracle的设计规范，相当的不错，贴这以备后续之需！
目录
1、数据库模型设计方法规范
1.1、数据建模原则性规范
1.2、实体型之间关系认定规范
1.3、范式化1NF的规范
1.4、范式化2NF的规范
1.5、范式化3NF的规范
1.6、反范式化冗余字段使用规范
1.7、数据库对象命名基本规范
2、表的设计规范
2.1、表的主键规范
2.2、表的主键列规范
2.3、使用注释的规范
2.4、一个表所含字段总长度的规范
2.5、一个表所含字段访问频繁度的规范
2.6、一个表所含数据量的规范
2.7、大对象字段（BLOB，CLOB）使用规范
2.8、增量同步表的设计规范
2.9、表的表空间使用规范
2.10、索引的表空间使用规范
3、设计分区表的规范
3.1、RANGE分区的规范
3.2、LIST分区的规范
3.3、HASH分区的规范
3.4、RANGE-LIST分区的规范
3.5、RANGE-HASH分区的规范
4、索引的设计规范
4.1、主键索引的规范
4.2、唯一约束索引的规范
4.3、外键列索引的规范
4.4、复合索引的规范
4.5、函数索引的规范
4.6、位图索引的规范
4.7、反向索引的规范
4.8、分区索引的规范
4.9、索引重建的规范
5、SQL访问规范
5.1、避免SELECT *
5.2、避免笛卡尔运算
5.3、使用CTAS备份
5.4、INSERT时需写全列名
5.5、大数据量的DML
5.6、完成事务及时commit
5.7、java的变量绑定
5.8、perl的变量绑定
5.9、避免重复访问：使用group
5.10、避免重复访问：竖向显示变横向现实
5.11、避免重复访问：用表更新表
5.12、数据库连接及时关闭
6、实施规范
6.1、Oracle版本规范
6.2、数据库部署规范
6.3、Oracle内存初始化
6.4、Oracle字符集选择规范
6.5、Oracle归档规范
6.6、磁盘结构规范
6.7、数据文件形式
6.8、开发人员的备份规范
1、数据库模型设计方法规范
1.1、数据建模原则性规范
|
原则 |
对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑模型图是项目组成员之间在数据库层面沟通交互的依据，必须规范画图（表，主键，外键，关系） |
|
衡量 |
对于表的个数在20个以上的模型，需要数据组参与设计，并需DBA作最终审核 |
|
方法 |
对于OLTP系统，采用范式化思想进行模型设计，对于OLAP系统，采用面向问题及多级颗粒度的思想进行模型设计 |
|
实施 |
采用主流的模型设计软件工具PowerDesigner，ERStudio，ERWin |
1.2、实体型之间关系认定规范
|
原则 |
所有实体型间的业务逻辑关系，除了语义上保留其原有的业务关系外，本质上都要转化成关系数据库的三种关系（1:1）（1:N）（N:M） |
|
衡量 |
对于3个及以上实体型之间的“多元关系”，需要数据组参与设计 |
|
方法 |
比如实体型A和实体型B之间的关系，可以通过问两个问题来确定他们之间的关系：一个A可以对应几个B？一个B可以对应几个A? （1）一个A对应一个B，相反一个B对应一个A，那么A对B就是1:1关系； （2）一个A对应多个B，相反一个B对应一个A，那么A对B就是1:N关系； （3）一个A对应多个B，相反一个B对应对个A，那么A对B就是N:M关系； |
|
实施 |
（1）1:1关系选取任何一个表的主键到另一个表中作为外键来体现； （2）1：N关系将1表的主键在N表中以外键形式存在来体现； （2）N:M关系采用“关系表”来体现，该关系表的主键是由相关实体表的主键组成的符合主键，各实体表主键不但组成了该关系表的主键，同时也被看作外键在该关系表中存在； （4）对于三个以上表之间的“多元关系”常需要和反范式化冗余字段结合起来设计，以保证查询速度； |
1.3、范式化1NF的规范
|
原则 |
OLTP系统的模型，需要符合第三范式 |
|
衡量 |
对于表在20个以上的模型，需要数据组参与设计 |
|
方法 |
范式化要求： INF：列是访问的最小单位，具有原子性，不可再被分割； |
|
实施 |
依据具体情况对相应属性进行拆分或者合并。 范式化1NF常见现象： 现象一：同一个属性值的不同细度把握，比如，常见的“姓名”这个属性，设计一：“姓名”是一个列，设计二：“姓”是一个列，“名”是一个列，两个列的值组合起来才表达一个“姓名”语义。两种设计方法，在不同的系统中都有应用，这主要是依据需求的细度来确定，灵活把握； 现象二：把多个属性值错误的作为一个属性值存储，比如：常见的OA系统要存储员工的各种属性，包括技能信息，技能范围：Oracle，JAVA，.NET,C#,Perl,UNIX等等，一种常见的错误设计是：设计一张员工表，其中有一个技能属性字段，然后某员工所掌握的多种技能用逗号（，）间隔，然后将这个字符串存储到这个员工表的技能属性字段中。这里的错误在于将多个属性值作为一个属性值存储在一个字段中，不能满足直接遍历员工对某个技能掌握情况，而且如果再要求说明员工对个技能的掌握程度（精通，熟悉，一般等等），则再增加字段，里面的对应关系将很容易错乱，这是严重违反1NF的情况。正确的设计应该是：两个实体表：一张是员工表，一张是技能字典表，一个员工可以掌握多个技能，也就是（1:N）关系，相反一个技能可以被多个员工掌握，也是（1:N）关系，双向都是（1:N）关系，那么综合起来员工和技能之间就是“多对多关系（N:M）”，依据前述规范，应该设计一张“关系表”来存储“多对多关系”，主键为复合主键（员工主键+技能主键），该关系有一个属性“技能掌握程度”。 |
1.4、范式化2NF的规范
|
原则 |
OLTP系统的模型，需要符合第三范式 |
|
衡量 |
对于表在20个以上的模型，需要数据组参与设计 |
|
方法 |
范式化要求： 2NF：满足1NF,不存在非主键属性对主键属性的部分依赖； |
|
实施 |
范式化2NF常见现象： 实体表中一般不会出现违反2NF的情况，因为都是“一个”主键列，而关系表是两个以上列的“复合”主键，故而关系表容易出现违反2NF的情况。主要是该关系表非主键外的属性，本该属于相关的某个实体表的，却放到了该关系表中，这使得该属性不能通过该关系表的复合主键唯一确定，DML操作会发生错误；如果违反了2NF，那么应该把这个属性从关系表中拆分，也许会单独形成一个表，绝大部分情况下是将该属性归并到某个相关的实体表中； 违反2NF的例子：学生考试情况中，有两个实体表：学生表和学科表，学生与学科之间的考试关系就是N:M的关系，就要创建一张关系表存储该多对多的考试关系，表的主键为学生编号和学科编号，属性为考试分数；那么“任课老师”该放在那里呢？如果放到考试关系表中，那么安排任课老师，必须先进行考试，这显然不符合实际，也就是任课老师不该依赖于学生编号和学科编号，只是依赖于学科编号，也就是说任课教师信息应该放在学科表中； |
1.5、范式化3NF的规范
|
原则 |
OLTP系统的模型，需要符合第三范式 |
|
衡量 |
对于表在20个以上的模型，需要数据组参与设计 |
|
方法 |
范式化3NF要求： 3NF：满足2NF，不存在非主键属性对主键属性的传递依赖； |
|
实施 |
范式化3NF常见现象： 违反3NF的情况，绝大多数是在含有外键的表中；比如A表中的外键字段Bkey是B的主键，那么依赖于Bkey的属性应当属于B表的属性，而不是A表，如果放入A表，则这些对A表的主键Akey的依赖，首先是依赖于A（BKey），而后通过A(BKey)对A（AKey）的依赖，传递依赖于A(Akey)；三种关系（1:1,1:N，N:M）都含有外键，都很可能发生违反3NF的情况。违反3NF的后果：会导致那些问题属性插入异常，或者被误删。 违反3NF的例子： 教师和学科之间，存在着上课关系，假设一个教师上一门课而且一门课只有一个教师上，那么该关系为1:1关系，将教师表的主键教师编号在学科表中以外键形式存在就表达了该1:1关系，那么教师的“联系电话”属性该放哪里呢？如果看到“教师编号”出现在了学科表中，就将联系电话放入学科表中，那么联系电话首先是对表中的教师编号依赖，再依据教师编号对学科的依赖，达到了学科编号的依赖，那么联系电话对学科编号的依赖就是传递依赖，违反了3NF，应该将其从学科表中拆出来放入教师表中，不然的话，会发生操作异常，比如，假设一个教师已经存在但是还没有为其分配科目，那么他的电话就无法存入库中。 |
1.6、反范式化冗余字段使用规范
|
原则 |
OLTP系统中在完成范式化工作之后，对某些表，可以适当反范式化增加冗余字段以提高数据访问性能；在OLAP中采用的是面向问题的设计思想，应该大量使用反范式化冗余信息 |
|
衡量 |
当SQL关连查询涉及到4张表时可考虑采用冗余字段 |
|
方法 |
常用在两个地方：（1）关系表中的冗余：在关系表中增加相关实体表的相关属性，以达到关连查询时减少表的关联数量的目的（2）层次关系中的冗余：在多层次的子父表关系中，将父表的属性存储在“子表”或者“孙子表”或者“重孙表”中； 反范式化冗余字段实例： （1）关系表中的冗余：比如在考试关系中，原本在学科表中的学分信息，可以冗余添加到考试关系表中，这样，每个学生得了多少学分，就可以直接从考试表得到，而无需关联学科表来得到； （2）多层关系中的冗余：假设为之范畴从大到小有国家表，省份表，城市表，城区表，社区表，它们之间的层次关系是通过上一级的主键在下一级中以外键形式存在来体现的，但是，如果需要问：某个设计属于哪个国家？这样就要关连查询所有的5张表，性能会很差，这时可以将国家编号以外键形式放入到社区表中做冗余，这样直接关联国家表和社区表即可得到答案。一般的，每间隔一级增加一个冗余外键，比如将国家编号放入城市表中，将城市编号放入社区表中 |
|
实施 |
如何保证冗余字段数据的正确性（一致性）是反范式化的关键，需要对冗余字段详细添加注释，说明冗余了什么，以及该字段的维护方法，常用维护方法如下： （1）如果在程序开发前设计的冗余字段，可以在正常的业务逻辑程序中一并处理（2）如果是程序完成之后增加的冗余字段，可以使用触发器维护（3）对于OLAP中大量存在冗余字段，可能需要使用单独的处理任务进行维护 |
1.7、数据库对象命名基本规范
第一：长度规范：凡是需要命名的对象其标识符均不能超过30个字符，也即：Oracle中的表名、字段名，函数名，过程名，触发器名，序列名，视图名的长度均不能超过30个字符；
第二：构成规范：数据库各种名称必须以字母开头，但严禁使用SYS开头；名称只能含有字母，数字和下划线“_”三类字符，“_”用于间隔名称中的各语义字段；不要使用DUAL作表名；
第三：大小写规范： 构成Oracle数据库中的各种名称（表明，字段名，过程名，视图名等等）的所有字符，必须使用大写，也就是不能在脚本中，对任何名称添加双引号“”来设定字符的大小写形式，只要不采用“”限制，Oracle自动会将各名称转化成大写。
2、表的设计规范
2.1、表的主键规范
遵循如下三点原则：第一：有无原则：除临时表和外部表，以及流水表，日志表外，其他表都要建立主键；第二：构成原则：主键不能使用含有实际语义的列，应该增加一个xx_id字段做主键，类型为number，取值来自序列sequence；第三：创建原则：对于500万以上的表，请数据组参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键；
2.2、表的主键列规范
对于实体表，主键就是一列，就是没有任何语义的自增的NUMBER列，对于关系表，主键就是相关实体表主键形成的复合主键，是多列；
2.3、使用注释的规范
|
原则 |
每个表，每个字段都要有注释，说明其含义，对于冗余字段还要特别说明其维护方法，外键字段说明参照与那个表 |
|
衡量 |
原则上谁设计谁注释 |
|
方法 |
查询字典表user_tab_comments和user_col_comments可知道表和字段的注释信息 |
|
实施 |
对表添加注释： SQL>comment on table <table_name> is 'xx'; 对字段添加注释： SQL>comment on column<table_name>.<col_name> is 'xx'; |
2.4、一个表所含字段总长度的规范
|
原则 |
一个表中的所有字段，应当能存储在一个数据块中（BLOCK），也即：表的单行字段总长度<db_block(减去pctfree) |
|
衡量 |
对不含有大对象数据类型字段的表，字段数大于50个的，请数据组参与设计 |
|
方法 |
查询字典表USER_TAB_COLUMNS中的字段DATA_LENGTH得到表中所有字段的总长度，再依据db_block和表的pctfree参数可以判断是否一个数据行可以存储在一个数据块(BLOCK)中 |
|
实施 |
如果所有字段的总长度超出了一个数据块，那么需要将该表拆分成两个（甚至多个）表，拆分的依据是字段的频繁使用程度，也就是频繁使用的字段在一个表中，很少被使用的字段放在另一个表中，他们之间使用相同的主键值，用主外键关联。这点就是“一个表所含字段访问频繁度的规范” |
2.5、一个表所含字段访问频繁度的规范
|
原则 |
一个表中的各字段的访问频繁度应该基本一致 |
|
衡量 |
如果一个表的字段数超过50个， 请数据组参与审核 |
|
方法 |
如果一个表的字段数过多超过50个，并且依据业务逻辑确定该表中一些字段频繁被访问，另一些字段则很少被访问，则该表需要做拆分处理，这在OLAP系统中比较常见； 目的：这样可以避免读取频繁信息时多读取很少被访问的信息，可以提高IO性能，减少内存耗费； |
|
实施 |
将访问频繁度相差太远的字段拆分到两个表中，一个表存频繁访问的字段，另一个表存很少被访问的字段; |
2.6、一个表所含数据量的规范
|
原则 |
一个非分区表中的数据量不要超过500万 |
|
衡量 |
一个非分区表中的数据量超过500万， 请数据组参与设计成分区表，如果该表数据量超过5000万，请DBA参与设计 |
|
方法 |
在系统上线前，通过对业务分析，判断一个表的数据量；在系统上线后，可以通过exp的日志，Top性能SQL，count(1)来发现数据量大的表 |
|
实施 |
将这些表进行分区，具体方法请参看分区表的设计规范 |
2.7、大对象字段（BLOB，CLOB）使用规范
|
原则 |
存储图片，视频，音频，文件，500字节以上文本等占用太多空间的字段（大对象字段），不能和其他字段存储在一个表中 |
|
衡量 |
含有大对象（BLOB，CLOB）字段的表设计和存储请数据组参与设计 |
|
方法 |
方法一：数据库存储，可以重新建一个表专门存储该大对象字段，该表基本为两个字段，一个为大对象编号ID为主键，一个为大对象内容本身，并将该主键在原表中作外键关联，该大对象表存储在单独的表空间中；方法二：操作系统存储，将这些文件存储在操作系统空间中，大对象字段存储该文件的全路径名 比较：如果该大对象字段常被修改，那么采用方法一； 如果该大对象信息为静态，加载后基本不变，那么可以采用方法二，它有一个致命缺点就是信息存储在数据库外部，不安全，容易丢失。 |
2.8、增量同步表的设计规范
字典信息表和需要使用增量同步的表必须增加如下属性：
|
属性名 |
类型 |
取值 |
说明 |
|
Status |
Char(1) |
Y/N：Y为激活N为作废，默认为Y |
标识该行是否使用。用于软删除，软删除需将主键和唯一约束列添加随机数后缀。 |
|
Create_time |
Date |
默认为sysdate |
创建时间 |
|
Update_time |
Date |
默认为sysdate |
最后修改时间 |
2.9、表的表空间使用规范
|
原则 |
依据表的DML频度而使用不同的表空间 |
|
衡量 |
表空间的规划由建库人员完成 |
|
方法 |
为了减少空间碎片问题，（1）将很少被DML（增删改）的静态表，放在一组表空间中；（2）将只发生INSERT的表放到一组表空间中，（3）而将常发生两种以上DML操作的动态表放在另一组表空间中，这三组表空间不能相交 |
|
实施 |
在上线前，依据需求分析确定动态表和静态表，将他们做最分离；上线运行之后，依据数据库性能分析得到的信息来区分动态表和静态表 |
2.10、索引的表空间使用规范
|
原则 |
表和索引原则上应该使用不同的表空间存储，并且不同DML频度的表的索引，放在不同的表空间中 |
|
衡量 |
表空间的规划有数据组或者DBA实施 |
|
方法 |
绝大多数情况下，动态表的索引必然是动态的，静态表的索引必然是静态的，依据对表的分析来确定索引的动静性 |
|
实施 |
将动态表的索引放在一组表空间中，静态表的索引放在另一个表空间中，两组不相交。而且和表所在的表空间也不相交。 |
3、设计分区表的规范
3.1、RANGE分区的规范
|
原则 |
大数据量的表需进行分区化 |
|
衡量 |
当表的数据量超过500万， 请数据组参与设计成分区表，当表的数据量超过5000万，请DBA参与设计 |
|
方法 |
SQL常依据某列的范围访问表，则对表使用RNAGE分区，常见情况是SQL根据时间范围进行查新，则使用时间字段作为分区关键字进行RANGE分区； 将对表的多种访问结合考虑来确定分区的细度： 1.大多数SQL操作的分区关键字值的范围； 2.数据维护的需要，比如以月为单位删除历史数据； 3.数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于500万，则还需要进行细分； 4.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很多，所以需要灵活掌握； |
|
实施 |
1.当各个分区中的数据能均等划分时性能最好，如果相差太大，则考虑采用其它分区，或者将大数据量的分区再进行HASH子分区； 2.各分区采用各自的表空间存储，使用user_tab_partitions字典来查看确定每个分区的表空间位置； 3.分区表的索引采用本地索引，因为常会根据分区关键字（比如时间）进行分区维护（比如删除1年前的数据，也就是删除1年前的分区），分区维护时全局索引会失效，而本地索引不会失效，这能保证访问表时索引正常可用； |
3.2、LIST分区的规范
|
原则 |
大数据量的表需进行分区化 |
|
衡量 |
当表的数据量超过500万， 请数据组参与设计成分区表，当表的数据量超过5000万，请DBA参与设计 |
|
方法 |
SQL常居于某列的散列值访问表，则对表使用LIST分区，LIST分区不支持多列分区关键字；常见情况针对某个地区或者某个业务进行数据访问，那么就使用地区编号或者业务编号作为分区关键字； 将对表的多种访问结合考虑来确定分区的细度： 1.一般使用一个分区关键字的值来划定一个分区； 2.可以把分区关键字的值相对应数据比较少的几个分区合并作一个分区； 3.如果一个分区关键字值所对应的数据量过大，比如大于500万，则应该对表采用RANGE分区，对该值的分区再采用HASH子分区；也就是说，一个可以采用LIST分区的表，肯定可以转化成RANGE分区（可带子分区），反之不然； 4.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要快很多，所以需要灵活掌握； |
|
实施 |
1.各分区采用各自的表空间存储，使用user_tab_partitions字典来确定每个分区的表空间； 2.分区表的索引采用本地索引 |
3.3、HASH分区的规范
|
原则 |
大数据量的表需进行分区化 |
|
衡量 |
当表的数据量超过500万， 请数据组参与设计成分区表，当表的数据量超过5000万，请DBA参与设计 |
|
方法 |
SQL访问表不按照某列的范围进行，也不按某列离散值进行，而且对该表的数据不会依据某列的值范围或者离散值进行定期维护，那么使用HASH分区；HASH分区是不知道应该选择何种分区时的选择；HASH分区的各分区都可能存有各种情况的数据，故而不能用于依据分区清理数据的情况； 对确定分区细度的考虑： 1.依据分区的数据量规划和表的最大数据量来确定分区数； 2.一个分区的数据量要小于500万，这是一个硬性的尺度，但从技术上来看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要快很多，所以需要灵活掌握； |
|
实施 |
1.各分区采用各自的表空间存储，使用user_tab_partitions字典来确定每个分区的表空间； 2.对于HASH分区表，大多数情况下依然要求采用本地索引，但是如果分区过细，也可以采用全局索引，因为根据HASH分区表的特征（各分区无业务区分，都有数据），该表很少会发生分区维护的工作； |
3.4、RANGE-LIST分区的规范
|
原则 |
大数据量的表需进行分区化 |
|
衡量 |
当表的数据量超过500万， 请数据组参与设计成分区表，当表的数据量超过5000万，请DBA参与设计 |
|
方法 |
SQL访问表时，既依据某列值的范围，又依据其他列的离散值或者范围，这种情况下采用RANGE-LIST复合分区，常用于语表中的数据需要依据一个时间字段做周期性删除等维护，并且正常业务SQL访问既依据时间字段，又依据其他字段的散列值进行访问的情况； 比如：电信增值业务计费表，既有时间又有业务属性列，统计的时候，会选择时间范围和业务属性，所以可以以时间列为分区关键字建立RANGE分区，以业务属性列为关键字建立LIST子分区； 分区划分的方法： 1.就按照大多数范围访问的范围值来划定RANGE分区的范围，依据单个LIST子分区关键字的值来划分子分区; 2.如果LIST子分区中数据量较小而且又常被一起访问的子分区可以合并成一个子分区； 3.如果LIST子分区中一个子分区关键字值对应的子分区数据量还是很大，超过500，影响性能，那么可以通过细分RANGE分区来达到减少LIST子分区数据量的目的，这点和LIST分区在该情况下的处理方法（转化成RANGE-HASH）不同； |
|
实施 |
1.各子分区应该尽量分散到不同的表空间中存储，使用user_tab_subpartitions字典来确定每个子分区的表空间； 2.RANGE-LIST大多数情况采用本地索引，因为常根据RANGE分区关键字的来进行分区维护； |
3.5、RANGE-HASH分区的规范
|
原则 |
大数据量的表需进行分区化 |
|
衡量 |
当表的数据量超过500万， 请数据组参与设计成分区表，当表的数据量超过5000万，请DBA参与设计 |
|
方法 |
SQL访问表时，主要依据某个列的范围进行访问，即访问特征符合RANGE分区的要求，或者数据维护特征符合RANGE分区的要求，但是以SQL或者维护的数据范围来划定分区，分区数据量又很大，对性能有影响，需再进行子分区，由于分区中的数据都会被访问到，所以子分区采用HASH方法，整个表就是RANGE-HASH分区； 划定分区的方法： 先按照大多数范围访问的范围值来划定RANGE分区的范围，再依据性能情况来确定HASH子分区的数据量; |
|
实施 |
1.各子分区应该尽量分散到不同的表空间中存储，使用user_tab_subpartitions字典来确定每个子分区的表空间； 2.RANGE-HASH大多数情况采用本地索引，因为常根据RANGE分区关键字的来进行分区维护； |
4、索引的设计规范
4.1、主键索引的规范
|
原则 |
对数据量表应该先在主键列建唯一索引，再建主键约束 |
|
衡量 |
分区表的主键必须采用该方法 |
|
方法 |
主键上隐含索引，drop或disable主键时，索引会丢失，为保证性能不变，为了对主键约束和相应索引有更多的控制，对大表(分区表)的索引采用如下方式建立： (1)在准备建主键的列上建立唯一索引（UNIQUE INDEX）: CREATE UNIQUE INDEX Index_Name ON Table_Name(Column_Name) TABLESPACE TBS_INDEX; (2)再加上主键约束： ALTER TABLE Table_Name ADD (PRIMARY KEY(Column_Name) USING INDEX TABLESPACE TBS_INDEX ); Oracle会在指定的列上加上主键约束，并且使用该索引 |
|
实施 |
分区表的主键默认索引是全局索引，所以主键索引的分区方法：先建立分区化的唯一索引，再建主键约束， |
4.2、唯一约束索引的规范
|
原则 |
针对大数据量表应该先在唯一约束列上建立普通索引，再添加唯一性约束 |
|
衡量 |
分区表的唯一约束必须采用该方法 |
|
方法 |
删除或禁用唯一性约束通常同时使相关联的唯一索引失效，因而降低了数据库性能。要避免这样问题，可以采取下面的步骤：（a）在唯一性约束的列上创建非唯一性索引（普通索引）；（b）添加唯一性约束； |
4.3、外键列索引的规范
|
原则 |
无论表的大小，外键列都要建立索引 |
|
原因 |
其一是为了子父表关联查询的性能考虑，其二是为了避免父子表修改而发生死锁 |
|
实施 |
普通表的外键列建立普通索引即可，如果表是分区表，则依据表的情况建立本地索引或者全局索引 |
4.4、复合索引的规范
|
原则 |
复合索引只有在该种复合常被和该表相关的大多数SQL使用时才建立 |
|
衡量 |
复合索引的列数不能超过3个，否则该索引很少会被使用 |
|
实施 |
(1)复合索引的第一列，可以通过不使用该种复合的SQL来确定。假设一些SQL的WHERE中复合使用列为ABC，而其他一些SQL的WHERE中常使用的是C列，那么该复合索引可以按照CAB的顺序建立，这样上述两种SQL都能使用该索引； (2)对于不能把握好的复合索引，请在选择性大的列上分别建立单列索引； (3)切忌不能将表相关的所有SQL中WHERE涉及到的列复合起来建立复合索引； |
4.5、函数索引的规范
|
原则 |
由于使用形式需和创建形式一致，尽量避免使用函数索引 |
|
衡量 |
如果想要使用函数索引，请尽量进行转化 |
|
方法 |
由于函数索引在使用时，使用形式必须和创建形式一致，故应该尽量避免使用函数索引，尽量采用如下方法转化SQL以避免函数索引的使用： 原本在WHERE中列上添加函数的，取函数的反意义函数添加到“=”另一侧的常数项上，这样只需要在列上建立普通索引即可，比如常见的日期转化函数： TO_CHAR(CREATE_TIME)=’2010-07-07’采用TO_DATE()转化为CREATE_TIME=TO_DATE(‘2010-07-07’,’yyyy-mm-dd’) |
4.6、位图索引的规范
|
原则 |
静态表中的低基数列可以使用位图索引 |
|
衡量 |
在事务型数据库（OLTP）中禁止使用位图（bitmap）索引，在报表型数据库（OLAP）中的静态表，可以适当使用； |
|
方法 |
对于常发生DML操作的表，不能建立位图索引，请建立普通的索引即可，否则该表的相关操作很容易造成锁等待，使系统性能大受影响；其次，索引列需要低基数，只有几个数值，比如性别列（男，女，保密）和学历列（大专，本科，研究生，博士生） |
4.7、反向索引的规范
|
原则 |
列值顺序增加的列，其上的WHERE运算是<>或者=而不是范围（between and或者 < and >）检索时，可以采用反向函数 |
|
衡量 |
一般创建反向索引的列为NUMBER类型，值由SEQUENCE生成 |
|
方法 |
|
|
实施 |
4.8、分区索引的规范
|
原则 |
对分区表的索引，需要做分区维护的，必须使用局部索引 |
|
衡量 |
一般情况下，HASH分区表可以采用全局索引，其他分区，包括RANGE-HASH也应该采用本地索引，主要是由于HASH分区表不常进行分区维护； |
|
方法 |
|
|
实施 |
4.9、索引重建的规范
|
原则 |
重建索引使用ALTER INDEX REBUILD方式，禁止采用DROP INDEX & CREATE INDEX方式； |
|
衡量 |
分区表等大数据量表的索引必须采用ALTER INDEX REBUILD方式重建 |
|
方法 |
ALTER INDEX IDX_NAME REBUILD [TABLESPACE TBSP_NAME] |
|
实施 |
可以在现网上直接实施 |
5、SQL访问规范
5.1、避免SELECT *
程序中不能出现SELECT*，即使是选择全部选择项，也需要全部指明，这主要出于如下原因：第一：使用*相对比较慢，因为Oracle需要遍历更多的内部字典信息；第二：为避免以后相关表增加字段造成程序错误，比如INSERT INTO SELECT和SELECT INTO语句会报错；
5.2、避免笛卡尔运算
多表关联查询不能出现笛卡尔积，如果在报表中为集聚表（或称中间表）生成多个维度组成的复合主键需要使用迪克尔积的，必须请数据组确认性能。
5.3、使用CTAS备份
在进行DML操作（INSERT，UPDATE，DELETE）之前，必须对数据进行备份，使用如下语句：
方法一：表数据全部备份：
CREATETABLE TAB_NAME_BAK AS SELECT * FROM TAB_NAME;
方法二：部分备份：对大表仅备份将要修改的数据：
CREATE TABLE TAB_NAME_BAK
AS SELECT * FROM TAB_NAME WHERE [选择出被操作数据的条件];
5.4、INSERT时需写全列名
代码中INSERT语句必须写出全部列名，以保证表增加字段后语句执行不受影响：
如：INSERT INTO TAB(COL1,COL2)VALUES(COL1_VAL,COL2_VAL)；
再如：
INSERT INTO TAB(COL1,COL2)
SELECT COL1_VAL,COL2_VAL FROM TAB_BB；
不能将COL1,COL2和COL1_VAL,COL2_VAL省略;
5.5、大数据量的DML
DML操作涉及到大数据量时，请分解为多次执行；
对于UPDATE和DELETE每次涉及数据量在1万条左右，并且每次执行完就提交；
对于INSERT INTO SELECT如果采用提示(/*+ append parallel */)可以处理百万级别的数据量。
5.6、完成事务及时commit
对于一个完成了的事务，请用commit显示提交，这是避免锁争用的锁等待的需要，特别是对DML操作频繁的表；
5.7、java的变量绑定
使用“变量绑定”来处理一条SQL带不同常量多次执行的情况，动态绑定可以大大优化SQL的执行效率，还可以优化Oracle的内存使用。
在Java中，结合使用setXXX系列方法，可以为不同数据类型的绑定变量进行赋值，从而大大优化了SQL语句的性能。
JAVA情况下的动态绑定示例如下：
String v_id = 'xxxxx';
String v_sql = 'select name from tb_a where id = ? ';
stmt = con.prepareStatement( v_sql );
stmt.setString(1, v_id ); //为绑定变量赋值
stmt.executeQuery();
5.8、perl的变量绑定
使用“变量绑定”来处理一条SQL带不同常量多次执行的情况，动态绑定可以大大优化SQL的执行效率，还可以优化Oracle的内存使用。
PERL绑定变量实例如下：
$modsql = qq{
insert into tmp_tai_rtkpi_mark
(tab_name,
kpi_id,
ne_id,
timepoint,
cacu_time,
start_time,
stop_time,
down_base,
up_base,
ajast_flag,
inuse_flag,
cal_data)
values
(?,?,?,?,?,?,?,?,?,?,?,?)
};
if ( !$dbh->prepare($modsql) ) {
writeToLog( "start SQL prepare Error!/n" . DBI::errstr . "/n/n" );
}
$sth_msg_in_DB = $dbh->prepare($modsql)
|| die( "start SQL prepare Error!/n" . $DBI::errstr . "/n" );
$sth_msg_in_DB->bind_param( 1, $kpiid_tab{$kpi_id} );
$sth_msg_in_DB->bind_param( 2, $kpi_id );
$sth_msg_in_DB->bind_param( 3, -1 );
$sth_msg_in_DB->bind_param( 4, -1 );
$sth_msg_in_DB->bind_param( 5, $current_time );
$sth_msg_in_DB->bind_param( 6, $start_time );
$sth_msg_in_DB->bind_param( 7, $end_time );
$sth_msg_in_DB->bind_param( 8, $temp_min );
$sth_msg_in_DB->bind_param( 9, $temp_max );
$sth_msg_in_DB->bind_param( 10, 0 );
$sth_msg_in_DB->bind_param( 11, 1 );
$sth_msg_in_DB->bind_param( 12, -1 );
$sth_msg_in_DB->execute() || die( "SQL Execute Error!/n" . $DBI::errstr . "/n" );
5.9、避免重复访问：使用group
避免重复访问（一）：同源单组单查询:
如下语句要避免：
SELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’A’ UNION ALL
SELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’B’ UNION ALL
SELECT CLASS,sum(COL) FROM TAB_TEST WHERE CLASS=’C’
改写成：
SELECT CLASS,sum(COL) FROM TAB_TEST GROUP BY CLASS
5.10、避免重复访问：竖向显示变横向现实
避免重复访问（二）：竖向显示变横向显示
问题语句：
SELECTA.C1 AC1,A.C2AC2,A.C3AC3,
B.C1BC1,B.C2BC2,B.C3BC3,
C.C1CC1,C.C2CC2,C.C3CC3
FROM
(SELECT'123' X,'SYNONYM' C1, sum(2)C2,count(1)C3
FROMTAB WHERE TABTYPE = 'SYNONYM')A,
(SELECT'123' X,'TABLE' C1, sum(2)C2,count(1)C3
FROMTAB WHERE TABTYPE = 'TABLE')B,
(SELECT'123' X,'VIEW' C1, sum(2)C2,count(1)C3
FROMTAB WHERE TABTYPE = 'VIEW')C
;
正确使用形式如下：
SELECTMAX(DECODE(TABTYPE,'SYNONYM','SYNONYM',NULL)) AC1,
MAX(DECODE(TABTYPE,'SYNONYM',sum(2),0))AC2,
MAX(DECODE(TABTYPE,'SYNONYM',count(1),0))AC3,
MAX(DECODE(TABTYPE,'TABLE','TABLE',NULL)) BC1,
MAX(DECODE(TABTYPE,'TABLE',sum(2),0))BC2,
MAX(DECODE(TABTYPE,'TABLE',count(1),0))BC3,
MAX(DECODE(TABTYPE,'VIEW','VIEW',NULL)) CC1,
MAX(DECODE(TABTYPE,'VIEW',sum(2),0))CC2,
MAX(DECODE(TABTYPE,'VIEW',count(1),0))CC3
FROMTAB
WHERETABTYPE IN('TABLE','SYNONYM','VIEW')
GROUPBY TABTYPE;
5.11、避免重复访问：用表更新表
避免重复访问（三）：一个表同时更新另一个表的多个字段
问题SQL：使用TB_SOURCE表更新表TB_TARGET的多个字段
UPDATE TB_TARGET A SET
A.COL1 = (select B.COL1 from TB_SOURCE B where B.id = A.id) ,
A.COL2 = (select B.COL2 from TB_SOURCE B where B.id = A.id) ,
A.COL3 = (select B.COL3 from TB_SOURCE B where B.id = A.id) ,
A.COL4 = (select B.COL4 from TB_SOURCE B where B.id = A.id)
WHERE A.id IN ( select B.id from TB_SOURCE B)
正确使用形式如下：
UPDATE TB_TARGET A
SET (COL1, A.COL2, A.COL3, A.COL4 )=(SELECT B.COL1, B.COL2, B.COL3, B.COL4
FROM TB_SOURCE B WHERE B.id = A.id)
WHERE EXISTS (select 1 from TB_SOURCE B where B.id = A.id)
5.12、数据库连接及时关闭
程序中必须显示关闭数据库连接，不仅正常执行完后需显示关闭，而且在异常处理块（例如java的exception段）也要显示关闭。
6、实施规范
6.1、Oracle版本规范
Oracle需要使用稳定版本，例如：9i请使用9.2.0.8或者10g请使用10.2.0.4，不能采用9.2.0.1和10.2.0.1等不稳定版本;
现网环境和入围项目坏境Oracle安装版本需采用64bit的Oracle版本，这是充分发挥Oracle管理能力的需要。
运行Oracle的操作系统，请使用UNIX或者Linux，具体版本请依据Oracle官方要求，但应该和Oracle一样采用64bit系统；
6.2、数据库部署规范
第一：原则上一个数据库实例应该使用一台单独的服务器，即：一个实例一台服务器，而且该服务器上不再部署其他的应用程序；
第二：原则上在线事物系统（OLTP）和在线分析报表系统（OLAP）不能在同一个服务器上运行；
6.3、Oracle内存初始化
Oracle实例所需内存，应该为物理内存减去操作系统本身所需的4G内存以外的内存。安装完毕数据库需要配置Oracle内存，原则上，初次分配时，对于OLTP数据库，SGA为数据库内存的80%，PGA为20%；OLAP数据库SGA为40%，PGA为60%。当然，对于11g，只需要整体设计数据库总体内存就行。
6.4、Oracle字符集选择规范
当数据库中存储的数据有各种语言文字时，比如兴趣点名字需要用各种语言文字存储，则数据库安装时，需选用多字节字符集AL32UTF8；并设置参数NLS_LENGTH_SEMANTICS=CHAR。
6.5、Oracle归档规范
OLTP系统必须采用归档模式运行，且必须执行rman物理备份；OLAP系统，可依据数据安全要求采用适当的安全备份策略，比如只需要进行冷备份和exp/expdp方式备份，并非一定要使用归档模式；
6.6、磁盘结构规范
为了保证在磁盘物理故障发生时的高可用性，磁盘需采用RAID结构，具体的，在繁忙OLTP读写系统中，采用RAID0+1结构，对于报表系统和绝大多数OLTP系统则采用RAID5结构；
6.7、数据文件形式
对于绝大多数情况，I/O不是性能瓶颈，Oracle的数据文件采用文件系统存储即可，而不需要采用裸设备；而对于I/O明显成为系统性能瓶颈的时候，才有必要采用裸设备。
6.8、开发人员的备份规范
做好充分的数据库备份是各级别数据库相关人员的义务和责任，针对开发人员也需要对自己关心的数据进行备份，具体方法如下：
（1）在对个别表进行修改之前，使用CTAS进行备份：
Create Table tb_name_bak As Select * from tb_name [where …...]对修改数据作备份，待所作修改确认无误之后，再将备份表删除；
（2）对个别重要的表，使用exp/expdp进行重点备份：
exp user/passwd@oracle_name tables=(tab1,tab2,…) file=xxx.dmp
log=xxxx.log