表设计
逻辑段设计原则
Tablespace
每个表在创建时候必须指定所在的表空间不要采用默认表空间以防止表建立在系统表空间上导致性能问题对于事务比较繁忙的数据表必须存放在该表的专用表空间中
Pctused
默认pctused导致数据库物理空间利用率非常低%左右对于update比较少或update不导致行增大的表pctused可设置在—之间对于update能够导致行增大的表update设置在—之间
Initrans
对于需要并行查询或者在RAC数据库中需要并行处理的表initrans设置为的倍数否则不设该值
Storage
Initial
尽量减少表数据段的extents数量initial的大小尽量接近数据段的大小KK… MMMMM …等按的倍数进行圆整例如表或分区数据段大小为M则initial取M
Next
表或分区扩展extents的大小按上述方法进行圆整当表或分区数据段无法按Initial接近值进行圆整的情况下其大小可以按 Initial+Next进行圆整此时必须设置Minextents=例如表或分区数据段大小为M则Initial=MNext=MMinextents=
Minextents
该参数表示表创建时候Extents的初始数量一般取—
Pctincrease
表示每个扩展Extents的增长率设置pctincrease=能够获得较好的存储性能
特殊表设计原则
分区表
对于数据量比较大的表根据表数据的属性进行分区以得到较好的性能如果表按某些字段进行增长则采用按字段值范围进行范围分区如果表按某个字段的几个关键值进行分布则采用列表分区对于静态表则采用hash分区或列表分区在范围分区中如果数据按某关键字段均衡分布则采用子分区的复合分区方法
聚蔟表
如果某几个静态表关系比较密切则可以采用聚蔟表的方法
完整性设计原则
主键约束
关联表的父表要求有主健主健字段或组合字段必须满足非空属性和唯一性要求对于数据量比较大的父表要求指定索引段
外键关联
对于关联两个表的字段一般应该分别建立主键外键实际是否建立外键根据对数据完整性的要求决定为了提高性能对于数据量比较大的标要求对外健建立索引对于有要求级联删除属性的外键必须指定on delete cascade
NULL值
对于字段能否null应该在sql建表脚本中明确指明不应使用缺省由于NULL值在参加任何运算中结果均为NULL所以在应用程序中必须利用nvl()函数把可能为NULL值得字段或变量转换为非NULL的默认值例如NVL（sale）
Check条件
对于字段有检查性约束要求指定check规则
触发器
触发器是一种特殊的存储过程通过数据表的DML操作而触发执行起作用是为确保数据的完整性和一致性不被破坏而创建实现数据的完整约束
触发器的before或after事务属性的选择时候对表操作的事务属性必须与应用程序事务属性保持一致以避免死锁发生在大型导入表中尽量避免使用触发器
注释
表字段等应该有中文名称注释以及需要说明的内容
索引设计
对于查询中需要作为查询条件的字段可以考虑建立索引最终根据性能的需要决定是否建立索引对于复合索引索引字段顺序比较关键把查询频率比较高的字段排在索引组合的最前面在分区表中尽量采用local分区索引以方便分区维护
除非时分区local索引否则在创建索引段时候必须指定指定索引段的tablespacestorage属性具体参考内容
视图设计
视图是虚拟的数据库表在使用时要遵循以下原则
从一个或多个库表中查询部分数据项
为简化查询将复杂的检索或字查询通过视图实现
提高数据的安全性只将需要查看的数据信息显示给权限有限的人员
视图中如果嵌套使用视图级数不得超过级
由于视图中只能固定条件或没有条件所以对于数据量较大或随时间的推移逐渐增多的库表不宜使用视图可以采用实体化视图代替
除特殊需要避免类似Select * from [TableName] 而没有检索条件的视图
视图中尽量避免出现数据排序的SQL语句
包设计
存储过程函数外部游标必须在指定的数据包对象PACKAGE中实现存储过程函数的建立如同其它语言形式的编程过程适合采用模块化设计方法当具体算法改变时只需要修改需要存储过程即可不需要修改其它语言的源程序当和数据库频繁交换数据是通过存储过程可以提高运行速度由于只有被授权的用户才能执行存储过程所以存储过程有利于提高系统的安全性
存储过程函数必须检索数据库表记录或数据库其他对象甚至修改（执行InsertDeleteUpdateDropCreate等操作）数据库信息如果某项功能不需要和数据库打交道则不得通过数据库存储过程或函数的方式实现在函数中避免采用DML或DDL语句
在数据包采用存储过程函数重载的方法简化数据包设计提高代码效率存储过程函数必须有相应的出错处理功能
安全性设计
管理默认用户
在生产环境中必须严格管理sys和system用户必须修改其默认密码禁止用该用户建立数据库应用对象删除或锁定数据库测试用户scott
数据库级用户权限设计
必须按照应用需求设计不同的用户访问权限包括应用系统管理用户普通用户等按照业务需求建立不同的应用角色
用户访问另外的用户对象时应该通过创建同义词对象synonym进行访问
角色与权限
确定每个角色对数据库表的操作权限如创建检索更新删除等每个角色拥有刚好能够完成任务的权限不多也不少在应用时再为用户分配角色则每个用户的权限等于他所兼角色的权限之和
应用级用户设计
应用级的用户帐号密码不能与数据库相同防止用户直接操作数据库用户只能用帐号登陆到应用软件通过应用软件访问数据库而没有其它途径操作数据库
用户密码管理
用户帐号的密码必须进行加密处理确保在任何地方的查询都不会出现密码的明文
SQL编写
字符类型数据
SQL中的字符类型数据应该统一使用单引号特别对纯数字的字串必须用单引号否则会导致内部转换而引起性能问题或索引失效问题利用trim()lower()等函数格式化匹配条件
复杂sql
对于非常复杂的sql(特别是有多层嵌套带子句或相关查询的)应该先考虑是否设计不当引起的对于一些复杂SQL可以考虑使用程序实现
USER_TAB_COMMENTS 数据字典
Comment on 可加注解
高效性
避免In子句
使用In 或 not In子句时特别是当子句中有多个值时且查询数据表数据较多时速度会明显下降可以采用连接查询或外连接查询来提高性能
Char 比 varchar 查询时高询
在进行查询及建立索引时char比varchar的效率要高当然varchar在存储上比char要好
避免嵌套的Select子句
这个实际上是In子句的特例
避免使用Select * 语句
如果不是必要取出所有数据不要用*来代替应给出字段列表注不含select count(*)
避免不必要的排序
不必要的数据排序大大的降低系统性能
健壮性
Insert语句
使用Insert语句一定要给出要插入值的字段列表这样即使更改了表结构加了字段也不会影响现有系统的运行
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字段值非相同记录数
Not null 为字段类型性质的约束
本约束功能在后期无语法使期失效可使用修改字段类型方式
alter table modify 字段名 类型 not null
alter table modify 字段名 类型
