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

3、将子表的外键列的索引改为压缩型

　　3.1 压缩型索引的原理和用途

　　在前面的例子中，由于一条订单会对应多条订单条目，所以T_ORDER_ITEM的ORDER_ID字段总会出现重复的值，如：

ITEM_ID ORDER_ID ITEM COUNT 1 100 101 1 2 100 104 2 3 100 201 3 4 200 301 2 5 200 401 1 6 200 205 3

　　在ORDER_ID列上创建一个普通未压缩的B-Tree索引，则索引数据的物理上的存储形式如下：

图 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 }

　　则只有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时的执行计划为：

SELECT STATEMENT Optimizer=CHOOSE TABLE ACCESS (BY INDEX ROWID) OF 'T_ORDER' AND-EQUAL INDEX (RANGE SCAN) OF 'IDX_CLIENT' (NON-UNIQUE) INDEX (RANGE SCAN) OF 'IDX_ORDER_DATE' (NON-UNIQUE)

　　而组合条件为ORDER_DATE + IS_SHIPPED时的执行计划为：

SELECT STATEMENT 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系统。

查看本文来源