当前位置：首页 > 技能相关 > ORACLE > 正文内容

ORACLE 创建分区表(Range模式)

admin1年前 (2024-09-21)ORACLE6900 修订时间：2024-09-21 10:05:07

什么是范围分区（Range）？

根据预先定义的范围来划分分区，范围分区最适合管理类似且有明显顺序的数据，根据数据的顺序可以很容易划定分区范围。

范围分区表是通过：

create table 语句的 partition by range 子句来创建的；

分区的范围通过 values less than 子句指定；

其指定的是分区的上限（不包含），所有大于等于指定值的数据被分配至下一个分区，除了第一个分区，每个分区的下限即前一个分区的上限：

create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')),
 partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')),
 partition pmax values less than (maxvalue)
);

上面的例子中定义了3个分区：

所有create_time小于'2023-02-01'的数据（不包含）被分配在分区p1中。
所有create_time小于'2023-03-01'的数据（不包含），大于'2023-02-01'的数据（包含）被分配在p2中。
所有create_time大于等于'2023-03-01'的数据被分配在pmax中，如果没有这个分区，那么插入大于等于'2023-03-01'的数据时，会因为没有合适的存储分区而报错。

你也可以在定义分区时指定存储特性，例如将分区分散到不同的表空间（表空间可以放到不同的物理磁盘上）：

create tablespace tbs1;
create tablespace tbs2;
create tablespace tbs3;
create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1,  -- 指定分区p1放在tbs1中
 partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')) tablespace tbs2,
 partition pmax values less than (maxvalue) tablespace tbs3
);

拓展：间隔分区（Interval partition）-> 推荐使用

间隔分区是范围分区的一个扩展，它也是通过范围来划分分区，唯一的区别是：间隔分区可以在相应分区数据插入时自动创建分区，省去了普通范围分区手动创建分区的操作。

如果不是需要创建不规则的范围分区，那么更推荐使用间隔分区来替代范围分区，你只需要指定一个分区间隔及初始分区，后续的分区创建将由Oracle自动完成。

间隔分区表的创建由在普通范围分区定义上新增一个interval子句创建：

create table inv_part (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))     -- 指定分区间隔
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);

上面的例子指定在初始分区p1的基础上，每隔1个月创建一个分区。

通过视图user_tab_partitions可以看到目前只有1个分区p1:

select table_name, partition_name from user_tab_partitions where table_name='INV_PART';

我们在初始分区的上限之上插入一条数据：

insert into inv_part values(1, 'Vincent', date '2023-02-02');
commit;
select table_name, partition_name from user_tab_partitions where table_name='INV_PART';

在现有分区之上插入数据时，Oracle自动为我们创建了1个对应的分区SYS_P327。

对于间隔分区，你也可以通过 store in 子句指定多个表空间，Oracle将以循环的方式在各个表空间中创建分区。

create table multi_tbs (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))  store in (tbs1, tbs2, tbs3)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1
);

查询初始分区的所属表空间：

select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';

插入两条数据，触发自动创建新的分区：

insert into multi_tbs values(1, 'Vincent', date '2023-02-02');
insert into multi_tbs values(2, 'Victor', date '2023-03-02');
commit;
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';

可以看到Oracle自动以循环的方式在3个表空间中创建了分区。