0%

oracle 11g 的表分区

发表于 分类于 阅读次数： Valine：
本文字数： 12k 阅读时长 ≈ 10 分钟

1. 概念

  • 表分区允许用户将一个表分为多个分区；
  • 用户可以执行查询，只访问表中的特定分区；
  • 将不同的分区存储在不同的磁盘，提高访问性能和安全性；
  • 可以独立地备份和恢复每个分区。

2. 传统的表分区

传统表分区指的是 oracle 10g 及之前版本的表分区。传统的表分区有四种类型，分别为范围分区、散列分区、列表分区和复合分区四种。我们可以使用如下语句查看指定表的分区信息：

1
select * from user_tab_partitions u where u.table_name = '全大写表名';

2.1 范围分区

2.1.1 含义

范围分区是以表中的一个列或一组列的值的范围分区。范围分区的缺点很明显，即可能存在大量相同范围的记录存在特定的分区的现象，从而导致分区分配不均的情况。

2.1.2 语法

语法如下：

1
2
3
4
5
6
7
PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUES LESS THAN(range1),
  PARTITION part2 VALUES LESS THAN(range2),
  ...
  [PARTITION partN VALUES LESS THAN(MAXVALUE)]
);

2.1.3 示例

创建一个产品表，按照价格字段 price 的范围分为三个分区，具体如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 创建表并分区
create table product(pno number, price number)
partition by range(price) (
    partition p1 values less than(1000), -- 存储 price < 1000 的记录
    partition p2 values less than(2000), -- 存储 price >= 1000 and price < 2000 的记录
    partition p3 values less than(maxvalue) -- 存储 price >= 2000 的记录
);

-- 插入测试数据
insert into product values(1, 200);
insert into product values(2, 1000);
insert into product values(3, 2020);

-- 分区查询结果
SQL> select * from product partition(p1);

       PNO      PRICE
---------- ----------
         1        200

SQL> select * from product partition(p2);

       PNO      PRICE
---------- ----------
         2       1000

SQL> select * from product partition(p3);

       PNO      PRICE
---------- ----------
         3       2020

提示：对表建立分区之后，查询语句会发生变化，即要对某个分区进行查询的时候需要在后面带 partition；而对于更新删除操作则仍然保持普通的写法。

2.2 散列分区

2.2.1 含义

散列分区允许用户对不具有逻辑范围的数据进行分区，通过在分区键上执行HASH函数决定存储的分区。不同于范围分区，散列分区可以有效地避免分区数据分配不均的情况发生，散列分区可以将数据平均地分布到不同的分区中。

2.2.2 语法

1
2
3
4
5
6
7
8
9
10
-- number_of_partitions 表示分区数量
PARTITION BY HASH (column_name)
PARTITIONS number_of_partitions;
或
PARTITION BY HASH (column_name)
( PARTITION part1 [TABLESPACE tbs1],
  PARTITION part2 [TABLESPACE tbs2],
  ...
  PARTITION partN [TABLESPACE tbsN]
);

2.2.3 示例

创建一个 person 表，按人员的姓名分为四个散列分区，具体如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
-- 创建散列分区，仅指定分区数量
create table person(name varchar2(50), age number)
partition by hash(name)
partitions 4;

-- 创建散列分区，指定具体分区
create table person(name varchar2(50), age number)
partition by hash(name) (
    partition p1,
    partition p2,
    partition p3,
    partition p4
)

-- 插入测试数据
insert into person values('张三', 22);
insert into person values('李四', 36);
insert into person values('王五', 45);
insert into person values('赵六', 12);

-- 分区查询结果
SQL> select * from person partition(p1);

NAME                                                      AGE
-------------------------------------------------- ----------

SQL> select * from person partition(p2);

NAME                                                      AGE
-------------------------------------------------- ----------
张三                                                       22
王五                                                       45

SQL> select * from person partition(p3);

NAME                                                      AGE
-------------------------------------------------- ----------
李四                                                       36

SQL> select * from person partition(p4);

NAME                                                      AGE
-------------------------------------------------- ----------
赵六                                                       12

2.3 列表分区

2.3.1 含义

列表分区允许用户将不相关的数据组织在一起。列表分区支持对字段的具体值进行分区，不同于范围限定一个范围，列表分区是限定在具体的值。

2.3.2 语法

1
2
3
4
5
6
7
PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);

2.3.3 示例

创建一个 person 表，并按地市字段 city 对表进行分区，具体如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
-- 创建表分区
create table person(name varchar2(50), city varchar2(20))
partition by list(city)(
    partition p1 values ('广州'),
    partition p2 values ('深圳'),
    partition p3 values (default)
);

-- 插入测试数据
insert into person values('李四', '深圳');
insert into person values('李四', '广州');
insert into person values('李四', '南昌');

-- 查询分区数据信息
SQL> select * from person partition(p1);

NAME                                               CITY
-------------------------------------------------- --------------------
李四                                               广州

SQL> select * from person partition(p2);

NAME                                               CITY
-------------------------------------------------- --------------------
李四                                               深圳

SQL> select * from person partition(p3);

NAME                                               CITY
-------------------------------------------------- --------------------
李四                                               南昌

2.4 复合分区

2.4.1 含义

复合分区指的是范围分区与散列分区或列表分区的组合，即复合只存在两种组合方式：范围分区 + 散列分区与范围分区 + 列表分区。

2.4.2 语法

1
2
3
4
5
6
7
8
9
10
-- number_of_partitions 表示子分区数量
PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUES LESS THAN(range1),
  PARTITION part2 VALUES LESS THAN(range2),
  ...
  PARTITION partN VALUES LESS THAN(MAXVALUE)
);

2.4.3 示例

创建一个 student 表并进行复合分区，先按 sno 进行范围分区，在对 sname 进行散列分区，具体如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
-- 创建复合分区
create table student(sno number, sname varchar2(50))
partition by range(sno)
subpartition by hash(sname)
subpartitions 4 (
    partition p1 values less than(1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(maxvalue)
);

-- 插入测试数据
insert into student values(20, '李四');
insert into student values(2020, '张三');
insert into student values(3020, '王五');

-- 查询分区数据
SQL> select * from student partition(p1);

       SNO SNAME
---------- --------------------------------------------------
        20 李四

SQL> select * from student partition(p2);

       SNO SNAME
---------- --------------------------------------------------

SQL> select * from student partition(p3);

       SNO SNAME
---------- --------------------------------------------------
      2020 张三

SQL> select * from student partition(p4);

       SNO SNAME
---------- --------------------------------------------------
      3020 王五

3. 11g 新增表分区

3.1 引用分区

3.1.1 含义

引用分区是基于由外键引用父表的分区的方法，它依赖已有的父表子表的关系，子表通过外键关联到父表，进而继承了父表的分区方式而不需自己创建，子表还继承了父表的维护操作。

主表是无论是范围、散列还是列表分区，子表都是引用分区。

3.1.2 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- 创建父表，并按名称创建两个分区
create table father(
    fid number primary key,
    fname varchar2(50)
)
partition by hash(fname) (
    partition p1,
    partition p2
);

-- 创建子表，并设置外键
create table son(
    sid number primary key,
    sname varchar2(50),
    fid number not null, -- 引用的外键必须非空，否则报 ORA-14652 错误
    constraint fk_fid foreign key(fid) references father(fid)
)
partition by reference(fk_fid); -- 设置引用分区，不可或缺

-- 查询分区定义信息，可以发现 SON 表也存在两个和父表一样的分区
SQL> select TABLE_NAME, COMPOSITE, PARTITION_NAME  from user_tab_partitions;

TABLE_NAME                     COMPOSITE PARTITION_NAME
------------------------------ --------- ------------------------------
FATHER                         NO        P1
FATHER                         NO        P2
SON                            NO        P1
SON                            NO        P2

-- 插入测试用例
insert into father values(1, '张三');
insert into father values(2, '李四');
insert into father values(3, '王五');

insert into son values(11, '张三三', 1);
insert into son values(22, '李四四', 2);
insert into son values(33, '王五五', 3);

-- 查询分区数据信息
SQL> select * from father partition(p1);

       FID FNAME
---------- --------------------------------------------------
         2 李四

SQL> select * from father partition(p2);

       FID FNAME
---------- --------------------------------------------------
         1 张三
         3 王五

SQL> select * from son partition(p1);

       SID SNAME                                                     FID
---------- -------------------------------------------------- ----------
        22 李四四                                                      2

SQL> select * from son partition(p2);

       SID SNAME                                                     FID
---------- -------------------------------------------------- ----------
        11 张三三                                                      1
        33 王五五                                                      3

3.2 间隔分区

3.2.1 含义

间隔分区可以完全自动地根据间隔阈值创建范围分区，它是范围分区的扩展 。间隔分区在数据仓库中有广泛的应用。

3.2.2 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 创建间隔分区
create table sale_detail (
    sale_detail_id number,
    product_id number,
    quantity number,
    sale_date date
)
partition by range(sale_date) -- 对 sale_date 进行范围分区
interval (numtoyminterval(1, 'MONTH')) -- 对范围分区进行扩展变为间隔分区，自定义扩展规则，即按月份每次扩展一个分区
(
    -- 自定义的初始分区，所有小于 20200601 日期的记录都存在这个分区中
    partition p_201006 values less than (to_date('20200601', 'yyyymmdd'))
);

-- 查询分区信息，此时可以发现只有一个初始自定义分区
SQL> select TABLE_NAME, PARTITION_NAME, HIGH_VALUE from user_tab_partitions where table_name = 'SALE_DETAIL';

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
SALE_DETAIL                    P_201006                       TO_DATE(' 2020-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

-- 插入测试数据
insert into sale_detail values (1, 11, 10, to_date('20200501', 'yyyymmdd')); -- 此时只有 p_201006 分区
insert into sale_detail values (2, 22, 20, to_date('20200711', 'yyyymmdd')); -- 此时 oracle 自动创建一个上界为 202007801 的分区
insert into sale_detail values (3, 33, 30, to_date('20200805', 'yyyymmdd')); -- 此时 oracle 自动创建一个上界为 202000901 的分区
insert into sale_detail values (3, 33, 30, to_date('20200803', 'yyyymmdd')); -- 存储在上界为 202000901 的分区
insert into sale_detail values (4, 44, 40, to_date('20201212', 'yyyymmdd')); -- 此时 oracle 自动创建一个上界为 20210101 的分区
insert into sale_detail values (5, 55, 50, to_date('20200913', 'yyyymmdd')); -- 此时 oracle 自动创建一个上界为 202001001 的分区

3.3 虚拟列分区

3.3.1 含义

基于虚拟列的分区：把分区建立在某个虚拟列上，即建立在函数或表达式的计算结果上，来完成某种任务。

3.3.2 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 创建表，并定义虚拟列，根据虚拟列进行分区
create table sale(
    sale_id number primary key,
    product_id number,
    price number,
    quantity number,
    sale_date date,
    total_price as (price * quantity) virtual -- 创建虚拟列
)
partition by range(total_price)(
    partition p_1000 values less than(1000),
    partition p_2000 values less than(2000),
    partition p_max values less than(maxvalue)
);

-- 插入测试数据
insert into sale(sale_id, product_id, price, quantity, sale_date) values(1, 11, 10, 5, sysdate);
insert into sale(sale_id, product_id, price, quantity, sale_date) values(2, 22, 2000, 5, sysdate);

3.4 系统分区

3.4.1 含义

系统分区不指定分区列，由ORACLE来完成分区的控制和管理，它没有了范围分区或列表分区的界限。

3.4.2 示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建系统分区表
create table person (
    id number,
    name varchar2(20),
    address varchar2(20)
)
partition by system (
    partition p1, partition p2, partition p3
)

-- 插入测试数据时，需要指明插入的分区
insert into person partition(p1) values (1, '张三', '北京');
insert into person partition(p2) values (2, '李四', '广州');
insert into person partition(p3) values (3, '王五', '南宁');

4. 维护分区

4.1 添加分区

在最后一个分区之后添加新分区

1
2
-- 向 sales 表添加一个 p4分区
ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);

4.2 删除分区

删除一个指定的分区，分区的数据也随之删除，不保留分区结构

1
2
-- 删除 sales 表的 p4 分区
ALTER TABLE SALES DROP PARTITION P4;

4.3 截断分区

删除指定分区中的所有记录，保留分区结构

1
2
-- 截断 sales 表的 p3 分区
ALTER TABLE SALES TRUNCATE PARTITION P3;

4.4 合并分区

将范围分区或复合分区的两个相邻分区连接起来

1
2
-- 将 sales 表的 s1、s2 分区合并为 s2 分区
ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;

4.5 拆分分区

将一个大分区中的记录拆分到两个分区中

1
2
-- 已 1500 为临界将 sales 表的 p2 拆分为 p21 和 p22 两个分区
ALTER TABLE SALES SPLIT PARTITION P2 AT (1500) INTO (PARTITION P21, PARTITION P22);
------ 本文结束------