神通广大的分区表

最新推荐文章于 2025-06-14 22:32:06 发布

road_of_technology

最新推荐文章于 2025-06-14 22:32:06 发布

阅读量371

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/road_of_technology/article/details/104573535

本文深入解析Oracle数据库中的分区表概念，包括范围、列表、散列和组合分区等类型，探讨分区表的优势如高效分区消除、强大的分区操作，以及在实际应用中的注意事项。文章还介绍了分区表的陷阱，如索引失效和效率问题，并提供了相应的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

分区类型

范围分区、列表分区、散列分区、组合分区

下面示例以学生表日志表为例进行说明，学生日志表通过学校代码CODE做了范围分区，因为对应可用以下脚本进行查询

--查询当前用户下的分区信息
select * from USER_PART_TABLES;

--查询当前用户下的分区详细信息
select * from USER_TAB_PARTITIONS;

在百万级记录数据的分区表中，也是有额外开销的，如果分区数量过多，Oracle就需要管理过多的段，在操作分区表时也容易引发Oracle内部大量的递归调用，此外本身的语法也有一定的复杂度，所以一般来说，只有大表才建议建分区，记录数在100万以下的表，基本不建议建分区。

分区实用特性

高效的分区消除

分区表存在的最大意义就在于，可以有效的做到分区消除。因为分区表其实就是将一个大对象分成了多个小对象，可以带来性能上的大幅提升。
强大的分区操作

（1）分区truncate

delete无法释放空间，而truncate却有效地释放了空间。但是针对普通表而言，truncate往往不能轻易使用，因为delete往往针对某些条件的局部记录删除，而truncate显然不能带上条件，无法做到局部删除。

Oracle可以只truncate某个分区，这就等同于实现了局部删除。
```
--删除分区数据及空间
alter table T_STU_LOG truncate partotion PERSON_CODE1;
```
分区清理的方法，在有大量历史数据需要清理时，发挥着极其重要的作用，很多历史表、日志表都被设计为分区表，正是由于这个特性使得清理数据极其方便迅速，而且能有效释放空间。

（2）分区数据转移

关于分区表的历史记录的处理，其实是可以分为删除可转移两部分的，关于转移备份的方案，Oracle提供了一个非常棒的工具，就是分区交换，可以实现普通表和分区表的某个分区之间数据的相互交换，他们之间的转换非常快，基本上在瞬时就可以完成，实际上只是Oracle在内部数据字典做的一些小动作而已。

注意：两张表的字段必须是完全一样的
```
-- 分区PERSON_CODE1 的数据转移到T_STU_LOG_EX 表中

alter table T_STU_LOG exchange partition PERSON_CODE1 with table T_STU_LOG_EX ;
```
（3）分区切割

如果把数据都放在一个分区里，不符合分区的设计思想，可以再扩建出多个分区来

关键字：split、at和into
```
alter table T_STU_LOG split partition PERSON_CODE6 at (7) into 
(partition PERSON_CODE6,partition PERSON_CODE_MAX );
```
（4）分区合并

关键字：merge和into
```
alter table T_STU_LOG merge partitions partition partition PERSON_CODE6,partition PERSON_CODE_MAX into partiton PERSON_CODE_MAX;
```
（5）分区增与删
```
alter table T_STU_LOG add partition PERSON_CODE7 values less than  (7);
```
注意：最后一个分区是less than(maxvalue)的情况下，是不能追加分区的，只能SPLIT分割。因为要追加的分区界限比这个PERSON_CODE7还要低,显然不能允许

可以删除less than(maxvalue)的分区再新增即可

分区索引类型

全局索引:

全局索引和普通的建表索引方式无异，而局部索引需要增加local关键字
```
create index idx_part_tab on T_STU_LOG(PARID);

create index idx_part_tab_area on T_STU_LOG(PARID) local;
```
全局索引基本可以理解为普通索引

局部索引:其实就是针对各个分区所建的索引。全局索引好比一个大索引，而局部索引好比6个小索引。

分区表之相关陷阱
索引为何频频失效

分区表的分区操作（清除、转移、合并增删等）对局部索引一般都没有影响，但是对全局索性影响比较大，会使全局索引失效，需进行索引的重建。

Oracle在提供这些分区操作时提供了一个很有用的参数 update global indexes ,可以有效地避免全局索引失效，其实这个参数的本质动作是在分区操作做完后，暗暗执行了索引重建的工作。
```
--查看索引的状态（N/A - 分区索引，VAILD 有效全局索引，UNUSABLE 无效全局索引）
select index_name,status from user_indexes where index_name='IDX_STU_LOG';

-- 索引重建
alter index IDX_STU_LOG rebuild;

-- 关键字update global indexes可避免全局索引失效
alter table T_STU_LOG truncate partition PERSON_CODE1 update global indexes;
```
有索引效率反而低

在表中存在局部索引时，查询表数据应加上分区条件，否则导致效率反而低下

无法应用分区条件

大家在分区设计时，往往没有预先规划好如何应用分区，这是很不应该的。中心服务的常客表按照代理人进行分区，对常客的操作也是以代理人为前提进行操作。

select * from T_STU_LOG where STULOGID=243 and agentid=2;

--查询当前用户下的分区信息
select * from USER_PART_TABLES;
--查询当前用户下的分区详细信息
select * from USER_TAB_PARTITIONS;
select * from user_indexes where table_name like 'T_STU';
select * from user_segments where partition_name is not null;
select * from user_segments where segment_name LIKE 'RANGE%';