ITPub博客

首页 > 数据库 > Oracle > PowerDesigner优化Oracle库表设计.

PowerDesigner优化Oracle库表设计.

Oracle 作者:pwshzhf 时间:2011-07-22 01:20:04 0 删除 编辑

序言

 绝大多数的Oracle数据库功能问题都是由于数据库设计不科学构成的,只有少局部问题根植于Database Buffer、Share Pool、Redo LogBuffer等内存模块搭配不科学,I/O争用,CPU争用等DBA职责范围上。因而除非是面对一个业已告终不可改变的系统,否则我们不应过多地将关怀点投向内存、I/O、CPU等功能调剂项目上,而应关怀数据库表本身的设计是否科学,库表设计的科学性才是过程功能的恳挚执牛耳者。

科学的数据库设计必需琢磨以下的方面:
 ·业务数据以何种措施表白。如一个员工有多个Email,你能够在T_EMPLOYEE表中发生多个Email字段如email_1、email_2、email_3,也能够创立一个T_EMAIL子表来存储,甚至能够用逗号分隔开多个Email地址储藏在一个字段中。

 ·数据以何种措施物理存储。如大表的分区,表空间的科学设计等。

 ·如何发生科学的数据表索引。表索引几乎是长进数据表查询功能最管用的措施,Oracle具有种类丰富的数据表索引种类,如何取舍抉择显得尤其重要。

 本文我们将目光重要聚焦于数据表的索引上,同时也将提及其他两点的内容。穿越对一个容易的库表设计实例的分析引出设计中的不足,并逐一修订。琢磨到达工编写库表的SQL脚本原始且低效,我们将用现在最流行的库表设计工具PowerDesigner来解说表设计的过程,因而在本文中你还会打听到一些相干的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索引。

优化设计
 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所示指定表数据的存储表空间。

图 3 指定表数据的存储表空间

  3) 为每个索引指定索引数据的存储表空间。在TableProperties中切换到Indexes页,在这里列出了表的所有索引,双击需设置表空间的索引,在弹出的IndexProperties窗口中切换到Options页,按如下措施指定索引的存储表空间。

图 4 指定索引数据的存储表空间

  将表空间的问题延长一下:一个利用系统库表的表空间能够举行更精细的划分。
  率先,万一表中存在LOB种类的字段,有为其指定一个特定的表空间,因为LOB种类的数据在物理存储构造的管教上和等闲数据的计策有很大的不同,将其放在一个自力更生的表空间中,就可得体地设置其物理存储参数了。

  其次,必需琢磨库表数据的DML垄断个性:依据DML(INSERT,UPDATE,DELETE)垄断频繁程度,将几乎不发生任何DML垄断的数据放在自力更生的表空间中,因为极少DML垄断的表可设置相称其个性的物理参数:如PCTFREE可置为0,其BUFFER_POOL指定为KEEP,以便将数据缓存在KEEP数据缓存区中等等,不一而足。

  另外,还能够琢磨按业务必要将不同的业务模块离别储藏,这重要是琢磨到备份问题。假想我们有一局部业务数据重要性很强,而其他的业务数据重要性相对较弱,这么就能够将两者离别存储,以便设置不同的备份计策。
  当然,无节制的细化表空间也将带来管教上和安排上的混杂,依据业务需求科学地计划表空间以到达管教和功能上的最佳经常必需更多的权衡。

 2、显式为主键列发生反向键索引

 2.1 反向键索引的原理和用处
 我们懂得Oracle会积极为表的主键列发生索引,这个默认的索引是等闲的B-Tree索引。对于主键值是按次序(递增或递减)加入的情形,默认的B-Tree索引并不志愿。这是因为万一索引列的值具有严厉次序时,随着数据行的插入,索引树的层级增长很快。搜查索引起生的I/O读写次数和索引树的层级数成正比,也即便说,一棵具有5个层级的B-Tree索引,在最后读取到索引数据时最多可能发生多达5次I/O垄断。因而,收缩索引的层级数是索引功能调剂的一个重要措施。
 万一索引列的数据以严厉的有序的措施插入,那么B-Tree索引树将变成一棵不对称的"歪树",如图 5所示:


图 5不对称的B-Tree索引


  而万一索引列的数据以随机值的措施插入,我们将获得一棵倾向对称的索引树,如图 6所示:


图 6对称的B-Tree索引

  比拟图 5和图 6,在图 5中搜查到A块必需举行5次I/O垄断,而图 6仅必需3次I/O垄断。

  既然索引列数据从序列中获得,其有序性无法令避,但在发生索引时,Oracle批准对索引列的值举行反向,即预先对列值举行比特位的反向,如1000,10001,10011,10111,1100穿越反向后的值将是0001,1001,1101,0011。显明穿越位反向处理的有序数据变得比拟随机了,这么所获得的索引树就比拟对称,从而长进表的查询功能。

  但反向键索引也有它局限性:万一在WHERE语句中,必需对索引列的值举行范围性的搜查,如BETWEEN、<、>等,其反向键索引无法利用,此刻,Oracle将厉行全表扫描;只有对反向键索引列举行 <> 和 = 的比拟垄断时,其反向键索引才会获得利用。

  2.2 反向键索引的SQL语句

  回到我们上面的例子,由于T_ORDER和T_ORDER_ITEM的主键值起源于序列,主键值是有严厉次序的,因而我们该当屏弃默认的Oracle所供给的索引,而批准显式为主键指定一个反向键索引的措施。

  ORDER_ID为T_ORDER表的主键,主键名为PK_ORDER,我们为ORDER_ID列上发生一个反向键索引IDX_ORDER_ID,并使PK_ORDER_ID利用这个索引,其SQL语句如下:


create table T_ORDER (
 ORDER_ID NUMBER(10) not null,鄂尔多斯
 CLIENT VARCHAR2(60),
 ADDRESS VARCHAR2(100),
 ORDER_DATE CHAR(8));
create unique index IDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;alter table T_ORDER add constraint PK_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;


  
要保证创立IDX_ORDER_ID的SQL语句在创立PK_ORDER主键的SQL语句之前,因为主键必需引用到这个反向键索引。

  由于主键列的数据是单一的,所感受IDX_ORDER_ID加上unique限量,使其成为单一型的索引。

 2.3 PowerdDesigner如何垄断

 1) 率先,必需为ORDER_ID列发生一个反向键索引。敞开T_ORDER的TableProperties的窗口,切换到Indexes页,修建一个名为IDX_ORDER_ID的索引。填写完索引的名目后,双击这个索引,弹出IndexProperties窗口,在这个窗口的Columns入抉择ORDER_ID列。然后,切换到Options页,按图7的措施将其设置为反向键索引。


图 7 设置反向键索引

 2) 显式指定主键PK_ORDER利用这个索引。在TableProperties窗口中切换到Keys页,默认情形下,PowerDesigner为T_ORDER所指定的主键名为Key1,我们将其更名为PK_ORDER,双击这个主键,弹出Key Properties窗口,切换到Options页,按图8的措施为PK_ORDER指定IDX_ORDER_ID。

图 8 为主键指定特定的索引

 不可抵赖PowerDesigner确乎是现在业界最壮大易用的数据库设计工具,但很缺憾,当我们为表主键指定一个索引时,其发生的语句在次序上有问题:即创立主键的语句位于创立索引语句之前:
createtable T_ORDER (…);alter table T_ORDER add constraint PK_T_ORDER primarykey (ORDER_ID) using index IDX_ORDER_ID;create unique indexIDX_ORDER_ID on T_ORDER ( ORDER_ID ASC) reverse;
  我们能够穿越对PowerDesigner生成SQL语句的设置举行调剂,先生成创立表和索引的SQL语句,再创立为表增加主键和外键的SQL语句来到达曲线救国的目标,请看下一步。

 3)穿越菜单Database->Generate Database...投放Database Configuration窗口,切换到Keys&Indexes页,按图 9设置:


图 9 设置生成键和索引SQL的选项
  这里,我们将Primary Keys和Foreign keys的选项都废止,而将Indexes勾选,以到达只生成表的索引SQL语句的目标。
  点击"确定"后,生成创立数据库表及其索引的SQL语句,运行该SQL创立数据库后,再按图 10设置生成为表增加主键和外键的SQL语句:


图 10 生成创立表主键和外键的SQL语句
  除此设置外,还定然切换到Tables & Views页下,废止所有选项,避免重新生成创立表的语句。

则只有WHERE语句上包括COL_1(复合索引的第一个字段)的查询才会利用这个复合索引,而未包括COL_1的查询则不会利用这个复合索引。
  回到我们的例子,如何发生中意CLIENT + ORDER_DATE + IS_SHIPPED和ORDER_DATE + IS_SHIPPED两种查询的索引呢?
  琢磨到IS_SHIPPED列基数很小,只有两个可能的值:0,1。在这种情形下,有两种计划:第一,离别为CLIENT + ORDER_DATE+ IS_SHIPPED和ORDER_DATE +IS_SHIPPED发生一个复合索引;第二,离别在CLIENT和ORDER_DATE列上发生一个索引,而IS_SHIPEED列不发生索引。

  第一种计划的查询效率最快,但因为CLIENT和ORDER_DATE在索引中会重复揭示两次,挪借较大的存储空间。第二种计划CLIENT和ORDER_DATE不会在索引存储揭示两次,较为勤俭空间,查询效率比之于第一种计划会稍低一些,但波及不大。

  我们批准第二种计划为CLIENT和ORDER_DATE离别创立索引IDX_CLIENT和IDX_ORDER_DATE,组合查询条件为CLIENT + ORDER_DATE + IS_SHIPPED时的厉行计划为:
SELECTSTATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER'AND-EQUAL INDEX (RANGE SCAN) OF 'IDX_CLIENT' (NON-UNIQUE) INDEX (RANGESCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)
  而组合条件为ORDER_DATE + IS_SHIPPED时的厉行计划为:
SELECTSTATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER'INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)
  穿越这么的改革,我们获得了一个中意两种组合查询的厉行计划。

 归纳
  连贯本文的订单主从表实例构造上很容易,然而其粗劣的设计包括了众多问题,这也是众多对Oracle物理存储构造未曾很好会意的数据库设计师轻率疏忽的地方。
  在等闲理况下,这么的设计并不会导致严重系统的功能问题,然而锦上添花是每一位良好软件设计师的功德,另外,对于设计师,定然要打听这么一条规律:对于等质的功能晋级,在编码层面经常必需比设计层面付出更多的艰难。

  在Oracle中长进数据库的功能必需琢磨的问题,当心的误区还许多,本文涵盖是一些最常见的问题。下面,我们将长进数据库垄断功能措施及一些误区作个小结:
  ·对于大表,能够琢磨创立分区表,分区表有范围分区、散列分区、列表分区和散列分区几种,穿越它能够到达化大表为小表的目标。
  ·琢磨适量的数据冗余,如一个业务表有一个审批事态,审批必需穿越多步,每一步对应审批表的一条登记,最后审批的那条登记定夺了业务的事态。我们大可在业务表中储藏一个审批事态的符号,以废止每次必需穿越关系审批表获得业务审批事态的混杂的关系表查询。
  ·不要做太多的关系表查询,一些几乎不发生数据改变的表码表,如性别,学历,婚姻事态等表码表,能够琢磨在利用过程启用时顺次性地下载到利用过程的内存中缓存起来,在从数据库获得收获集后,再由过程利用这些缓存的表码表数据来翻译这些表码字段,而不要在数据库中穿越表间的关系查询措施来翻译这些字段。
  ·常看到一些令我瞠目标设计:在必需举行频繁DML(INSERT,助听器UPDATE,DELETE)垄断的表的某些基数低的字段(如性别,婚姻事态)上创立位图索引。位图索引是好东西,但它是利于用范围的,在OLTP系统中,必需举行频繁DML垄断的表中不该当揭示位图索引,位图索引只实用于几乎不举行DML垄断,只举行查询的DSS系统中。另外,聚簇和索引组织表也都更合乎DSS系统,而非OLTP系统。然而这段代码包括额外的String,并非全面必需。更好的代码为:

为何用环境变量而无须相对路径来包括第三方库.

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/22272235/viewspace-1116491/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-08-05

最新文章