没有合适的资源?快使用搜索试试~ 我知道了~
优化Oracle库表设计的若干方法.docx
1.该资源内容由用户上传,如若侵权请联系客服进行举报
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
2.虚拟产品一经售出概不退款(资源遇到问题,请及时私信上传者)
版权申诉
0 下载量 159 浏览量
2022-05-05
10:30:17
上传
评论
收藏 270KB DOCX 举报
温馨提示
试读
16页
优化Oracle库表设计的若干方法.docx
资源推荐
资源详情
资源评论
优化 Oracle 库表设计的若干方法
前言
绝大多数的 Oracle 数据库性能问题都是由于数据库设计不合理造成的,只有少部分问题
根植于 Database Buer、Share Pool、Redo Log Buer 等内存模块配置不合理,I/O
争用,CPU 争用等 DBA 职责范围上。所以除非是面对一个业已完成不可变更的系统,否
则我们不应过多地将关注点投向内存、I/O、CPU 等性能调整项目上,而应关注数据库表
本身的设计是否合理,库表设计的合理性才是程序性能的真正执牛耳者。
合理的数据库设计需要考虑以下的方面:
·业务数据以何种方式表达。如一个员工有多个 Email,你可以在 T_EMPLOYEE 表中建立
多个 Email 字段如 email_1、email_2、 email_3,也可以创建一个 T_EMAIL 子表来存
储,甚至可以用逗号分隔开多个 Email 地址存放在一个字段中。
·数据以何种方式物理存储。如大表的分区,表空间的合理设计等。
·如何建立合理的数据表索引。表索引几乎是提高数据表查询性能最有效的方法,Oracle
拥有类型丰富的数据表索引类型,如何取舍选择显得特别重要。
本文我们将目光主要聚焦于数据表的索引上,同时也将提及其他两点的内容。通过对一个
简单的库表设计实例的分析引出设计中的不足,并逐一改正。考虑到手工编写库表的 SQL
脚本原始且低效,我们将用目前最流行的库表设计工具 PowerDesigner 10 来讲述表设计
的过程,所以在本文中你还会了解到一些相关的 PowerDesigner 的使用技巧。
一个简单的例子
某个开发人员着手设计一个订单的系统,这个系统中有两个主要的业务表,分别是订单基
本信息表和订单条目表,这两张表具有主从关系的表,其中 T_ORDER 是订单主表,而
T_ORDER_ITEM 是订单条目表。数据库设计人员的设计成果如图 1 所示:
图 1 订单主从表
ORDER_ID 是订单号,为 T_ORDER 的主键,通过名为 SEQ_ORDER_ID 的序列产生键值,
而 ITEM_ID 是 T_ORDER_ITEM 表的主键,通过名为 SEQ_ORDER_ITEM 的序列产生键
值,T_ORDER_ITEM 通过 ORDER_ID 外键关联到 T_ORDER 表。
需求文档指出订单记录将通过以下两种方式来查询数据:
·CLIENT + ORDER_DATE+IS_SHPPED:根据"客户+订货日期+是否发货"条件查询订
单及订单条目。
·ORDER_DATE+IS_SHIPPED:根据"订货日期+是否发货"条件查询订单及订单条目。
数 据 库 设 计 人 员 根 据 这 个 要 求 , 在 T_ORDER 表 的 CLIENT 、 ORDER_DATE 及
IS_SHPPED 三 字 段 上 建 立 了 一 个 复 合 索 引 IDX_ORDER_COMPOSITE ; 在
T_ORDER_ITEM 为外键 ORDER_ID 建立 IDX_ORDER_ITEM_ORDER_ID 索引。
让我们看一下该份设计的最终 SQL 脚本:
/*订单表*/
create table T_ORDER (
ORDER_ID NUMBER(10) not null,
ADDRESS VARCHAR2(100),
CLIENT VARCHAR2(60),
ORDER_DATE CHAR(8),
IS_SHIPPED CHAR(1),
constraint PK_T_ORDER primary key (ORDER_ID)
);
create index IDX_CLIENT on T_ORDER (
CLIENT ASC,
ORDER_DATE ASC,
IS_SHIPPED ASC);
/*订单条目子表*/
create table T_ORDER_ITEM (
ITEM_ID NUMBER(10) not null,
ORDER_ID NUMBER(10),
ITEM VARCHAR2(20),
COUNT NUMBER(10),
constraint PK_T_ORDER_ITEM primary key (ITEM_ID)
);
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM (
ORDER_ID ASC);
alter table T_ORDER_ITEM add constraint FK_T_ORDER__REFERENCE_T_ORDER foreign key
(ORDER_ID) references T_ORDER (ORDER_ID);
我们承认在 ER 关系上,这份设计并不存在的缺陷,但却存在以下有待优化的地方:
·没有将表数据和索引数据存储到不同的表空间中,而不加区别地将它们存储到同一表空间
里。这样,不但会造成 I/O 竞争,也为数据库的维护工作带来不便。
·ORACLE 会自动为表的主键列创建一个普通 B-Tree 索引,但由于这两张表的主键值都通
过序列提供,具有严格的顺序性(升序或降序),此时手工为其指定一个反键索引
(reverse key index)将更加合理。
·在子表 T_ORDER_ITEM 外键列 ORDER_ID 上建立的 IDX_ORDER_ITEM_ORDER_ID
的普通 B-Tree 索引非常适合设置为压缩型索引,即建立一个压缩型的 B-Tree 索引。因为
一份订单会对应多个订单条目,这就意味着 T_ORDER_ITEM 表存在许多同值的
ORDER_ID 列值,通过将其索引指定为压缩型的 B-Tree 索引,不但可以减少
IDX_ORDER_ITEM_ORDER_ID 所需的存储空间,还将提高表操作的性能。
·企图仅通过建立一个包含 3 字段 IDX_ORDER_COMPOSITE 复合索引满足如前所述的两
种查询条件方式的索引是有问题的,事实上使用 ORDER_DATE+IS_SHIPPED 复合条件
的查询将利用不到 IDX_ORDER_COMPOSITE 索引。
5优化设计
1、将表数据和索引数据分开表空间存储
1.1 表数据和索引为何需要使用独立的表空间
Oracle 强烈建立,任何一个应用程序的库表至少需要创建两个表空间,其中之一用于存储
表数据,而另一个用于存储表索引数据。因为如果将表数据和索引数据放在一起,表数据
的 I/O 操作和索引的 I/O 操作将产生影响系统性能的 I/O 竞争,降低系统的响应效率。将表
数据和索引数据存放在不同的表空间中(如一个为 APP_DATA,另一个为 APP_IDX),
并在物理层面将这两个表空间的数据文件放在不同的物理磁盘上,就可以避免这种竞争了。
拥有独立的表空间,就意味着可以独立地为表数据和索引数据提供独立的物理存储参数,
而不会发生相互影响,毕竟表数据和索引数据拥有不同的特性,而这些特性又直接影响了
物理存储参数的设定。
此外,表数据和索引数据独立存储,还会带来数据管理和维护上的方面。如你在迁移一个
业务数据库时,为了降低数据大小,可以只迁出表数据的表空间,在目标数据库中通过重
建索引的方式就可以生成索引数据了。
1.2 表数据和索引使用不同表空间的 SQL 语法
指定表数据及索引数据存储表空间语句最简单的形式如下。
将表数据存储在 APP_DATA 表空间里:
create table T_ORDER ( ORDER_ID NUMBER(10) not null, …)tablespace
APP_DATA;
将索引数据存储在 APP_IDX 表空间里:
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID
ASC)tablespace APP_IDX;
1.3 PowerDesigner 中如何操作
1) 首先,必须创建两个表空间。通过 Model->Tablespace...在 List of Tablespaces 中
创建两个表空间:
图 2 创建表空间
2) 为每张表指定表数据存储的表空间。在设计区中双击表,打开 Table Properties 设计
窗口,切换到 options 页,按图 3 所示指定表数据的存储表空间。
剩余15页未读,继续阅读
资源评论
cailibin
- 粉丝: 4
- 资源: 7015
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功