Oracle9i 数据库设计指引全集 (2)

2.2 命名

2.2.1 语言

命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。

英文单词使用用对象本身意义相对或相近的单词。选择最简单或最通用的单词。不能使用毫不相干的单词来命名

当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的意义。

当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。

2.2.2 大小写

名称一律大写，以方便不同数据库移植，以及避免程序调用问题。

2.2.3 单词分隔

命名的各单词之间可以使用下划线进行分隔。

2.2.4 保留字

命名不允许使用 SQL 保留字。

2.2.5 命名长度

表名、字段名、视图名长度应限制在 20 个字符内 ( 含前缀 ) 。

2.2.6 字段名称

同一个字段名在一个数据库中只能代表一个意思。比如 telephone 在一个表中代表 “ 电话号码 ” 的意思，在另外一个表中就不能代表 “ 手机号码 ” 的意思。

不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。

2.3 数据类型

2.3.1 字符型

固定长度的字串类型采用 char ，长度不固定的字串类型采用 varchar 。避免在长度不固定的情况下采用 char 类型。如果在数据迁移等出现以上情况，则必须使用 trim() 函数截去字串后的空格。

2.3.2 数字型

数字型字段尽量采用 number 类型。

2.3.3 日期和时间

2.3.3.1 系统时间

由数据库产生的系统时间首选数据库的日期型，如 DATE 类型。

2.3.3.2 外部时间

由数据导入或外部应用程序产生的日期时间类型采用 varchar 类型，数据格式采用： YYYYMMDDHH24MISS 。

2.3.3.3 大字段

如无特别需要，避免使用大字段 (blob ， clob ， long ， text ， image 等 ) 。

2.3.3.4 唯一键

对于数字型唯一键值，尽可能用系列 sequence 产生。

2.4 设计

2.4.1 范式

如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余 , 但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到 3NF. 如非确实必要，避免一个字段中存储多个标志的做法。如 11101 表示 5 个标志的一种取值。这往往是增加复杂度，降低性能的地方。
2.4.2 表设计

2.4.2.1 逻辑段设计原则

2.4.2.1.1 Tablespace

每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较繁忙的数据表，必须存放在该表的专用表空间中。

2.4.2.1.2 Pctused

默认 pctused 导致数据库物理空间利用率非常低 40% 左右；对于 update 比较少或 update 不导致行增大的表， pctused 可设置在 60—85 之间；对于 update 能够导致行增大的表， update 设置在 40—70 之间

2.4.2.1.3 Initrans

对于需要并行查询或者在 RAC 数据库中需要并行处理的表， initrans 设置为 2 的倍数，否则，不设该值。

2.4.2.1.4 Storage

2.4.2.1.4.1 Initial

尽量减少表数据段的 extents 数量， initial 的大小尽量接近数据段的大小 64K ， 128K ， … ， 1M ， 2M ， 4M ， 8M ， 16M ， … ，等按 2 的倍数进行圆整。例如表或分区数据段大小为 28M ，则 initial 取 32M 。

2.4.2.1.4.2 Next

表或分区扩展 extents 的大小，按上述方法进行圆整。当表或分区数据段无法按 Initial 接近值进行圆整的情况下，其大小可以按 Initial+Next 进行圆整。此时，必须设置 Minextents=2 。例如：表或分区数据段大小为 150M ，则 Initial=128M ； Next=32M ， Minextents=2 。

2.4.2.1.4.3 Minextents

该参数表示表创建时候 Extents 的初始数量，一般取 1—2 。

2.4.2.1.4.4 Pctincrease

表示每个扩展 Extents 的增长率，设置 pctincrease=0 能够获得较好的存储性能。

2.4.2.2 特殊表设计原则

2.4.2.2.1 分区表

对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。如果表按某些字段进行增长，则采用按字段值范围进行范围分区；如果表按某个字段的几个关键值进行分布，则采用列表分区；对于静态表，则采用 hash 分区或列表分区；在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。

2.4.2.2.2 聚蔟表

如果某几个静态表关系比较密切，则可以采用聚蔟表的方法。

2.4.2.3 完整性设计原则

2.4.2.3.1 主键约束

关联表的父表要求有主健，主健字段或组合字段必须满足非空属性和唯一性要求。对于数据量比较大的父表，要求指定索引段。

2.4.2.3.2 外键关联

对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量比较大的标要求对外健建立索引。对于有要求级联删除属性的外键，必须指定 on delete cascade 。

2.4.2.3.3 NULL 值

对于字段能否 null ，应该在 sql 建表脚本中明确指明，不应使用缺省。由于 NULL 值在参加任何运算中，结果均为 NULL 。所以在应用程序中必须利用 nvl() 函数把可能为 NULL 值得字段或变量转换为非 NULL 的默认值。例如： NVL （ sale,0 ）。

2.4.2.3.4 Check 条件

对于字段有检查性约束，要求指定 check 规则。

2.4.2.3.5 触发器

触发器是一种特殊的存储过程，通过数据表的 DML 操作而触发执行，起作用是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。

触发器的 before 或 after 事务属性的选择时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。在大型导入表中，尽量避免使用触发器。

2.4.2.4 注释

表、字段等应该有中文名称注释，以及需要说明的内容。

2.4.3 索引设计

对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。在分区表中，尽量采用 local 分区索引以方便分区维护。

除非时分区 local 索引，否则在创建索引段时候必须指定指定索引段的 tablespace 、 storage 属性，具体参考 4.4.2.1 内容。

2.4.4 视图设计

视图是虚拟的数据库表，在使用时要遵循以下原则：

从一个或多个库表中查询部分数据项；

为简化查询，将复杂的检索或字查询通过视图实现；

提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员；

视图中如果嵌套使用视图，级数不得超过 3 级；

由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的库表，不宜使用视图；可以采用实体化视图代替。

除特殊需要，避免类似 Select * from [TableName] 而没有检索条件的视图；

视图中尽量避免出现数据排序的 SQL 语句。

2.4.5 包设计

存储过程、函数、外部游标必须在指定的数据包对象 PACKAGE 中实现。存储过程、函数的建立如同其它语言形式的编程过程，适合采用模块化设计方法；当具体算法改变时，只需要修改需要存储过程即可，不需要修改其它语言的源程序。当和数据库频繁交换数据是通过存储过程可以提高运行速度，由于只有被授权的用户才能执行存储过程，所以存储过程有利于提高系统的安全性。

存储过程、函数必须检索数据库表记录或数据库其他对象，甚至修改（执行 Insert 、 Delete 、 Update 、 Drop 、 Create 等操作）数据库信息。如果某项功能不需要和数据库打交道，则不得通过数据库存储过程或函数的方式实现。在函数中避免采用 DML 或 DDL 语句。

在数据包采用存储过程、函数重载的方法，简化数据包设计，提高代码效率。存储过程、函数必须有相应的出错处理功能。

2.4.6 安全性设计

4.4.6.1 管理默认用户

在生产环境中，必须严格管理 sys 和 system 用户，必须修改其默认密码，禁止用该用户建立数据库应用对象。删除或锁定数据库测试用户 scott 。

2.4.6.2 数据库级用户权限设计

必须按照应用需求，设计不同的用户访问权限。包括应用系统管理用户，普通用户等，按照业务需求建立不同的应用角色。

用户访问另外的用户对象时，应该通过创建同义词对象 synonym 进行访问。

2.4.6.3 角色与权限

确定每个角色对数据库表的操作权限，如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于他所兼角色的权限之和。

2.4.6.4 应用级用户设计

应用级的用户帐号密码不能与数据库相同，防止用户直接操作数据库。用户只能用帐号登陆到应用软件，通过应用软件访问数据库，而没有其它途径操作数据库。

2.4.6.5 用户密码管理

用户帐号的密码必须进行加密处理，确保在任何地方的查询都不会出现密码的明文。

2.5 SQL 编写

2.5.1 字符类型数据

SQL 中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用 trim(),lower() 等函数格式化匹配条件。

2.5.2 复杂 sql

对于非常复杂的 sql( 特别是有多层嵌套，带子句或相关查询的 ) ，应该先考虑是否设计不当引起的。对于一些复杂 SQL 可以考虑使用程序实现。

USER_TAB_COMMENTS 数据字典

Comment on 可加注解

2.5.3 高效性

2.5.3.1 避免 In 子句

使用 In 或 not In 子句时，特别是当子句中有多个值时，且查询数据表数据较多时，速度会明显下降。可以采用连接查询或外连接查询来提高性能。

Char 比 varchar 查询时高询

在进行查询及建立索引时， char 比 varchar 的效率要高，当然 varchar 在存储上比 char 要好

2.5.3.2 避免嵌套的 Select 子句

这个实际上是 In 子句的特例。

2.5.3.3 避免使用 Select * 语句

如果不是必要取出所有数据，不要用 * 来代替，应给出字段列表，注：不含 select count(*) 。

2.5.3.4 避免不必要的排序

不必要的数据排序大大的降低系统性能。

2.5.4 健壮性

2.5.4.1 Insert 语句

使用 Insert 语句一定要给出要插入值的字段列表，这样即使更改了表结构加了字段也不会影响现有系统的运行。

2.5.4.2 Count(*) 、 Count(*) 、 count(distinct id) 的区别

Select count(*) from testtab

得到表 testtab 的记录数

select count(id) from testtab

得到表 testtab id 字段非空记录数

select count(distinct id) from testtab

得到表 testtab id 字段值非相同记录数

2.5.4.3 Not null 为字段类型性质的约束

本约束功能在后期无语法使期失效，可使用修改字段类型方式

alter table modify 字段名 类型 not null

alter table modify 字段名 类型
2.5.4.4 外键值可用 null 的问题

外键列如没有明确说明 not null ，可插入 null 记录（而 null 是在外部表的记录中没有的），如无可插 null 记录的想法，要对外键字段加 not null 约束。

2.5.4.5 序列 sequence 跳号的问题

sequence 因回滚，系统崩溃（使用 cache 内的值将认为已用），多表引用都将使其跳号，所以不能用于为连续序号 utl_row.cast_to_row

2.5.4.6 unicn/ intersect/ minus 使用 ordey by 的注意事项

以上语句进行连表操作，而表同表的字段顺序的类型相同但字段标题名可不同，使用 ordey by 时后面如果是字段名，要求所有的表的字段标题名相同，否则用字段的顺序号

 

　　select id,name,year from user1　　union　　select no,name,to_number(null) year from user2　　order by 1,name,year

 

 

 

 

 

2.5.5 安全性

2.5.5.1 Where 条件

无论在使用 Select, 还是使用破坏力极大的 Update 和 Delete 语句时，一定要检查 Where 条件判断的完整性，不要在运行时出现数据的重大丢失。如果不确定，最好先用 Select 语句带上相同条件来果一下结果集，来检验条件是否正确。

2.5.6 完整性

有依赖关系的表，例如主外键关系表，在删除父表时必须级联删除其子表相应数据，或则按照某种业务规则转移该数据。 9I 中表中字段缩小及变类型，字段为空或表空， varchar 和 char 长度不变可任意改，字段名和表名可字段可用 ALTER TABLE table SET UNUSED (column) 设定为不可用，注意无命令再设为可用

3 备份恢复设计原则

3.1 数据库 exp/imp 备份恢复

Oracle 数据库的 Exp 、 Imp 提供了数据快速的备份和恢复手段，提供了数据库级、用户级和表级的数据备份恢复方式。这种方法一般作为数据库辅助备份手段。

3.1.1 数据库级备份原则

在数据库的数据量比较小，或数据库初始建立的情况下采用。不适合 7*24 的在线生产环境数据库备份。

3.1.2 用户级备份原则

在用户对象表数据容量比较小、或则用户对象初始建立的情况下使用。

3.1.3 表级备份原则

主要在以下场合采用的备份方式：

参数表备份

静态表备份

分区表的分区备份。

3.2 数据库冷备份原则

数据库冷备份必须符合以下原则：

数据库容量比较小。

数据库允许关闭的情况。

3.3 Rman 备份恢复原则

这种方式适用于 7*24 环境下的联机热备份情形。

3.3.1 Catalog 数据库

单独建立备份恢复用的数据库实例，尽可能与生产环境的数据库分开，确保 catalog 与生产数据库的网络连接良好。在 9I 系统使用良好的备份策略以可，支持完全使用控制文件保存 catalog 信息，备份策略如下：

 

　　backup spfile format '/data/backup/%d_SPFILE_%T_%s_%p.bak';　　sql "alter system archive log current";　　backup archivelog all format '/data/backup/%d_ARC_%T_%s_%p.bak' delete all input;　　backup current controlfile format '/data/backup/%d_CTL_%T_%s_%p.bak';　　在spfile、控制文件、数据库全丢的情况下可通过下面的方式恢复　　RMAN> connect target　　connected to target database (not started)　　RMAN> startup　　RMAN> restore spfile from '/data/backup/COMMDB_SPFILE_20030411_9_1.bak';　　SQL> startup　　ORA-00205: error in identifying controlfile, check alert log for more info　　RMAN> restore controlfile from 'd:/DB92_CTL_20031113_9_1.BAK';　　Mout database:　　RMAN> recover database;　　RMAN> alter database open resetlogs;

注意：对数据库设定控制文件保存备份信息为 365 天，具体语句如下。

 

　　alter system set control_file_record_keep_time=365 SCOPE=BOTH;

3.3.2 Archive Log

设置 Archive Log 的位置，确保存储介质有足够的空间来保留指定时间内 archive log 的总量。建设定期对 RMAN 进行全备份，删除冗余归档日志文件。

3.3.3 全备份策略

对于小容量数据库，可以采用全备份策略。对于大容量数据库，必须制定全备份策略方案，备份时对 archive log 进行转储，同时冷备份 catalog 数据库。

3.3.4 增量备份策略

对于大容量数据库，必须制定增量备份、累积备份和全备份的周期，备份时对 archive log 进行转储，同时冷备份 catalog 数据库。

3.3.5 恢复原则

采用 Rman 脚本进行数据库恢复。数据库恢复有以下几种：

3.3.5.1 局部恢复

主要用于恢复表空间、数据文件，一般不影响数据库其他操作。

3.3.5.2 完全恢复

数据库恢复到故障点，由 catalog 当前数据库决定。

3.3.5.3 不完全恢复

恢复到数据库的某一时间点或备份点。

恢复 catalog 数据库。

恢复数据库 control file 。

恢复到数据库某一时间点。

重设日志序列。

3.4 备用数据库原则

数据库系统在以下情况下可以考虑采用备用数据库 data guard 原则：

数据库容量适中。

数据库严格要求 7*24 不间断，或间断时间要求控制在最小范围内。

数据库要求有异地备份冗余。

3.5 一些小经验

使用 oemc 的 oms 时，首选项要求是节点和数据库分别加入系统用户（如： administrator ）和数据库 DBA 用户 (system) 。节点的系统用户必须有批处理作业登录的权限

agent 不能启动， lisnter 修改后都要手动删除 oracle/ora9/network/agent 中的 *.q 文件

oracle/admin/my9i/bdump 中是用户的出错日志

改变表的空间的方式 alter table hr.ssss move TABLESPACE example （要重建索引） ; 或用 imp 导入时，设定导入用户只有某一表空间的使用权，无 RESOURCE 角色和 UNLIMITED TABLESPACE 权限

aleter system set log_checkpoint_to_alter=true ，后可报警文件发现 checkpoint 的起动和结束时间。