PowerDesigner优化Oracle库表设计

作者：佚名 2007-08-31

内容导航：

第1页：前言第2页：优化设计

图 11 未进行压缩的索引存储
　　ORDER_ID的重复值在索引块中重复出现，这样不但增加了存储空间的需求，而且因为查询时需要读取更多的索引数据块，所以查询性能也会降低＝。让我们来看一下经过压缩后索引数据的存储方式：

图 12 进行压缩的索引存储
　　压缩型的索引消除了重复的索引值，将相同索引列值所关联的ROWID存储在一起。这样，不但节省了存储空间，查询效率也提高了，真可谓两全齐美了。
　　对象T_ORDER和T_ORDER_ITEM这样的主从表进行查询时，一般情况下，我们都必须通过外键查询出子表所有关联的记录，所以在子表的外键上建立压缩型的索引是非常适合的。

　　3.2 压缩型索引的SQL语句
　　创建压缩型索引的SQL语句非常简单，在T_ORDER_ITEM的ORDER_ID上创建压缩型索引的SQL如下所示：
create index IDX_ORDER_ITEM_ORDER_ID on T_ORDER_ITEM ( ORDER_ID ASC) compress;
　　需要在创建索引的语句后附上compress关键字就可以了。

　　3.3 PowerDesigner如何创建压缩型索引
　　1) 打开T_ORDER_ITEM表的Table Properties的窗口，切换到Indexes页，为ORDER_ID列创建一个名为IDX_ORDER_ITEM_ORDER_ID的索引。
　　2) 双击IDX_ORDER_ITEM_ORDER_ID弹出Index Properties窗口，切换到Options页，按图 13将索引设置为压缩型：

图 13 将索引指定为压缩型

　4、建立满足需求的复合键索引
　　设计人员希望通过T_ORDER表上的IDX_ORDER_COMPOSITE复合索引满足以下两种组合条件的查询：
　　·CLIENT + ORDER_DATE + IS_SHIPPED
　　·ORDER_DATE + IS_SHIPPED
　　为方便阐述，我们特地将IDX_ORDER_COMPOSITE的创建SQL语句再次列出：
create index IDX_ORDER_COMPOSITE on T_ORDER ( CLIENT ASC, ORDER_DATE ASC, IS_SHIPPED ASC);

　　事实上，在CLIENT + ORDER_DATE + IS_SHIPPED 三列上所执行的复合条件查询会应用到这个索引，而在ORDER_DATE + IS_SHIPPED列上所执行的复合查询不会使用这个索引，因而将导致一个全表扫描的操作。

　　可以用许多工具来了解查询语句的执行计划，通过SET AUTOTRACE ON来查询以上两个复合查询的执行计划：
　　打开SQL/Plus，输入以下的语句：
SQL> set autotrace on
SQL> select * from t_order where CLIENT = '1' and ORDER_DATE='1' and IS_SHIPPED='1';
　　分析得到的执行计划为：
SELECT STATEMENT Optimizer=CHOOSETABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_COMPOSITE' (NON-UNIQUE)
　　可见Oracle先利用IDX_ORDER_COMPOSITE得到满足条件的记录ROWID，再通过ROWID返回记录。

　　而下面查询语句：
SQL> select * from t_order where ORDER_DATE='1' and IS_SHIPPED='1'
　　的执行计划则为：
SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'T_ORDER'
　　很明显，Oracle在T_ORDER表上执行了一个全表扫描的操作，没有用到IDX_ORDER_COMPOSITE索引。

　　对复合列索引，我们得出这个结论：
　　假设在COL_1,COL_2,…,COL_n这些列上建立了一个复合索引：
create index IDX _COMPOSITE on TABLE1
{
COL_1,
COL_2,
…,
COL_n
" >第3页：将子表的外键列的索引改为压缩型 }

【IT168 技术文档】

前言

　绝大多数的Oracle数据库性能问题都是由于数据库设计不合理造成的，只有少部分问题根植于Database Buffer、Share Pool、Redo Log Buffer等内存模块配置不合理，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 2 3

【内容导航】

第1页：前言

第2页：优化设计

第3页：　　分析得到的执行计划为：
SELECT STATEMENT Optimizer=CHOOSETABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' INDEX (RANGE SCAN) OF 'IDX_ORDER_COMPOSITE' (NON-UNIQUE)
　　可见Oracle先利用IDX_ORDER_COMPOSITE得到满足条件的记录ROWID，再通过ROWID返回记录。

　　而下面查询语句：
SQL> select * from t_order where ORDER_DATE='1' and IS_SHIPPED='1'
　　的执行计划则为：
SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (FULL) OF 'T_ORDER'
　　很明显，Oracle在T_ORDER表上执行了一个全表扫描的操作，没有用到IDX_ORDER_COMPOSITE索引。

　　对复合列索引，我们得出这个结论：
　　假设在COL_1,COL_2,…,COL_n这些列上建立了一个复合索引：
create index IDX _COMPOSITE on TABLE1
{
COL_1,
COL_2,
…,
COL_n
'> 将子表的外键列的索引改为压缩型 }

[责任编辑：cynthia]

进入论坛>>相关论坛贴