数据库分区与索引的深入解析

### 数据库分区与索引的深入解析 #### 1. 分区概述 分区是数据库管理中的重要技术，它可以提高数据库的性能、可用性和可管理性。常见的分区方案有范围分区、哈希分区、列表分区、间隔分区、引用分区、间隔引用分区、虚拟列分区和复合分区。 #### 2. 分区创建示例 以下是一个分区表创建的示例，最终会得到五个分区，其中 PART_1 有两个子分区，PART_2 有三个子分区： ```sql PARTITION part_2 VALUES LESS THAN(to_date('01/01/2015','dd/mm/yyyy')) ( subpartition part_2_sub_1 values ( 1, 3 ), subpartition part_2_sub_2 values ( 5, 7 ), subpartition part_2_sub_3 values ( 2, 4, 6, 8 ) ) / ``` #### 3. 行移动 当修改用于确定分区的列的值时，有两种情况需要考虑： - **不导致分区改变**：这种修改在所有情况下都被支持，行仍会留在当前分区。 - **导致分区改变**：只有在表启用了行移动功能时才支持，否则会引发错误。 下面通过一个范围分区表 `range_example` 来演示行移动的情况： ```sql -- 创建范围分区表 CREATE TABLE range_example ( range_key_column date, data varchar2(20) ) PARTITION BY RANGE (range_key_column) ( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2014','dd/mm/yyyy')), PARTITION part_2 VALUES LESS THAN (to_date('01/01/2015','dd/mm/yyyy')) ); -- 插入数据 insert into range_example ( range_key_column, data ) values ( to_date( '15-dec-2013 00:00:00', 'dd-mon-yyyy hh24:mi:ss' ), 'application data...' ); insert into range_example ( range_key_column, data ) values ( to_date( '01-jan-2014 00:00:00', 'dd-mon-yyyy hh24:mi:ss' )-1/24/60/60, 'application data...' ); -- 查询 PART_1 分区的数据 select * from range_example partition(part_1); -- 修改数据使其仍在 PART_1 分区 update range_example set range_key_column = trunc(range_key_column) where range_key_column = to_date( '31-dec-2013 23:59:59', 'dd-mon-yyyy hh24:mi:ss' ); -- 修改数据使其移动到 PART_2 分区（未启用行移动，会报错） update range_example set range_key_column = to_date('01-jan-2014','dd-mon-yyyy') where range_key_column = to_date('31-dec-2013','dd-mon-yyyy'); -- 启用行移动 alter table range_example enable row movement; -- 再次尝试修改数据使其移动到 PART_2 分区 update range_example set range_key_column = to_date('01-jan-2014','dd-mon-yyyy') where range_key_column = to_date('31-dec-2013','dd-mon-yyyy'); ``` 需要注意的是，启用行移动会导致行的 ROWID 发生变化，并且行移动的内部操作相当于删除并重新插入该行，会更新表上的所有索引，比普通的更新操作更昂贵。因此，不建议频繁修改分区键并导致分区移动。 #### 4. 不同分区方案的特点 | 分区方案 | 适用场景 | 分区消除情况 | | ---- | ---- | ---- | | 范围分区 | 数据按某些值逻辑分隔，如时间数据（销售季度、财政年度、月份等） | 在很多情况下可利用分区消除，包括精确相等和范围查询 | | 哈希分区 | 数据没有自然的范围可用于分区，选择唯一或几乎唯一的列进行哈希 | 精确相等或使用 IN 操作时可利用分区消除，范围查询时不行 | | 列表分区 | 数据有离散值的列，且按该列分区对应用有意义，如州或地区代码 | 易于在查询中实现分区消除 | | 间隔分区 | 扩展范围分区功能，当插入的数据不适合现有分区时自动添加分区 | 继承范围分区的分区消除特性 | | 引用分区 | 便于实现通过引用完整性约束关联的分区表，子表与父表逻辑分区方式相同 | 依赖父表分区消除 | | 间隔引用分区 | 结合间隔和引用分区功能，Oracle 12c 新增 | 结合两者分区消除特性 | | 虚拟列分区 | 可使用虚拟列作为分区键，灵活分区 | 取决于虚拟列的使用方式 | | 复合分区 | 范围分区后结果分区仍过大，可进一步使用哈希、列表或范围分区 | 可在三个级别实现分区消除 | 建议在有合适的范围可用于分区时，优先使用范围分区。当范围分区结果过大或需要利用并行操作时，可在范围分区内使用哈希或列表分区。 #### 5. 索引分区 索引也可以进行分区，主要有两种方法： - **本地索引**：与表进行等分区，每个表分区对应一个索引分区，索引分区中的所有条目指向单个表分区，单个表分区中的所有行在单个索引分区中表示。 - **全局索引**：按范围或哈希对索引进行分区，单个索引分区可指向任何（和所有）表分区。 以下是本地索引和全局索引的对比：