前言
绝大多数的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 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索引。
优化设计
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) 为每个索引指定索引数据的存储表空间。在Table Properties中切换到Indexes页，在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的Index Properties窗口中切换到Options页，按如下方式指定索引的存储表空间。 前言
绝大多数的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 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索引。
优化设计
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) 为每个索引指定索引数据的存储表空间。在Table Properties中切换到Indexes页，在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的Index Properties窗口中切换到Options页，按如下方式指定索引的存储表空间。 前言
绝大多数的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 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索引。
优化设计
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) 为每个索引指定索引数据的存储表空间。在Table Properties中切换到Indexes页，在这里列出了表的所有索引，双击需设置表空间的索引，在弹出的Index Properties窗口中切换到Options页，按如下方式指定索引的存储表空间。
