科技行者

行者学院 转型私董会 科技行者专题报道 网红大战科技行者

知识库

知识库 安全导航

至顶网软件频道优化Oracle库表设计的若干方法

优化Oracle库表设计的若干方法

  • 扫一扫
    分享文章到微信

  • 扫一扫
    关注官方公众号
    至顶头条

  前言   绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的,只有少部分问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不合理,I/O争用,CPU争用等DBA职责范围上。

作者:中国IT实验室 来源:中国IT实验室 2007年10月7日

关键字: ORACLE

  • 评论
  • 分享微博
  • 分享邮件

在本页阅读全文(共3页)

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的Table Properties的窗口,切换到Indexes页,新建一个名为IDX_ORDER_ID的索引。填写完索引的名称后,双击这个索引,弹出Index Properties窗口,在这个窗口的Columns中选择ORDER_ID列。然后,切换到Options页,按图 7的方式将其设置为反向键索引。


图 7 设置反向键索引

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


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

  不可否认PowerDesigner确实是目前业界最强大易用的数据库设计工具,但很遗憾,当我们为表主键指定一个索引时,其产生的语句在顺序上有问题:即创建主键的语句位于创建索引语句之前:

create table T_ORDER (…);alter table T_ORDER add constraint PK_T_ORDER primary key (ORDER_ID) using index IDX_ORDER_ID;create unique index IDX_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页下,取消所有选项,避免重新生成创建表的语句。

查看本文来源

    • 评论
    • 分享微博
    • 分享邮件
    邮件订阅

    如果您非常迫切的想了解IT领域最新产品与技术信息,那么订阅至顶网技术邮件将是您的最佳途径之一。

    重磅专题
    往期文章
    最新文章