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

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

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

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


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


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


　　压缩型的索引消除了重复的索引值，将相同索引列值所关联的ROWID存储在一起。这样，不但节省了存储空间，查询效率也提高了，真可谓两全齐美了。

　　对象T_ORDER和T_ORDER_ITEM这样的主从表进行查询时，一般情况下，我们都必须通过外键查询出子表所有关联的记录，所以在子表的外键上建立压缩型的索引是非常适合的。

　　3.2 压缩型索引的SQL语句

　　创建压缩型索引的SQL语句非常简单，在T_ORDER_ITEM的ORDER_ID上创建压缩型索引的SQL如下所示：


　　需要在创建索引的语句后附上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将索引设置为压缩型：


　　4、建立满足需求的复合键索引

　　设计人员希望通过T_ORDER表上的IDX_ORDER_COMPOSITE复合索引满足以下两种组合条件的查询：

　　·CLIENT + ORDER_DATE + IS_SHIPPED

　　·ORDER_DATE + IS_SHIPPED

　　为方便阐述，我们特地将IDX_ORDER_COMPOSITE的创建SQL语句再次列出：


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

　　可以用许多工具来了解查询语句的执行计划，通过SET AUTOTRACE ON来查询以上两个复合查询的执行计划：

　　打开SQL/Plus，输入以下的语句：


　　分析得到的执行计划为：


　　可见Oracle先利用IDX_ORDER_COMPOSITE得到满足条件的记录ROWID，再通过ROWID返回记录。

　　而下面查询语句：


　　的执行计划则为：


　　很明显，Oracle在T_ORDER表上执行了一个全表扫描的操作，没有用到IDX_ORDER_COMPOSITE索引。

　　对复合列索引，我们得出这个结论：

假设在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时的执行计划为：


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


　　通过这样的改造，我们得到了一个满足两种组合查询的执行计划。