ORACLE设计规范

1、ORACLE 设计规范1、数据库模型设计方法规范1.1、数据建模原则性规范原则对于涉及数据库的项目，需要构建数据库逻辑模型图，逻辑 模型图是项目组成员之间在数据库层面沟通交互的依据，必 须规范画图（表，主键，外键，关系）衡量对于表的个数在 20 个以上的模型，需要数据组参与设计， 并 需 DBA 作最终审核方法对于 OLTP 系统，采用范式化思想进行模型设计， 对于 OLAP 系统，采用面向问题及多级颗粒度的思想进行模型设计实施采用主流的模型设计 软件工具 PowerDesigner，ERStudio， ERWin1.2、实体型之间关系认定规范原则所有实体型间的业务逻辑关系，除了语义上保留其原

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 关系

3、；实施 （1） 1:1 关系选取任何一个表的主键到另一个表中作为外键 来体现；（2）1：N 关系将 1表的主键在 N 表中以外键形式存在来体 现；（ 2）N:M 关系采用 “关系表 ”来体现， 该关系表的主键是由相 关实体表的主键组成的符合主键，各实体表主键不但组成了 该关系表的主键，同时也被看作外键在该关系表中存在；（4）对于三个以上表之间的 “多元关系 ”常需要和反范式化冗 余字段结合起来设计，以保证查询速度；1.3 、范式化 1NF 的规范原则OLTP 系统的模型，需要符合第三范式衡量对于表在 20 个以上的模型，需要数据组参与设计方法范式化要求：INF ：列是访问的最小单位，具有原子性

4、，不可再被分割；实施 依据具体情况对相应属性进行拆分或者合并。范式化 1NF 常见现象： 现象一：同一个属性值的不同细度把握，比如，常见的“姓 名”这个属性，设计一：“姓名”是一个列，设计二：“姓” 是一个列，“名”是一个列，两个列的值组合起来才表达一 个“姓名”语义。两种设计方法，在不同的系统中都有应用， 这主要是依据需求的细度来确定，灵活把握； 现象二：把多个属性值错误的作为一个属性值存储，比如： 常见的 OA 系统要存储员工的各种属性，包括技能信息，技 能范围： Oracle， JAVA，.NET,C#,Perl,UNIX 等等，一种常见 的错误设计是：设计一张员工表，其中有一个技能属性

5、字段， 然后某员工所掌握的多种技能用逗号（，）间隔，然后将这 个字符串存储到这个员工表的技能属性字段中。这里的错误 在于将多个属性值作为一个属性值存储在一个字段中，不能 满足直接遍历员工对某个技能掌握情况，而且如果再要求说 明员工对个技能的掌握程度（精通，熟悉，一般等等），则 再增加字段，里面的对应关系将很容易错乱，这是严重违反 1NF 的情况。正确的设计应该是：两个实体表：一张是员工 表，一张是技能字典表，一个员工可以掌握多个技能，也就 是（1:N）关系，相反一个技能可以被多个员工掌握， 也是（ 1:N） 关系，双向都是（ 1:N）关系，那么综合起来员工和技能之间 就是“多对多关系（ N:M

6、 ）”，依据前述规范，应该设计一张“关系表”来存储“多对多关系”，主键为复合主键（员 工主键 + 技能主键），该关系有一个属性“技能掌握程度”。1.4 、范式化 2NF 的规范原则 OLTP 系统的模型，需要符合第三范式衡量 对于表在 20 个以上的模型，需要数据组参与设计方法 范式化要求：2NF ：满足 1NF,不存在非主键属性对主键属性的部分依赖； 实施 范式化 2NF 常见现象：实体表中一般不会出现违反 2NF 的情况，因为都是“一个” 主键列，而关系表是两个以上列的“复合”主键，故而关系 表容易出现违反 2NF 的情况。主要是该关系表非主键外的属 性，本该属于相关的某个实体表的，却放到

7、了该关系表中， 这使得该属性不能通过该关系表的复合主键唯一确定， DML 操作会发生错误；如果违反了 2NF，那么应该把这个属性从 关系表中拆分，也许会单独形成一个表，绝大部分情况下是 将该属性归并到某个相关的实体表中； 违反 2NF 的例子：学生考试情况中，有两个实体表：学生表 和学科表，学生与学科之间的考试关系就是 N:M 的关系，就 要创建一张关系表存储该多对多的考试关系，表的主键为学 生编号和学科编号，属性为考试分数；那么“任课老师”该 放在那里呢？如果放到考试关系表中，那么安排任课老师， 必须先进行考试，这显然不符合实际，也就是任课老师不该 依赖于学生编号和学科编号，只是依赖于学科编

8、号，也就是 说任课教师信息应该放在学科表中；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

9、）的依赖，传递依赖于 A（Akey）；三种 关系（ 1:1,1:N，N:M ）都含有外键，都很可能发生违反 3NF 的情况。违反 3NF 的后果：会导致那些问题属性插入异常， 或者被误删。违反 3NF 的例子： 教师和学科之间，存在着上课关系，假设一个教师上一门课 而且一门课只有一个教师上，那么该关系为 1:1 关系，将教 师表的主键教师编号在学科表中以外键形式存在就表达了该1:1关系，那么教师的 “联系电话 ”属性该放哪里呢？如果看到 “教师编号 ”出现在了学科表中， 就将联系电话放入学科表中， 那么联系电话首先是对表中的教师编号依赖，再依据教师编 号对学科的依赖，达到了学科编号的依赖，那么

10、联系电话对 学科编号的依赖就是传递依赖，违反了 3NF，应该将其从学 科表中拆出来放入教师表中，不然的话，会发生操作异常， 比如，假设一个教师已经存在但是还没有为其分配科目，那 么他的电话就无法存入库中。1.6、反范式化冗余字段使用规范原则 OLTP 系统中在完成范式化工作之后，对某些表，可以适当 反范式化增加冗余字段以提高数据访问性能；在 OLAP 中采 用的是面向问题的设计思想，应该大量使用反范式化冗余信 息衡量 当 SQL 关连查询涉及到 4 张表时可考虑采用冗余字段方法 常用在两个地方： （ 1）关系表中的冗余：在关系表中增加相 关实体表的相关属性，以达到关连查询时减少表的关联数量 的

11、目的（ 2）层次关系中的冗余：在多层次的子父表关系中， 将父表的属性存储在 “子表”或者“孙子表 ”或者“重孙表”中； 反范式化冗余字段实例： （1）关系表中的冗余：比如在考试关系中，原本在学科表中 的学分信息，可以冗余添加到考试关系表中，这样，每个学 生得了多少学分，就可以直接从考试表得到，而无需关联学 科表来得到；（2）多层关系中的冗余：假设为之范畴从大到小有国家表， 省份表，城市表，城区表，社区表，它们之间的层次关系是 通过上一级的主键在下一级中以外键形式存在来体现的，但 是，如果需要问：某个设计属于哪个国家？这样就要关连查 询所有的 5 张表，性能会很差，这时可以将国家编号以外键 形式

12、放入到社区表中做冗余，这样直接关联国家表和社区表 即可得到答案。一般的，每间隔一级增加一个冗余外键，比 如将国家编号放入城市表中，将城市编号放入社区表中 实施 如何保证冗余字段数据的正确性（一致性）是反范式化的关键，需要对冗余字段详细添加注释，说明冗余了什么，以及 该字段的维护方法，常用维护方法如下： （1）如果在程序开发前设计的冗余字段， 可以在正常的业务 逻辑程序中一并处理 （ 2）如果是程序完成之后增加的冗余字 段，可以使用触发器维护 （3）对于 OLAP 中大量存在冗余字 段，可能需要使用单独的处理任务进行维护1.7、数据库对象命名基本规范第一：长度规范： 凡是需要命名的对象其标识符均

13、不能超过 30 个 字符，也即： Oracle 中的表名、字段名，函数名，过程名，触发 器名，序列名，视图名的长度均不能超过 30 个字符；第二：构成规范：数据库各种名称必须以字母开头，但严禁使用 SYS开头；名称只能含有字母， 数字和下划线 “_”三类字符，“ _ 用于间隔名称中的各语义字段；不要使用 DUAL 作表名； 第三：大小写规范： 构成 Oracle 数据库中的各种名称（表明，字 段名，过程名，视图名等等）的所有字符，必须使用大写，也就 是不能在脚本中，对任何名称添加双引号“”来设定字符的大小 写形式，只要不采用“”限制， Oracle 自动会将各名称转化成大 写。2、表的设计规范

14、2.1、表的主键规范遵循如下三点原则： 第一：有无原则： 除临时表和外部表， 以及流水表， 日志表外，其他表都要建立主键；第二：构成原则：主键不能使用含有 实际语义的列，应该增加一个 xx_id 字段做主键，类型为 number，取值 来自序列 sequence；第三：创建原则：对于 500 万以上的表，请数据组 参与设计实施，采用先建唯一索引再添加主键约束的方式来创建主键；2.2、表的主键列规范对于实体表，主键就是一列，就是没有任何语义的自增的 NUMBER 列， 对于关系表，主键就是相关实体表主键形成的复合主键，是多列；2.3、使用注释的规范原则每个表，每个字段都要有注释，说明其含义，对于

15、冗余字段还要特别说明其维护方法，外键字段说明参照与那个表衡量原则上谁设计谁注释方法查询字典表 user_tab_comments和 user_col_comments可知道表 和字段的注释信息实施对表添加注释：SQLcomment on table is xx;对字段添加注释：SQLcomment on column. is xx;2.4、一个表所含字段总长度的规范原则一个表中的所有字段， 应当能存储在一个数据块中 （BLOCK ），也即：表的单行字段总长度 db block（ 减去 pctfree）衡量对不含有大对象数据类型字段的表，字段数大于 50 个的，请数据组参与设计方法查询字典表 U

16、SER_TAB_COLUMNS 中的字段DATA_LENGTH 得到表中所有字段的总长度，再依据 db_block 和表的 pctfree参数可以判断是否一个数据行可以存储 在一个数据块 （BLOCK） 中实施如果所有字段的总长度超出了一个数据块， 那么需要将该表拆分成两个(甚至多个) 表，拆分的依据是字段的频繁使用程度， 也就是频繁使用的字段在一个表中， 很少被使用的字段放在另 一个表中，他们之间使用相同的主键值，用主外键关联。这点 就是“一个表所含字段访问频繁度的规范”2.5、一个表所含字段访问频繁度的规范原则一个表中的各字段的访问频繁度应该基本一致衡量如果一个表的字段数超过 50 个，

17、请数据组参与审核方法如果一个表的字段数过多超过 50 个，并且依据业务逻辑确定 该表中一些字段频繁被访问， 另一些字段则很少被访问， 则该 表需要做拆分处理，这在 OLAP 系统中比较常见； 目的：这样可以避免读取频繁信息时多读取很少被访问的信 息，可以提高 IO 性能，减少内存耗费；实施将访问频繁度相差太远的字段拆分到两个表中， 一个表存频繁 访问的字段，另一个表存很少被访问的字段 ;2.6、一个表所含数据量的规范原则一个非分区表中的数据量不要超过 500 万衡量一个非分区表中的数据量超过 500万，请数据组参与设计成分 区表，如果该表数据量超过 5000万，请 DBA 参与设计方法在系统上

18、线前，通过对业务分析，判断一个表的数据量；在系 统上线后， 可以通过 exp的日志， Top 性能 SQL，count(1)来发现数据量大的表实施 将这些表进行分区，具体方法请参看分区表的设计规范2.7、大对象字段（ BLOB ，CLOB）使用规范原则 存储图片，视频，音频，文件， 500 字节以上文本等占用太多 空间的字段（大对象字段），不能和其他字段存储在一个表中衡量 含有大对象 （BLOB，CLOB）字段的表设计和存储请数据组参 与设计方法 方法一： 数据库存储， 可以重新建一个表专门存储该大对象字 段，该表基本为两个字段，一个为大对象编号 ID 为主键，一 个为大对象内容本身， 并将该

19、主键在原表中作外键关联， 该大 对象表存储在单独的表空间中；方法二：操作系统存储，将这 些文件存储在操作系统空间中， 大对象字段存储该文件的全路 径名 比较：如果该大对象字段常被修改，那么采用方法一； 如果该大对象信息为静态， 加载后基本不变， 那么可以采用方 法二，它有一个致命缺点就是信息存储在数据库外部， 不安全， 容易丢失。2.8、增量同步表的设计规范字典信息表和需要使用增量同步的表必须增加如下属性：属性名类型取值说明StatusChar(1)Y/N ：Y 为激活N 为作废，默认为Y标识该行是否使用。 用于软删除， 软删除 需将主键和唯一约 束列添加随机数后 缀。Create_timeD

20、ate默认为 sysdate创建时间Update timeDate默认为 sysdate最后修改时间2.9、表的表空间使用规范原则依据表的 DML 频度而使用不同的表空间衡量方法表空间的规划由建库人员完成为了减少空间碎片问题，（ 1）将很少被 DML （增删改）的静 态表，放在一组表空间中；（ 2）将只发生 INSERT 的表放到 一组表空间中， （3）而将常发生两种以上 DML 操作的动态表 放在另一组表空间中，这三组表空间不能相交实施在上线前， 依据需求分析确定动态表和静态表， 将他们做最分 离；上线运行之后， 依据数据库性能分析得到的信息来区分动 态表和静态表2.10 、索引的表空间使用

21、规范原则表和索引原则上应该使用不同的表空间存储，并且不同 DML频度的表的索引，放在不同的表空间中衡量表空间的规划有数据组或者 DBA 实施方法绝大多数情况下， 动态表的索引必然是动态的， 静态表的索引必然是静态的，依据对表的分析来确定索引的动静性实施将动态表的索引放在一组表空间中， 静态表的索引放在另一个表空间中，两组不相交。而且和表所在的表空间也不相交。3、设计分区表的规范3.1 、 RANGE 分区的规范原则 大数据量的表需进行分区化衡量 当表的数据量超过 500 万， 请数据组参与设计成分区表，当 表的数据量超过 5000万，请 DBA 参与设计方法 SQL 常依据某列的范围访问表，则

22、对表使用 RNAGE 分区， 常见情况是 SQL 根据时间范围进行查新，则使用时间字段作 为分区关键字进行 RANGE 分区； 将对表的多种访问结合考虑来确定分区的细度：1. 大多数 SQL 操作的分区关键字值的范围；2. 数据维护的需要，比如以月为单位删除历史数据；3. 数据访问的性能，以操作范围确定的分区数据量还是过大，比如大于 500 万，则还需要进行细分；4. 一个分区的数据量要小于 500 万，这是一个硬性的尺度， 但 从技术上来看，每个分区 10 万数据量的情况比每个分区 20 万数据量的情况要快很多，所以需要灵活掌握；实施 1.当各个分区中的数据能均等划分时性能最好，如果相差太

23、大，则考虑采用其它分区，或者将大数据量的分区再进行 HASH 子分区；2. 各分区采用各自的表空间存储， 使用 user_tab_partitions字典 来查看确定每个分区的表空间位置；3. 分区表的索引采用本地索引， 因为常会根据分区关键字 （比 如时间）进行分区维护（比如删除 1 年前的数据，也就是删 除 1 年前的分区），分区维护时全局索引会失效，而本地索 引不会失效，这能保证访问表时索引正常可用；3.2 、 LIST 分区的规范原则大数据量的表需进行分区化衡量当表的数据量超过 500 万， 请数据组参与设计成分区表，当 表的数据量超过 5000万，请 DBA 参与设计方法SQL常居于

24、某列的散列值访问表， 则对表使用 LIST 分区，LIST分区不支持多列分区关键字；常见情况针对某个地区或者某个业务进行数据访问，那么就使用地区编号或者业务编号作为分区关键字； 将对表的多种访问结合考虑来确定分区的细度：1. 一般使用一个分区关键字的值来划定一个分区；2. 可以把分区关键字的值相对应数据比较少的几个分区合并 作一个分区；3. 如果一个分区关键字值所对应的数据量过大，比如大于500万，则应该对表采用 RANGE 分区，对该值的分区再采用 HASH 子分区；也就是说，一个可以采用 LIST 分区的表，肯 定可以转化成 RANGE 分区（可带子分区），反之不然；4. 一个分区的数据量

25、要小于 500 万，这是一个硬性的尺度， 但 从技术上来看， 每个分区 10万数据量分区方法比每个分区 20 万数据量的分区方法要快很多，所以需要灵活掌握；实施 1.各分区采用各自的表空间存储， 使用 user_tab_partitions字典 来确定每个分区的表空间；2.分区表的索引采用本地索引3.3 、 HASH 分区的规范原则大数据量的表需进行分区化衡量当表的数据量超过 500 万， 请数据组参与设计成分区表，当 表的数据量超过 5000万，请 DBA 参与设计方法SQL 访问表不按照某列的范围进行， 也不按某列离散值进行，而且对该表的数据不会依据某列的值范围或者离散值进行定 期维护，那

26、么使用 HASH 分区； HASH 分区是不知道应该选 择何种分区时的选择； HASH 分区的各分区都可能存有各种 情况的数据，故而不能用于依据分区清理数据的情况； 对确定分区细度的考虑： 1.依据分区的数据量规划和表的最大数据量来确定分区数； 2.一个分区的数据量要小于 500 万，这是一个硬性的尺度， 但 从技术上来看， 每个分区 10万数据量分区方法比每个分区 20 万数据量的分区方法要快很多，所以需要灵活掌握；实施 1.各分区采用各自的表空间存储， 使用 user_tab_partitions字典 来确定每个分区的表空间；2.对于 HASH 分区表，大多数情况下依然要求采用本地索引，

27、但是如果分区过细，也可以采用全局索引，因为根据 HASH 分区表的特征（各分区无业务区分，都有数据），该表很少 会发生分区维护的工作；3.4 、 RANGE-LIST 分区的规范 原则 大数据量的表需进行分区化衡量 当表的数据量超过 500 万， 请数据组参与设计成分区表，当 表的数据量超过 5000万，请 DBA 参与设计方法 SQL 访问表时，既依据某列值的范围，又依据其他列的离散 值或者范围，这种情况下采用 RANGE-LIST 复合分区，常用 于语表中的数据需要依据一个时间字段做周期性删除等维 护，并且正常业务 SQL 访问既依据时间字段，又依据其他字 段的散列值进行访问的情况； 比如

28、：电信增值业务计费表，既有时间又有业务属性列，统 计的时候，会选择时间范围和业务属性，所以可以以时间列 为分区关键字建立 RANGE 分区，以业务属性列为关键字建 立 LIST 子分区； 分区划分的方法： 1.就按照大多数范围访问的范围值来划定 RANGE 分区的范 围，依据单个 LIST 子分区关键字的值来划分子分区 ;2.如果 LIST 子分区中数据量较小而且又常被一起访问的子分 区可以合并成一个子分区；3.如果 LIST 子分区中一个子分区关键字值对应的子分区数据 量还是很大， 超过 500，影响性能，那么可以通过细分 RANGE 分区来达到减少 LIST 子分区数据量的目的，这点和 L

29、IST 分 区在该情况下的处理方法（转化成 RANGE-HASH ）不同； 实施 1.各子分区应该尽量分散到不同的表空间中存储，使用 user_tab_subpartitions字典来确定每个子分区的表空间； 2.RANGE-LIST 大多数情况采用本地索引，因为常根据 RANGE 分区关键字的来进行分区维护；3.5 、 RANGE-HASH 分区的规范 原则 大数据量的表需进行分区化衡量 当表的数据量超过 500 万， 请数据组参与设计成分区表，当 表的数据量超过 5000万，请 DBA 参与设计方法 SQL 访问表时，主要依据某个列的范围进行访问，即访问特 征符合 RANGE 分区的要求，

30、或者数据维护特征符合 RANGE 分区的要求，但是以 SQL 或者维护的数据范围来划定分区， 分区数据量又很大，对性能有影响，需再进行子分区，由于 分区中的数据都会被访问到，所以子分区采用 HASH 方法， 整个表就是 RANGE-HASH 分区； 划定分区的方法： 先按照大多数范围访问的范围值来划定 RANGE 分区的范围， 再依据性能情况来确定 HASH 子分区的数据量 ;实施 1.各子分区应该尽量分散到不同的表空间中存储，使用 user_tab_subpartitions字典来确定每个子分区的表空间； 2.RANGE-HASH 大多数情况采用本地索引，因为常根据 RANGE 分区关键字的

31、来进行分区维护；4、索引的设计规范4.1、主键索引的规范原则 对数据量表应该先在主键列建唯一索引，再建主键约束衡量分区表的主键必须采用该方法方法主键上隐含索引， drop 或 disable 主键时，索引会丢失，为保证性能不变，为了对主键约束和相应索引有更多的控制，对大表(分区表 )的索引采用如下方式建立：(1) 在准备建主键的列上建立唯一索引( UNIQUE INDEX ) :CREATE UNIQUE INDEX Index_Name ONTable_Name(Column_Name) TABLESPACE TBS_INDEX;(2) 再加上主键约束：ALTER TABLE Table_N

32、ame ADD (PRIMARY KEY(Column_Name) USING INDEX TABLESPACE TBS_INDEX );实施Oracle 会在指定的列上加上主键约束，并且使用该索引分区表的主键默认索引是全局索引，所以主键索引的分区方法：先建立分区化的唯一索引，再建主键约束，4.2、唯一约束索引的规范原则针对大数据量表应该先在唯一约束列上建立普通索引，再添加唯一性约束衡量分区表的唯一约束必须采用该方法方法 删除或禁用唯一性约束通常同时使相关联的唯一索引失效， 因而降低了数据库性能。 要避免这样问题， 可以采取下面的步骤：a)在唯一性约束的列上创建非唯一性索引(普通索引)；b)添

33、加唯一性约束；4.3、外键列索引的规范原则无论表的大小，外键列都要建立索引原因其一是为了子父表关联查询的性能考虑， 其二是为了避免父子 表修改而发生死锁实施普通表的外键列建立普通索引即可， 如果表是分区表， 则依据表的情况建立本地索引或者全局索引4.4、复合索引的规范原则复合索引只有在该种复合常被和该表相关的大多数 SQL 使用 时才建立衡量复合索引的列数不能超过 3 个，否则该索引很少会被使用实施(1)复合索引的第一列，可以通过不使用该种复合的SQL 来确定。假设一些 SQL的 WHERE 中复合使用列为 ABC，而其他 一些 SQL的 WHERE 中常使用的是 C 列，那么该复合索引可 以

34、按照 CAB 的顺序建立，这样上述两种 SQL 都能使用该索引； (2)对于不能把握好的复合索引， 请在选择性大的列上分别建立 单列索引；(3) 切忌不能将表相关的所有 SQL中 WHERE 涉及到的列复合起来建立复合索引；4.5、函数索引的规范原则由于使用形式需和创建形式一致，尽量避免使用函数索引衡量如果想要使用函数索引，请尽量进行转化方法由于函数索引在使用时，使用形式必须和创建形式一致，故应 该尽量避免使用函数索引，尽量采用如下方法转化 SQL 以避 免函数索引的使用：原本在 WHERE 中列上添加函数的， 取函数的反意义函数添加 到“ =另”一侧的常数项上，这样只需要在列上建立普通索引即

35、 可，比如常见的日期转化函数：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 操作的表，不能建立位图索引，请建立普通的索引即可， 否则该表的相关操作很容易造成锁等待， 使系统性能大受影响；其次，索引列需要低基数，只有几个数值，比 如性别列（男，女，

36、保密）和学历列（大专，本科，研究生， 博士生）4.7、反向索引的规范原则列值顺序增加的列，其上的 WHERE 运算是 或者=而不是范 围（ between and或者 ）检索时，可以采用反向函数衡量一般创建反向索引的列为 NUMBER 类型，值由 SEQUENCE 生成方法实施4.8、分区索引的规范原则对分区表的索引，需要做分区维护的，必须使用局部索引衡量一般情况下， HASH 分区表可以采用全局索引，其他分区，包 括 RANGE-HASH 也应该采用本地索引， 主要是由于 HASH 分 区表不常进行分区维护；方法实施4.9、索引重建的规范原则重建索引使用 ALTER INDEX REBUIL

37、D 方式，禁止采用DROP INDEX & CREATE INDEX方式；衡量分区表等大数据量表的索引必须采用REBUILD 方式重建ALTER INDEX方法ALTER INDEX IDX_NAME REBUILD TABLESPACE TBSP NAME实施可以在现网上直接实施5、SQL 访问规范5.1、避免 SELECT *程序中不能出现 SELECT* ，即使是选择全部选择项，也需要全部 指明，这主要出于如下原因： 第一：使用*相对比较慢， 因为 Oracle 需要遍历更多的内部字典信息； 第二：为避免以后相关表增加字段 造成程序错误，比如 INSERT INTO SELECT 和 S

38、ELECT INTO 语 句会报错；5.2 、避免笛卡尔运算 多表关联查询不能出现笛卡尔积， 如果在报表中为集聚表 (或称中 间表) 生成多个维度组成的复合主键需要使用迪克尔积的， 必须请 数据组确认性能。5.3、使用 CTAS 备份在进行 DML 操作( INSERT，UPDATE ，DELETE )之前，必须对 数据进行备份，使用如下语句：方法一：表数据全部备份：CREATETABLE TAB_NAME_BAK AS SELECT * FROM TAB_NAME;方法二：部分备份：对大表仅备份将要修改的数据：CREATE TABLE TAB_NAME_BAKAS SELECT * FROM

39、 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 、大数据量的 DMLDML 操作涉及到大数据量时，请分解为多次执行；对于 UPDATE 和

40、DELETE 每次涉及数据量在 1 万条左右，并且每 次执行完就提交；对于 INSERT INTO SELECT 如果采用提示 (/*+ append parallel */) 可以处理百万级别的数据量。5.6 、完成事务及时 commit对于一个完成了的事务，请用 commit 显示提交，这是避免锁争用的锁 等待的需要，特别是对 DML 操作频繁的表；5.7、java的变量绑定使用“变量绑定”来处理一条 SQL 带不同常量多次执行的情况， 动态绑定可以大大优化 SQL 的执行效率，还可以优化 Oracle 的内 存使用。在 Java中，结合使用 setXXX 系列方法，可以为不同数据类型的

41、绑定变量进行赋值，从而大大优化了 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

42、 绑定变量实例如下：$modsql = qqinsert 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 );

43、$sth_msg_in_DB = $dbh-prepare($modsql)| die( start SQL prepare Error!/n . $DBI:errstr . /n );$kpiid_tab$kpi_id ); $kpi_id );-1 );-1 );$current_time ); $start_time );$end_time ); $temp_min );$temp_max );$sth_msg_in_DB-bind_param( 1, $sth_msg_in_DB-bind_param( 2, $sth_msg_in_DB-bind_param( 3, $sth_msg

44、_in_DB-bind_param( 4, $sth_msg_in_DB-bind_param( 5, $sth_msg_in_DB-bind_param( 6, $sth_msg_in_DB-bind_param( 7, $sth_msg_in_DB-bind_param( 8, $sth_msg_in_DB-bind_param( 9, $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

45、() | die( SQL Execute Error!/n . $DBI:errstr . /n );5.9 、避免重复访问：使用 group避免重复访问（一）：同源单组单查询如下语句要避免：WHERE CLASS= A UNION ALLWHERE CLASS= B UNION ALLWHERE CLASS= CSELECT CLASS,sum(COL) FROM TAB_TEST SELECT CLASS,sum(COL) FROM TAB_TEST SELECT CLASS,sum(COL) FROM TAB_TEST 改写成：SELECT CLASS,sum(COL) FROM TA

46、B_TEST GROUP BY CLASS5.10 、避免重复访问：竖向显示变横向现实避免重复访问（二）：竖向显示变横向显示问题语句：SELECTA .C1 AC1 ,A .C2AC2 ,A.C3AC3 ,B.C1BC1,B.C2BC2,B.C3BC3,C.C1CC1,C.C2CC2,C.C3CC3FROM(SELECT 123 X,SYNONYM C1, sum(2)C2,count (1)C3FROMTAB WHERE TABTYPE = SYNONYM )A,(SELECT 123 X,TABLE C1, sum(2)C2,count(1)C3FROMTAB WHERE TABTYPE

47、= TABLE )B,(SELECT 123 X,VIEW C1, sum(2)C2,count(1)C3FROMTAB 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(TABT

48、YPE ,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)CC3FROMTABWHERETABTYPEIN (TABLE ,SYNONYM ,VIEW )GROUPBY TABTYPE ;5.11 、避免重复访问：用表更新表避免重复访问（三）：一个表同时更新另一个表的多个字段 问题 SQL：使用 TB_SOURCE 表更新表 TB_TARGET 的多个字段 UPDATE TB_TARGET A SETA.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.