往期回顾：

在数据库性能优化的实践中，我们经常遇到这样的困境：即使查询条件已经优化到极致，索引策略也已经完善，但某些特定的查询模式仍然存在性能瓶颈。特别是当查询既需要快速定位特定数据，又要求结果按某种顺序返回时，传统的表设计往往难以兼顾这两个需求。此时，我们需要将目光投向更深层次的设计特性——表的物理存储结构本身。

Oracle数据库提供的Cluster（聚族）技术，特别是有序散列聚族表（Sorted Hash Cluster），为解决这类问题提供了独特的解决方案。这种设计特性不仅改变了数据的物理存储方式，更从根本上重新定义了数据访问的执行路径。当相同的查询语句面对不同类型的表结构时，其执行计划会发生怎样的变化？排序操作能否被完全避免？本文将通过实际案例深入探讨这一关键问题。

传统表设计的性能瓶颈

在传统的表设计中，数据按照插入顺序或随机方式存储，当查询需要特定顺序的结果时，排序操作几乎不可避免。让我们首先观察传统表在面对排序查询时的表现。

普通表的测试环境

-- 创建传统普通表
drop table t;
CREATE TABLE t
(  cust_id       number,
   order_dt      timestamp,
   order_number  number,
   username      varchar2(30),
   ship_addr     number,
   bill_addr     number,
   invoice_num   number
);
set linesize 1000
set pagesize 2000
set autotrace traceonly 
variable x number

普通表的查询执行

-- 语句1：普通表的排序查询
select cust_id, order_dt, order_number
  from t
 where cust_id = :x
 order by order_dt;
执行计划：
Plan hash value: 961378228
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    39 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |     1 |    39 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |     1 |    39 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
统计信息：
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

从执行计划可以清晰看到，普通表必须执行SORT ORDER BY操作，即使数据量很小，排序步骤仍然不可避免。这种设计在数据量增大时会成为显著的性能瓶颈。

有序散列聚族表的革命性设计

有序散列聚族表通过将数据按照指定的散列键和排序键进行物理存储，实现了数据的预排序。这种设计理念的核心在于"空间换时间"——通过在存储阶段投入额外的组织成本，换取查询阶段的性能提升。

聚族表的创建与配置

set autotrace off
drop table t;
drop cluster shc;
-- 创建有序散列聚族
CREATE CLUSTER shc
(
   cust_id     NUMBER,
   order_dt    timestamp SORT
)
HASHKEYS 10000
HASH IS cust_id
SIZE  8192
/
-- 在聚族上创建表
CREATE TABLE t
(  cust_id       number,
   order_dt      timestamp SORT,
   order_number  number,
   username      varchar2(30),
   ship_addr     number,
   bill_addr     number,
   invoice_num   number
)
CLUSTER shc ( cust_id, order_dt )
/

这个聚族定义包含了几个关键要素：

- 散列键：cust_id用于快速定位数据块

- 排序键：order_dt SORT确保同一散列值内的数据按时间排序

- 散列桶数量：HASHKEYS 10000定义了散列空间的大小

- 块大小：SIZE 8192指定了每个散列桶的预期大小

聚族表的查询执行

set autotrace traceonly 
variable x number
-- 语句2：聚族表的相同查询
select cust_id, order_dt, order_number
  from t
 where cust_id = :x
 order by order_dt;
执行计划：
Plan hash value: 2719348108
---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T    |     1 |    39 |            |
---------------------------------------------------------------
统计信息：
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        416  bytes sent via SQL*Net to client
        404  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

执行计划的根本性变化分析

通过对比两种表设计的执行计划，我们发现了革命性的变化：

排序操作的完全消除

最显著的变化是聚族表的执行计划中完全没有了SORT ORDER BY操作。这是因为数据在物理存储时已经按照order_dt进行了预排序，查询时直接按顺序读取即可，无需额外的排序步骤。

访问方式的优化升级

- 普通表：TABLE ACCESS FULL，需要扫描表数据然后排序

- 聚族表：TABLE ACCESS HASH，通过散列直接定位目标数据块

性能指标的全面提升

从统计信息对比可以看出：

- 排序操作：普通表需要1次内存排序，聚族表为0次

- 执行成本：普通表Cost为3且包含34%的CPU开销，聚族表Cost为0

- 访问效率：聚族表通过散列访问实现了近乎O(1)的查找复杂度

聚族技术的机制解析

散列分布机制

有序散列聚族通过散列函数将具有相同cust_id的记录分配到同一个物理块中。当查询指定cust_id时，系统可以通过散列计算直接定位到对应的数据块，避免了全表扫描。

块内排序机制

在每个散列桶内，数据按照order_dt SORT字段进行物理排序存储。这意味着当读取某个客户的订单数据时，这些数据天然就是按时间顺序排列的，无需额外的排序操作。

空间与时间的权衡

聚族设计需要预分配散列空间，可能导致一定的空间浪费。但这种"空间换时间"的策略在查询密集型应用中能够带来显著的性能收益。

适用场景与设计考量

理想应用场景

- 客户订单系统：经常需要查询特定客户的订单并按时间排序

- 时间序列数据：如股票交易记录、传感器数据等

- 分组聚合查询：需要按某个维度分组并在组内排序的场景

设计注意事项

- 散列键选择：应选择分布均匀且查询频繁的字段

- 排序键设计：通常选择时间字段或序号字段

- 散列桶数量：需要根据数据量和查询模式合理设置

- 维护成本考虑：聚族表的DML操作可能比普通表复杂

总结：设计特性的战略价值

通过本文的深入分析，我们清晰地看到了表设计特性对SQL执行计划产生的根本性影响。有序散列聚族表不仅仅是一种存储技术，更是一种设计哲学的体现——通过在设计阶段的深思熟虑，实现运行时的高效执行。

聚族设计实现了查询性能的质的飞跃，将原本需要排序的操作转化为直接的顺序读取，这种优化在大数据量场景下的价值更加明显。聚族技术体现了"预计算"的设计思想，这种理念在现代大数据架构中得到了广泛应用，如列式存储、预聚合等技术都体现了类似的设计哲学。传统的性能优化往往聚焦于索引和SQL改写，而聚族设计为我们开辟了全新的优化维度——通过改变数据的物理组织方式来提升查询效率。

很显然，不同的表设计特性会从根本上改变SQL的执行计划。在面对特定的查询模式时，选择合适的表设计特性往往比后期的调优工作更加有效。聚族技术提醒我们，优秀的数据库设计不仅要考虑数据的逻辑结构，更要深入思考数据的物理组织方式，让架构设计的智慧在每一次查询中发挥价值。

未完待续..

左右SQL执行计划妙招 ⑬ ——执行计划利用设计特性改变【Iot表类型句】

公众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

左右SQL执行计划妙招 ⑫ ——执行计划利用设计特性改变【Cluster类型】