关于oracle设计

最新推荐文章于 2022-11-03 17:00:05 发布

xuhao1233344

最新推荐文章于 2022-11-03 17:00:05 发布

阅读量707

点赞数

分类专栏： oracle 文章标签： oracle 设计 sql 注释

本文链接：https://blog.csdn.net/xuhao1233344/article/details/61427809

版权

oracle 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1,逻辑

第三范式以上的设计消除了字段的冗余，（有效的避免出现数据变化时容易产生的不一致的情况）

1.1 第一范式（1NF）数据库表中的字段都是单一属性的，不可再分

2.2 第二范式（2NF）数据库表中不存在非关键字段对任一候选关键字段的部分函数依赖（部分函数依赖指的是存在组合关键字中的某些字段决定非关键字段的情况），；也就是所有非关键字段都完全依赖于任意一组候选关键字

3.3 第三范式（3NF）在第二范式基础上，数据表中如果不存在非关键字段对任一候选关键字段的传递函数依赖则复合第三范式

2规范

2.1凡是需要命名的对象其标识符长度均不能超过30个字符；如oracle中表名，字段名，函数名，过程名，触发器名，序列名，视图名长度均不能超过30个人字符。

2.2数据库各种名称必须以字符开头，但严禁使用sys开头名称中只能含有字母，数字，和下划线3类字符；不要使用dual做表名

2.3构成oracle数据库中的各种名称（表名，字段名，过程名，视图名）等等所有字符，必须使用大写，也就是不能在脚本中对任何名称添加双引号来设定字符的大小写形式，只要不采用双引号限制，oracle会自动将名称转化成大写。

2.4主键设计

2.4.1 临时表，外部表，流水表，日志表外，其他表都要建立主键

2.4.2 主键不能使用含有实际语义的列

2.4.3 对于500万以上的表，请数据组参与设计实施。采用先建唯一索引再添加主键约束的方式来创建主键

2.5 每个表，每个字段都要有注释，说明其含义，对于冗余字段还要有特别的说明其维护方法，外键字段说明参照与哪个表

2.6 一个表中的所有字段应当能存储在一个数据块中

3,表设计

主键最好设成单一主键，尽量不用复合主键，尽量使用没有业务语义的字段做主键，建议使用数值型，提高检索效率。

尽量规划在同一表空间，对于每个应用系统都能用到的表放在同一表空间中，一般是系统配置表，如果应用系统还包括一些大数据量的图片等多媒体数据表，应该放入第三个库中。这样可以改善数据库的性能。

大表应有明确的数据保留策略，明确业务字段的生命周期，并与本单位的数据生命周期管理相匹配，指定表的数据迁移，保留策略。

表设计中都应添加3个有用的字段：记录创建日期，记录创建人更新人，

大表不允许有触发器；触发器的功能可以用其他方式实现，在调试程序时触发器会成为干扰，假如你确实需要采用触发器，你最好集中对他文档化。

建议在数据库中引入版本管理机制来确定数据库的版本。

4.分区设计

每个表包含以下属性参数：TableSpace，PCTUSED；STORAGE

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

（如果几个静态表关系比较密切，则可以采用聚簇表的方法）

4.1大数据量的表需进行分区化

RANGE分区规范

SQL常依据某列的访问范围访问表，则对表使用RANGE分区，常见的如SQL根据时间范围查新，则使用时间字段作为分区关键字进行RANGE分区；将对表的多种访问结合起来考虑来确定分区的细度：1.大多数SQL操作的分区关键字值得范围；2.数据维护的需要，比如以月为单位删除历史数据3.数据访问的性能，以操作范围确定的分区数据量还是过大，（大于500万，则还需要细分）4.一个分区数据量小于500是硬性的尺度，但从技术上来看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很多，要灵活掌握。

LIST分区

SQL长居于某列的散列值访问表，则对表使用LIST分区，ＬＩＳＴ分区不支持多列分区关键字；常见情况针对某个地区或某个业务进行数据访问，那么就使用地区编号或者业务进行数据访问，使用地区编号或者业务编号作为分区关键字；将对表的多种访问结合考虑来确定分区的细度；1.一般使用一个分区关键字的值来划定一个分区；2，可以吧分区关键字的值相对应数据较少的几个分区合并成一个分区3.如果一个分区关键字值所对应的数据量过大，比如大于500万则应该对表采用RANGE分区，对该值得分区再采用HASH子分区；也就是说，一个可以采用LIST分区的表，肯定可以转化成RANGE分区（可带自分区），反之不然。4.一个分区数据量小于500是硬性的尺度，但从技术上来看，每个分区10万数据量的情况比每个分区20万数据量的情况要快很多，要灵活掌握。

HASH分区规范

SQL访问表不按照某列的范围进行，也不按某列的离散值进行，而且对该表的数据不会依据某列的值范围或者离散值进行定期维护，HASH分区是不知道应该选择何种分区时的选择；HASH分区的各分区都可能存在各种情况的数据，故而不能用于依据分区清理数据的情况，对确定分区细度的考虑：1.依据分区的数据量规划和表的数量来确定分区数；

2.一个分区的数据量要小于500万，硬性尺度，从技术上看，每个分区10万数据量分区方法比每个分区20万数据量的分区方法要快得多。

5列设计及命名规范

1.采用有意思的字段名，缩写等，使描述尽可能清楚。避免出现只有自己理解的缩写。

2.应增加更新日期字段时效性数据应包括“最新更新日期时间”字段，时间标记对查找数据问题的原因，按日期重新处理、重载数据和清楚旧数据特别有用。

3.不要有大小写混写的对象名，采用全部大写而且包含下划符的名字具有更好的可读性，绝对不要在对象名的字符之间留空格。

4.注意不要有保留词，要保证你的字段名没有和保留词，数据库系统或者常用访问方法冲突如DESC

5.保持字段名和类型的一致性，在命名字段并为其指定数据类型的时候一定要保证一致性；

6.仔细选择数字类型

7.给文本字段留足余量

8，列命名技巧，假如你给每个表的列名都采用统一前缀，那么在编写SQL表达式的时候会得到大大的简化

6索引规范

1.用不上分区条件的局部索引不宜建立，这样的查询会查询所有分区，严重影响性能

2.函数索引大多用于列运算，一般需要避免

3.位图索引遇到跟新，需谨慎设计，位图索引不适合用在表频繁更新的场合；位图索引不适合在所在列重复度很低的场合；

4外键未建索引将引发死锁及影响表连接性能（更新相关的表产生死锁，两表关联查询时性能低下）

5.建立联合索引需谨慎（要结合单列查询考虑，决定前缀，超过4个字段的联合索引需注意；在组合查询中，如果有等值条件和范围条件组合的情况，等值条件在前，性能更好；需考虑回表因素，一般情况下，如果见索引可以避免回表，也可考虑多列建组合索引。不过组合索引列不宜超过4个）

6.单表索引个数需要控制

超过5个以上的索引，在表记录很大时，将会极大的影响该表的更新；查询SQL（select tableName，count(*) from user_indexes Group by tableName having count(*)>=5order BY count(*) desc）

建后2个月内为使用过的索引，属于多于索引，可以考虑删除

7.单表无任何索引需要重视

单表无任何索引的情况一般比较少见，可以查询出来，在结合SQL应用进行分析，观察该表的大小以及是否有时间字段及编码字段这样适宜建索引的列，分析可以从以下脚本开始（select table_name from user_tables where table_name not in (selecttable_name from user_indexes)）

8.需注意索引的失效情况

导致索引失效的一般因素：对表进行MOVE 操作，会导致索引失效，操作需考虑索引的重建，对分区表进行系列操作，如split ，drop，truncate 分区时，容易导致分区表的全局索引失效，需考虑添加update，global，indexes的关键字操作，或者重建索引；分区表SPlit的时候，如果MAX区中已经有记录了，这个时候split就会导致有记录的新增分区的局部索引失效

（不同类型索引失效的查询）普通表及分区表的全局索引失效：

selectindex_name,table_name,tablespace_name,index_type from user_indexes where status='UNUSABLE';

分区表局部索引失效：

selectt1.index_name,t1.partition_name,t1.global_stats,t2.table_name,t2.table_typefrom user_ind_partitions t1,user_indexes t2 where t2.index_name=t1.index_nameand t1.status='UNUSABLE'

7环境参数规范

数据库参数

SGA及PGA参数；OLTP应用是主机内存的80%分配数据库，其中SGA80%，PGA20%

；OLAP应用是主机内存的80%分配数据，其中SGA50%，PGA50%;

POOCESS/SESSION

sqlplus "/as sysdba"

show parameter process

show parameter session

select count(*) from v$process;

select count(*) from v$session;

默认连接数150，大型应用一般不少于1000个；

OPEN_CURSOR 游标参数

sqlplus "/as sysdba "

show parameter open_cursor

默认open_cersors 是300 ，大型应用应1000以上，原则上不超过process设置

日志参数

一般来说Oracle默认的日志参数是3组，大小为500M，在实际较大的生产应用中往往不够，需要至少考虑在5组以上，大小在1G以上

是否归档，生产系统大多数需要开启归档，只有在特殊场合下数据安全性无关紧要，才可以关闭归档。

表空间规划

回滚表空间：自动管理避免自动扩展尽可能规划大

临时表空间：避免自动扩展尽可能大尽可能使用临时表空间组

业务表空间：控制个数不超过6个为宜，尽量避免自动扩展，超阀值由监控来检查，根据自己的业务固定表空间名，表空间需良好分类（参数配置表，业务数据表，历史记录表），表空间需合理命名

RAC系统

尽量采用BALANCE模式，保证两节点压力大致相当，可适当考虑不同业务部属在不同的节点上，避免RAC的CACHE争用，尽量考虑不同的节点使用不同的表空间

命名规范：并非一定要这么命名，只是强调在同一开发团队甚至同一公司里，必须同一规范，在内部达成一致的认可。

如表以t_为前缀

select * from user_tables where substr(table_name,1,2)<>'t_'

主键以pk_为前缀

select constraints_name,table_name fromuser_constraints where constraint_type='P'

and sub(constraint_name,1,3) <>'PK_' and constraint_name not like'BIN$%'

外键以fk_为前缀

select constraints_name,table_name fromuser_constraints where constraint_type='R'

and sub(constraint_name,1,3) <>'FK_' and constraint_name not like'BIN$%'

唯一索引以ux_为前缀

select constraints_name,table_name fromuser_constraints where constraint_type='U'

and sub(constraint_name,1,3) <>'UX_' and table_name not like 'BIN$%'

普通索引以idx_为前缀

select index_name,table_name fromuser_indexes where index_type='NORMAL' and uniqueness='NONUNIQUE' andsubstr(index_name,1,4)<>'IDX_' and table_name not like 'BIN$%'

开发规范

SQL 编写规范

关键字大小写统一，表，字段是否使用别名（明确规定别名的命名方法）SQL脚本采用缩进风格，风格一致，缩进格式一致。

sql 一般不使用其他用户下的表，如需使用，可以通过synonym，并在文档中进行记录

select * 语句及insert语句中的字段名

避免使用select * 语句，insert语句也必须给出字段列表，避免由于表结构变更时导致语句的不可执行。

insert into table_name

(col_1,col2,col3)

values

(value1,value2,value3);

insert 。。。。select 语句书写方法

insert into tablename1