oracle 9i 数据库设计指引全集.docx
《oracle 9i 数据库设计指引全集.docx》由会员分享，可在线阅读，更多相关《oracle 9i 数据库设计指引全集.docx（14页珍藏版）》请在冰豆网上搜索。
oracle9i数据库设计指引全集
Oracle9i数据库设计指引全集
一、数据库物理设计原则
1.1数据库环境配置原则
1.1.1操作系统环境：
对于中小型数据库系统，采用linux操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统，可以采用oracle9irac的集群数据库的方法，集群节点数范围在2—64个。
对于大型数据库系统，可以采用sunsolarissparc64位小型机系统或hp9000系列小型机系统。
rad5适合只读操作的数据库，rad1适合oltp数据库
1.1.2内存要求
对于linux操作系统下的数据库,由于在正常情况下oracle对sga的管理能力不超过1.7g。
所以总的物理内存在4g以下。
sga的大小为物理内存的50%—75%。
对于64位的小型系统，oracle数据库对sga的管理超过2g的限制，sga设计在一个合适的范围内：
物理内存的50%—70%，当sga过大的时候会导致内存分页，影响系统性能。
1.1.3交换区设计
当物理内存在2g以下的情况下，交换分区swap为物理内存的3倍，当物理内存>2g的情况下，swap大小为物理内存的1—2倍。
1.1.4其他环境变量参考oracle相关的安装文档和随机文档。
1.2数据库设计原则
1.2.1数据库sid
数据库sid是唯一标志数据库的符号，命名长度不能超过5个字符。
对于单节点数据库，以字符开头的5个长度以内字串作为sid的命名。
对于集群数据库，当命名sid后，各节点sid自动命名为sidnn，其中nn为节点号：
1，2，…,64。
例如rac1、rac2、rac24。
1.2.2数据库全局名
数据库全局名称：
.domain
1.2.3数据库类型选择
对于海量数据库系统，采用datawarehouse的类型。
对于小型数据库或oltp类型的数据库，采用transactionprocessing类型。
1.2.4数据库连接类型选择
oracle数据库有专用服务器连接类型和多线程服务器mts连接类型。
对于批处理服务，需要专用服务器连接方式，而对于oltp服务则mts的连接方式比较合适。
由于采用mts后，可以通过配置网络服务实现某些特定批处理服务采用专用服务器连接方式，所以数据库设计时一般采用mts类型。
1.2.5数据库sga配置
数据库sga可以采用手工配置或按物理内存比例配置，在数据库初始设计阶段采用按比例配置方式，在实际应用中按系统调优方式修改sga。
1.2.6数据库字符集选择
为了使数据库能够正确支持多国语言，必须配置合适的数据库字符集，采用utf8字符集。
注意：
如果没有大对象，在使用过程中进行语言转换没有什么影响，具体过程如下（切记设定的字符集必须是oracle支持，不然不能start）：
sql>shutdownimmediate;
sql>startupmount;
sql>altersystemenablerestrictedsession;
sql>altersystemsetjob_queue_processes=0;
sql>alterdatabaseopen;
sql>alterdatabasecharactersetinternal_usewe8iso8859p1;
sql>shutdownimmediate;
sql>startup
1.2.7数据库其他参数配置
1.2.7.1db_files
db_files是数据库能够同时打开的文件数量，默认值是200个。
当数据库规划时文件数量files接近或超过200个时候，按以下估计值配置:
db_files=files*1.5
1.2.7.2db_block_size
一个extent要是5个blocks的倍数为好，如：
一个blocks是4096字节，那一个extent就是2m、4m或8m为好。
db_block_size是数据库最小物理单元，一旦数据库创建完成，该参数无法修改，db_block_size按以下规则调整：
数据仓库类型：
db_block_size尽可能大，采用8192或16384
oltp类型：
db_block_size用比较小的取值范围：
2048或4096
blocks推荐是系统操作的块倍数（裸设备块大小是512字节，ntfs是4k，使用8k的方式在大部分系统上通用）。
1.2.8数据库控制文件配置
1.2.8.1控制文件镜象
多个控制文件存放在不同的物理位置。
1.2.8.2控制文件配置
控制文件中参数设置，最大的数据文件数量不能小于数据库参数db_files。
1.2.9数据库日志文件配置
1.2.9.1日志文件大小
日志文件的大小由数据库事务处理量决定，在设计过程中，确保每20分钟切换一个日志文件。
所以对于批处理系统，日志文件大小为几百m到几g的大小。
对于oltp系统，日志文件大小为几百m以内。
1.2.9.2日志文件组数量
对于批处理系统，日志文件组为5—10组；对于oltp系统，日志文件组为3—5组，每组日志大小保持一致；对于集群数据库系统，每节点有各自独立的日志组。
1.2.9.3日志成员数量
为了确保日志能够镜象作用，每日志组的成员为2个。
1.2.10数据库回滚段配置
在oracle9i数据库中，设计undo表空间取代以前版本的回滚段表空间。
undo表空间大小的设计规范由以下公式计算：
undospace=ur*ups*db_block_size+冗余量
ur：
表示在undo中保持的最长时间数（秒），由数据库参数undo_retention值决定。
ups：
表示在undo中，每秒产生的数据库块数量。
例如：
在数据库中保留2小时的回退数据，假定每小时产生200个数据库块。
则undospace=2*3600*200*4k=5.8g
1.2.11数据库临时段表空间配置
数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。
1.2.12数据库系统表空间配置
系统表空间大小1g左右，除了存放数据库数据字典的数据外，其他数据不得存储在系统表空间。
1.3数据库表空间设计原则
1.3.1表空间大小定义原则
当表空间大小小于操作系统对最大文件限制时，表空间由一个文件组成。
如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：
tablespace+sum（数据段+索引段）*150%。
1.3.2表空间扩展性设计原则
表空间数据文件采用自动扩展的方式，扩展容量快大小按2的整数倍（1m、2m、4m、8m、16m、32m、64m）进行扩展，创建表空间时尽量采用nologing选项。
表空间的最大限制一般采用unlimited，除非确切知道表空间数据文件的最大使用范围。
（一般windows３２位系统的文件最大２ｇ，６４位的unix系统系统文件最大１２８g,但也要注意文件格式设定的文件大小）,建议最大为2g。
表空间采用local管理方式，例如：
createtablespace tbs_userinfo
datafile
'/oradata/tbs_userinfo.dbf'
size8m
reuse
autoextendon
next2m
maxsizeunlimited
nologging
extentmanagement
local
autoallocate
segmentspacemanagementauto;
1.4裸设备的使用
一个scsi设备可以14个分区，unix操作系统256个分区,性能比文件系统方式高15％左右，空间大于要小于（实际分区大小减两个oracle的数据块），比如100m，大于为100000k，推荐在unix使用软连接（ln）方式把裸设备形成文件，用加入表空间时加resue选项，当然也可只接把设备加入表空间，移动裸设备使用dd命令
对于windows平台，oracle提供软连接工具，实现裸设备的使用，计算一条记录的长度
二、数据库逻辑设计原则
2.1命名规范
2.1.1表属性规范
2.1.1.1表名
前缀为tbl_。
数据表名称必须以有特征含义的单词或缩写组成，中间可以用“_”分割，例如：
tbl_pstn_detail。
表名称不能用双引号包含。
2.1.1.2表分区名
前缀为p。
分区名必须有特定含义的单词或字串。
例如：
tbl_pstn_detail的分区p2*******01表示该分区存储2004100101时段的数据。
2.1.1.3字段名
字段名称必须用字母开头，采用有特征含义的单词或缩写，不能用双引号包含。
2.1.1.4主键名
前缀为pk_。
主键名称应是前缀+表名+构成的字段名。
如果复合主键的构成字段较多，则只包含第一个字段。
表名可以去掉前缀。
2.1.1.5外键名
前缀为fk_。
外键名称应是前缀+外键表名+主键表名+外键表构成的字段名。
表名可以去掉前缀。
2.1.2索引
4.1.2.1普通索引
前缀为idx_。
索引名称应是前缀+表名+构成的字段名。
如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。
表名可以去掉前缀。
2.1.2.2主键索引
前缀为idx_pk_。
索引名称应是前缀+表名+构成的主键字段名，在创建表时候用usingindex指定主键索引属性。
2.1.2.3唯一所以
前缀为idx_uk_。
索引名称应是前缀+表名+构成的字段名。
2.1.2.4外键索引
前缀为idx_fk_。
索引名称应是前缀+表名+构成的外键字段名。
2.1.2.5函数索引
前缀为idx_func_。
索引名称应是前缀+表名+构成的特征表达字符。
2.1.2.6蔟索引
前缀为idx_clu_。
索引名称应是前缀+表名+构成的簇字段。
2.1.3视图
前缀为v_。
按业务操作命名视图。
2.1.4实体化视图
前缀为mv_。
按业务操作命名实体化视图。
2.1.5存储过程
前缀为proc_。
按业务操作命名存储过程
2.1.6触发器
前缀为trig_。
触发器名应是前缀+表名+触发器名。
2.1.7函数
前缀为func_。
按业务操作命名函数
2.1.8数据包
前缀为pkg_。
按业务操作集合命名数据包。
2.1.9序列
前缀为seq_。
按业务属性命名。
2.1.10表空间
2.1.10.1公用表空间
前缀为tbs_。
根据存储的特性命名，例如：
tbs_parameter。
2.1.10.2专用表空间
tbs_<表名称>_nn。
该表空间专门存储指定的某一个表，或某一表的若干个分区的数据
2.1.11数据文件
<表空间名>nn.dbf。
nn=1，2，3，4，…等。
2.1.12普通变量
前缀为var_。
存放字符、数字、日期型变量。
2.1.13游标变量
前缀为cur_。
存放游标记录集。
2.1.14记录型变量
前缀为rec_。
存放记录型数据。
2.1.15表类型变量
前缀为tab_。
存放表类型数据。
2.1.16数据库链
前缀为dbl_。
表示分布式数据库外部链接关系。
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.1tablespace
每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。
对于事务比较繁忙的数据表，必须存放在该表的专用表空间中。
2.4.2.1.2pctused
默认pctused导致数据库物理空间利用率非常低40%左右；对于update比较少或update不导致行增大的表，pctused可设置在60—85之间；对于update能够导致行增大的表，update设置在40—70之间
2.4.2.1.3initrans
对于需要并行查询或者在rac数据库中需要并行处理的表，initrans设置为2的倍数，否则，不设该值。
2.4.2.1.4storage
2.4.2.1.4.1initial
尽量减少表数据段的extents数量，initial的大小尽量接近数据段的大小64k，128k，…，1m，2m，4m，8m，16m，…，等按2的倍数进行圆整。
例如表或分区数据段大小为28m，则initial取32m。
2.4.2.1.4.2next
表或分区扩展extents的大小，按上述方法进行圆整。
当表或分区数据段无法按initial接近值进行圆整的情况下，其大小可以按initial+next进行圆整。
此时，必须设置minextents=2。
例如：
表或分区数据段大小为150m，则initial=128m；next=32m，minextents=2。
2.4.2.1.4.3minextents
该参数表示表创建时候extents的初始数量，一般取1—2。
2.4.2.1.4.4pctincrease
表示每个扩展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外键关联
对于关联两个表的字段，一般应该分别建立主键、外键。
实际是否建立外键，根据对数据完整性的要求决定。
为了提高性能，对于数据量比较大的标要求对外健建立索引。
对于有要求级联删除属性的外键，必须指定ondeletecascade。
2.4.2.3.3null值
对于字段能否null，应该在sql建表脚本中明确指明，不应使用缺省。
由于null值在参加任何运算中，结果均为null。
所以在应用程序中必须利用nvl（）函数把可能为null值得字段或变量转换为非null的默认值。
例如：
nvl（sale,0）。
2.4.2.3.4check条件
对于字段有检查性约束，要求指定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安全性设计
2.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.5sql编写
2.5.1字符类型数据
sql中的字符类型数据应该统一使用单引号。
特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。
利用trim（）,lower（）等函数格式化匹配条件。
2.5.2复杂sql
对于非常复杂的sql（特别是有多层嵌套，带子句或相关查询的），应该先考虑是否设计不当引起的。
对于一些复杂sql可以考虑使用程序实现。
user_tab_comments数据字典
commenton可加注解
2.5.3高效性
3.6.2排序的优化
9i为专用服务器时系统变量workarea_size_policy设定为auto,statistics_level设定为typical可获取v$pga_target_advice中的优化建议。
参数pga_aggregate_target值为所有连接用户可用排序内存。
9i为共享服务器时workarea_size_policy设定为menaul,sort_area_size值为每用户排序内存。
如果内存不足将使用temp表空间进行排序,排序使用比率disk/meme
升级会员 