oracle-9i-数据库设计指引全集

Oracle9i数据库设计指引全集一、数据库物理设计原则数据库环境配置原则操作系统环境：对于中小型数据库系统，采用linux操作系统比较合适，对于数据库冗余要求负载均衡能力要求较高的系统，可以采用oracle9irac的集群数据库的方法，集群节点数范围在 2—64个。对于大型数据库系统，可以采用sunsolarissparc64位小型机系统或hp9000系列小型机系统。rad5适合只读操作的数据库，rad1适合oltp数据库内存要求对于linux操作系统下的数据库,由于在正常情况下oracle对sga的管理能力不超过1.7g。所以总的物理内存在4g以下。sga的大小为物理内存的 50%—75%。对于64位的小型系统，oracle数据库对sga的管理超过2g的限制，sga设计在一个合适的范围内：物理内存的50%—70%，当sga过大的时候会导致内存分页，影响系统性能。交换区设计当物理内存在2g以下的情况下，交换分区 swap为物理内存的3倍，当物理内存＞2g的情况下，swap大小为物理内存的1—2倍。其他环境变量参考oracle相关的安装文档和随机文档。数据库设计原则数据库sid数据库sid是唯一标志数据库的符号，命名长度不能超过5个字符。对于单节点数据库，以字符开头的5个长度以内字串作为sid的命名。对于集群数据库，当命名sid后，各节点sid自动命名为sidnn，其中nn为节点号：1，2，…,64。例如racl、rac2、rac24。数据库全局名DuuIiaseDuuIiaseGanligurdtion感沽巧1川山 4pfBjItalabA^eIdcntiflulion|DPrariM：Self”匚Teip^lahNimvIncludesDfchfIL.|DPrariM：Self”匚Teip^lahNimvIncludesDfchfIL.QVk 1C^-rerc:r1JT“于亍VesTraniacdonProcescInQ¥邸rD»t«bd.s^Morh讪刪，dbMG血I曰Mmta<tlplatefr^rtilth自丰:川..wmgI花tfrgh自岛初匕品专Specifythe+sltowi^gdatata^Binformi^icinaH狛I山i忖ba哙-.=皿iQuWy>.^lifiv'Jb；WdD-^b^th躬丁咖』ly』曲电termunams.domain",ulob^E口alnbast； J自匚gsT^-trrA hreiwrtn-.sdbyinOrddeSiiosiarcsonei:h叮Imtvddtsbasan<-dsEachfnstanoi国uni®□朮/i^rtifirdbyan饬机feSyst^nn血明併厂(5©).Fornchctusifer怕關ir,i'Jtdfi<&1th©EPk-'■mf>T'r-ed雄f启MrYimg艸出乐血卜讦"I汕肥，狛汕M夺numberfcrKchI苗谕jihthat箱耳讥如atiqliy牺h■时韓eckA旨时9希怕dSIDpr&fbihstsbepnent^dfwyouwhichy心期canwcepl信thimge&&"valueyoupr^fer-数据库全局名称： <sid>.domain123数据库类型选择对于海量数据库系统， 采用datawarehouse的类型。对于小型数据库或oltp类型的数据库,采用transactionprocessing 类型。OMflftm比HfiflilfAllCn3”』l.ii'l,、【叩MtK.Duljh.isicM'.plilu^-数据库连接类型选择oracle数据库有专用服务器连接类型和多线程服务器 mts连接类型。对于批处理服务，需要专用服务器连接方式，而对于oltp服务则mts的连接方式比较合适。由于采用 mts后，可以通过配置网络服务实现某些特定批处理服务采用专用服务器连接方式， 所以数据库设计时一般采用mts类型。125数据库sga配置在数据库初始设计阶段采用按比例配数据库sga可以采用手工配置或按物理内存比例配置,置方式，在实际应用中按系统调优方式修改 在数据库初始设计阶段采用按比例配数据库sga可以采用手工配置或按物理内存比例配置,置方式，在实际应用中按系统调优方式修改 sga。126数据库字符集选择为了使数据库能够正确支持多国语言，必须配置合适的数据库字符集，采用 utf8字符集。注意：如果没有大对象，在使用过程中进行语言转换没有什么影响， 具体过程如下（切记设定的字符集必须是 oracle支持，不然不能start）：sql>shutdownimmediate;sql>startupmount;sql>altersystemenablerestrictedsession;sql>altersystemsetjob_queue_processes=O;sql>alterdatabaseopen;sql>alterdatabasecharactersetinternal_usewe8iso8859p1;sql>shutdownimmediate;sql>startup数据库其他参数配置dbfilesdb_files是数据库能够同时打开的文件数量，默认值是200个。当数据库规划时文件数量files接近或超过200个时候，按以下估计值配置:db_files=files*1.5db_block_size一个extent要是5个blocks的倍数为好，如：一个blocks是4096字节，那一个extent就是2m4m或8m为好。db_block_size 是数据库最小物理单元，一旦数据库创建完成，该参数无法修改，db_block_size按以下规则调整：数据仓库类型：db_block_size尽可能大，采用8192或16384oltp类型：db_block_size用比较小的取值范围：2048或4096blocks推荐是系统操作的块倍数（裸设备块大小是512字节，ntfs是4k，使用8k的方式在大部分系统上通用）。数据库控制文件配置控制文件镜象多个控制文件存放在不同的物理位置。控制文件配置控制文件中参数设置，最大的数据文件数量不能小于数据库参数 db_files。数据库日志文件配置日志文件大小日志文件的大小由数据库事务处理量决定，在设计过程中，确保每 20分钟切换一个日志文件。所以对于批处理系统，日志文件大小为几百m到几g的大小。对于oltp系统，日志文件大小为几百m以内。日志文件组数量对于批处理系统，日志文件组为 5—10组；对于oltp系统，日志文件组为3—5组，每组日志大小保持一致；对于集群数据库系统，每节点有各自独立的日志组。日志成员数量为了确保日志能够镜象作用，每日志组的成员为 2个。数据库回滚段配置在oracle9i数据库中，设计undo表空间取代以前版本的回滚段表空间。undo表空间大小的设计规范由以下公式计算：undospace=ur*ups*db_block_size+冗余量ur：表示在undo中保持的最长时间数（秒），由数据库参数undo_retention值决定。ups：表示在undo中，每秒产生的数据库块数量。例如：在数据库中保留2小时的回退数据，假定每小时产生200个数据库块。则undospace=2*3600*200*4k=5.8g数据库临时段表空间配置数据库临时段表空间根据实际生产环境情况调整其大小，表空间属性为自动扩展。数据库系统表空间配置系统表空间大小1g左右，除了存放数据库数据字典的数据外，其他数据不得存储在系统表空间。数据库表空间设计原则表空间大小定义原则当表空间大小小于操作系统对最大文件限制时，表空间由一个文件组成。如果表空间大小大于操作系统对最大文件限制时，该表空间由多个数据文件组成，表空间的总大小为估算为：tablespace+sum（数据段+索引段）*150%。表空间扩展性设计原则表空间数据文件采用自动扩展的方式，扩展容量快大小按2的整数倍（1m、2m、4m、8m、16m、32m64m）进行扩展，创建表空间时尽量采用 nologing 选项。表空间的最大限制一般采用unlimited ，除非确切知道表空间数据文件的最大使用范围。 （一般windows32位系统的文件最大2g, 64位的unix系统系统文件最大128 g,但也要注意文件格式设定的文件大小） ，建议最大为2g。表空间采用local管理方式，例如：createtablespacetbs_userinfodatafile'/oradata/tbs_userinfo.dbf'size8mreuseautoextendonnext2mmaxsizeunlimitednologgingextentmanagementlocalautoallocatesegmentspacemanagementauto;裸设备的使用一个scsi设备可以14个分区，unix操作系统256个分区,性能比文件系统方式高15％左右，空间大于要小于(实际分区大小减两个oracle的数据块)，比如100m，大于为100000k，推荐在unix使用软连接(ln)方式把裸设备形成文件，用加入表空间时加resue选项，当然也可只接把设备加入表空间，移动裸设备使用dd命令对于windows平台，oracle提供软连接工具，实现裸设备的使用，计算一条记录的长度二、数据库逻辑设计原则命名规范表属性规范表名前缀为tbl_。数据表名称必须以有特征含义的单词或缩写组成，中间可以用“ _”分割，例如：tbl_pstn_detail。表名称不能用双引号包含。表分区名前缀为p。分区名必须有特定含义的单词或字串。例如：tbl_pstn_detail的分区p2004100101表示该分区存储2004100101时段的数据。字段名字段名称必须用字母开头，采用有特征含义的单词或缩写，不能用双引号包含。主键名前缀为pk_。主键名称应是前缀+表名+构成的字段名。如果复合主键的构成字段较多，则只包含第一个字段。表名可以去掉前缀。外键名前缀为fk_。外键名称应是前缀+外键表名+主键表名+外键表构成的字段名。表名可以去掉前缀。

索引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表空间公用表空间前缀为tbs_。根据存储的特性命名，例如：tbs_parameter。专用表空间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_。表示分布式数据库外部链接关系。命名语言命名应该使用英文单词，避免使用拼音，特别不应该使用拼音简写。命名不允许使用中文或者特殊字符。英文单词用对象本身意义相对或相近的单词，选择最简单或最通用的单词，不能使用毫不相干的单词来命名。当一个单词不能表达对象含义时，用词组组合，如果组合太长时，采用用简或缩写，缩写要基本能表达原单词的 意义。当出现对象名重名时，是不同类型对象时，加类型前缀或后缀以示区别。大小写名称一律大写，以方便不同数据库移植，以及避免程序调用问题。单词分隔命名的各单词之间可以使用下划线进行分隔。保留字命名不允许使用sql保留字。命名长度表名、字段名、视图名长度应限制在20个字符内(含前缀)。字段名称同一个字段名在一个数据库中只能代表一个意思。比如telephone在一个表中代表“电话号码”的意思，在另外一个表中就不能代表“手机号码”的意思。不同的表用于相同内容的字段应该采用同样的名称，字段类型定义。2.3数据类型字符型固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。数字型数字型字段尽量采用number类型。日期和时间系统时间由数据库产生的系统时间首选数据库的日期型，如date类型。外部时间由数据导入或外部应用程序产生的日期时间类型采用varchar类型，数据格式采用：yyyymmddhh24miss。大字段如无特别需要，避免使用大字段（blob，clob，long，text，image等）。唯一键对于数字型唯一键值，尽可能用系列sequence产生。设计范式如无性能上的必须原因，应该使用关系数据库理论，达到较高的范式，避免数据冗余,但是如果在数据量上与性能上无特别要求，考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3nf.如非确实必要，避免一个字段中存储多个标志的做法。如11101表示5个标志的一种取值。这往往是增加复杂度，降低性能的地方。表设计逻辑段设计原则tablespace每个表在创建时候，必须指定所在的表空间，不要采用默认表空间以防止表建立在系统表空间上导致性能问题。对于事务比较繁忙的数据表，必须存放在该表的专用表空间中。pctused默认pctused导致数据库物理空间利用率非常低 40%左右；对于update比较少或update不导致行增大的表，pctused可设置在60—85之间；对于update能够导致行增大的表，update设置在40—70之间initrans对于需要并行查询或者在rac数据库中需要并行处理的表，initrans设置为2的倍数，否则，不设该值。storageinitial尽量减少表数据段的 extents数量，initial 的大小尽量接近数据段的大小 64k,128k，…，1m,2m,4m,8m,16m，…，等按2的倍数进行圆整。 例如表或分区数据段大小为 28m,则initial取32m。next表或分区扩展extents的大小,按上述方法进行圆整。当表或分区数据段无法按 initial接近值进行圆整的情况下,其大小可以按initial+next进行圆整。此时,必须设置minextents=2。例如：表或分区数据段大小为 150m,则initial=128m；next=32m,minextents=2。minextents该参数表示表创建时候extents的初始数量，一般取1—2。pctincrease表示每个扩展extents的增长率，设置pctincrease=0能够获得较好的存储性能。特殊表设计原则分区表对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。如果表按某些字段进行增长，则采用按字段值范围进行范围分区；如果表按某个字段的几个关键值进行分布，则采用列表分区；对于静态表，则采用hash分区或列表分区；在范围分区中，如果数据按某关键字段均衡分布，则采用子分区的复合分区方法。聚蔟表如果某几个静态表关系比较密切，则可以采用聚蔟表的方法。完整性设计原则主键约束关联表的父表要求有主健，主健字段或组合字段必须满足非空属性和唯一性要求。 对于数据量比较大的父表，要求指定索引段。外键关联对于关联两个表的字段，一般应该分别建立主键、外键。实际是否建立外键，根据对数据完整性的要求决定。为了提高性能，对于数据量比较大的标要求对外健建立索引。对于有要求级联删除属性的外键，必须指定ondeletecascade。♦♦除特殊需要，避免类似 select*from[tablename] 而没有检索条件的视图；2.4.2.3.32.4.2.3.3null 值对于字段能否null，应该在sql建表脚本中明确指明，不应使用缺省。由于null值在参加任何运算中，结果均为null。所以在应用程序中必须利用nvl()函数把可能为null值得字段或变量转换为非null的默认值。例如：nvl(sale,0)。check条件对于字段有检查性约束，要求指定check规则。触发器触发器是一种特殊的存储过程，通过数据表的dml操作而触发执行，起作用是为确保数据的完整性和一致性不被破坏而创建，实现数据的完整约束。触发器的before或after事务属性的选择时候，对表操作的事务属性必须与应用程序事务属性保持一致，以避免死锁发生。在大型导入表中，尽量避免使用触发器。注释表、字段等应该有中文名称注释，以及需要说明的内容。2.4.3索引设计对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。在分区表中，尽量采用local分区索引以方便分区维护。除非时分区local索引，否则在创建索引段时候必须指定指定索引段的tablespace、storage属性，具体参考4.4.2.1内容。视图设计视图是虚拟的数据库表，在使用时要遵循以下原则：♦从一个或多个库表中查询部分数据项；♦为简化查询，将复杂的检索或字查询通过视图实现；提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员；视图中如果嵌套使用视图，级数不得超过3级；由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的库表，不宜使用视图；可以采用实体化视图代替。♦视图中尽量避免出现数据排序的sql语句。包设计存储过程、函数、外部游标必须在指定的数据包对象 package中实现。存储过程、函数的建立如同其它语言形式的编程过程，适合采用模块化设计方法；当 具体算法改变时，只需要修改需要存储过程即可，不需要修改其它语言的源程序。当和数据库频繁交换数据是通过存储过程可以提高运行速度，由于只有被授权的用户才能执行存储过程，所以存储过程有利于提高系统的安全性。存储过程、函数必须检索数据库表记录或数据库其他对象，甚至修改（执行insert、delete、update、drop、create等操作）数据库信息。如果某项功能不需要和数据库打交道，则不得通过数据库存储过程或函数的方式实现。在函数中避免采用 dml或ddl语句。在数据包采用存储过程、函数重载的方法，简化数据包设计，提高代码效率。存储过程、函数必须有相应的出错处理功能。安全性设计管理默认用户在生产环境中，必须严格管理sys和system用户，必须修改其默认密码，禁止用该用户建立数据库应用对象。删除或锁定数据库测试用户scott。数据库级用户权限设计必须按照应用需求，设计不同的用户访问权限。包括应用系统管理用户，普通用户等，按照业务需求建立不同的应用角色。用户访问另外的用户对象时，应该通过创建同义词对象 synonym进行访问。角色与权限确定每个角色对数据库表的操作权限，如创建、检索、更新、删除等。每个角色拥有刚好能够完成任务的权限，不多也不少。在应用时再为用户分配角色，则每个用户的权限等于他所兼角色的权限之和。应用级用户设计应用级的用户帐号密码不能与数据库相同，防止用户直接操作数据库。用户只能用帐号登陆到应用软件，通过应用软件访问数据库，而没有其它途径操作数据库。用户密码管理用户帐号的密码必须进行加密处理，确保在任何地方的查询都不会岀现密码的明文。sql编写字符类型数据sql中的字符类型数据应该统一使用单引号。特别对纯数字的字串，必须用单引号，否则会导致内部转换而引起性能问题或索引失效问题。利用 trim（）,lower（） 等函数格式化匹配条件。复杂sql对于非常复杂的sql（特别是有多层嵌套，带子句或相关查询的 ），应该先考虑是否设计不当引起的。对于一些复杂 sql可以考虑使用程序实现。user_tab_comments数据字典commenton可加注解高效性排序的