Oracle 分区表详解

DBA随笔记 2024-11-18

282

Oracle作为一款成熟的数据库软件产品，就提供了多种数据表存储结构。我们最常见的就是三种，分别为堆表（Heap Table）、索引组织表（Index Organization Table，简称为IOT）和聚簇表（Cluster Table）。其他类型的表：分区表、临时表、压缩表等

1.Heap Table是我们在Oracle中最常使用的数据表，也是Oracle的默认数据表存储结构。在Heap Table中，数据行是按照“随机存取”的方式进行管理。从段头块之后，一直到高水位线以下的空间，Oracle都是按照随机的方式进行“粗放式”管理。当一条数据需要插入到数据表中时，默认情况下，Oracle会在高水位线以下寻找有没有空闲的地方，能够容纳这个新数据行。如果可以找到这样的地方，Oracle就将这行数据放在空位上。注意，这个空位选择完全依“能放下”的原则，这个空位可能是被删除数据行的覆盖位。

如果Heap Table段的HWM下没有找到合适的位置，Oracle堆表才去向上推高水位线。在数据行存储上，Heap Table的数据行是完全没有次序之分的。我们称之为“随机存取”特征。

对Heap Table，索引独立段的添加一般可以有效的缓解由于随机存取带来的检索压力。Index叶子节点上记录的数据行键值和Rowid取值，可以让Server Process直接定位到数据行的块位置。

2. 聚簇（Cluster Table）是一种合并段存储的情况。Oracle认为，如果一些数据表更新频率不高，但是经常和另外一个数据表进行连接查询（Join）显示，就可以将其组织在一个存储结构中，这样可以最大限度的提升性能效率。对聚簇表而言，多个数据表按照连接键的顺序保存在一起。

通常系统环境下，我们使用Cluster Table的情况不太多。Oracle中的数据字典大量的使用聚簇。相比是各种关联的基表之间固定连接检索的场景较多，从而确定的方案。

IOT（Index Organization Table）同Cluster Table一样，IOT是在Oracle数据表策略的一种“非主流”，应用的场景比较窄。但是一些情况下使用它，往往可以起到非常好的效果。

简单的说，IOT区别于堆表的最大特点，就在于数据行的组织并不是随机的，而是依据数据表主键，按照索引树进行保存。从段segment结构上看，IOT索引段就包括了所有数据行列，不存在单独的数据表段。

3. 分区表

随着表中行数的增多，管理和性能影响也将随之增加。备份、恢复、对整个数据表的查询将花费更多时间。通过把一个表中的行分为几个部分，可以减少大型表的管理和性能问题，以这种方式划分表数据的方法称为对表的分区。

分区表的优势:

(1). 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度;
(2). 方便数据管理：因为分区表的数据存储在多个部分中，所以按分区加载和删除数据比在大表中加载和删除数据更容易；
(3). 方便备份恢复：因为分区比被分区的表要小，所以针对分区的备份和恢复方法要比备份和恢复整个表的方法多。

3.1 范围分区 Range Partitioning

3.1.1 概述

范围分区就是对数据表中的某个值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上。如根据序号分区，根据业务记录的创建日期进行分区等 (联通每个月的账单记录就用的分区表存储)。

3.1.2.2 语法

PARTITION BY RANGE (column_name)
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  [PARTITION partN VALUE LESS THAN(MAXVALUE)]
);

3.1.2.3 创建分区表

-- scott:
SQL>
create table sale (product_id varchar2(5), sales_count number(10,2))
partition by range(sales_count)
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000)
);

3.1.2.4 查看信息

SQL> select * from user_tab_partitions where table_name='SALE';
col table_name for a10
col high_value for a10
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='SALE';
TABLE_NAME  PARTITION_NAME  HIGH_VALUE
----------  --------------  ------------
SALE    P1        1000
SALE    P2        2000
SALE    P3        3000

3.1.2.5 插入数据

insert into sale values('1',600);
1 row created.
insert into sale values('2',1000);
1 row created.
insert into sale values('3',2300);
1 row created.
insert into sale values('4',6000);
SQL> insert into sale values('4',6000);
insert into sale values('4',6000)
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
commit;

3.1.2.6 查看范围分区数据

select * from sale partition(p1);
select * from sale partition(p2);

3.1.2.7 增加 maxvalue 分区

alter table sale add partition p4 values less than(maxvalue);
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name='SALE';
TABLE_NAME  PARTITION_NAME  HIGH_VALUE
----------  --------------  ------------
SALE      P1      1000
SALE      P2      2000
SALE      P3      3000
SALE      P4      MAXVALUE

3.1.2.8 插入新分区的值

SQL> insert into sale values('4',6000);
SQL> select * from sale partition(p4);
PRODU  SALES_COUNT
-----  -----------------
4    6000

3.1.2.9 查看段的分配

SQL> col segment_name for a15
SQL> col partition_name for a15
SQL> select segment_name,segment_type,partition_name from user_segments;
SEGMENT_NAME  SEGMENT_TYPE    PARTITION_NAME
------------  ------------------  --------------
DEPT      TABLE
EMP        TABLE
PK_DEPT      INDEX
PK_EMP      INDEX
PRODUCTS    TABLE
SALE      TABLE PARTITION    P1
SALE      TABLE PARTITION    P2
SALE      TABLE PARTITION    P3
SALE      TABLE PARTITION    P4
SALGRADE    TABLE

3.1.2.10 跨分区操作报错处理

默认情况下，如果对分区表的分区字段做超范围 (跨段 p1，p2，p3，p4) update 操作，会报错ORA-14402。如果一定要改，可以通过打开表的row movement
属性来完成。

SQL> select * from sale;
PRODU  SALES_COUNT
-----  ---------------
1    600
2    1000
3    2300
4    6000

3.1.2.10.1 跨分区更改失败

SQL> update sale set sales_count=1200 where sales_count=600;
update sale set sales_count=1200 where sales_count=600
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change
-- ORA-14402:   更新分区关键字列将导致分区的更改

3.1.2.10.2 查询要更改数值当前 rowid

SQL> select rowid,t1.* from sale partition(p1) t1;
ROWID        PRODU  SALES_COUNT
------------------  ------  ------------
AAAXWQAAGAAAAVvAAA  1    600

3.1.2.10.3 使能 row movement

SQL> alter table sale enable row movement;
SQL> update sale set sales_count=1200 where sales_count=600;
已更新 1 行.
SQL> select rowid,t1.* from sale partition(p2) t1;
ROWID        PRODU  SALES_COUNT
------------------  ------  ------------
AAASvVAAEAAAAGdAAA  2    1000
AAAXWRAAGAAAAlvAAB  1    1200

一般来说范围分区的分区字段使用数字类型或日期类型

3.1.3 散列分区 Hash Partitioning (也叫 hash 分区)

3.1.3.1 概述

散列分区通过在分区键值上执行一个散列函数来决定数据的物理位置。(散列分区是根据字段的hash值进行均匀分布，尽可能的实现各分区所散列的数据相等)。
在范围分区中，分区键的连续值通常储存在相同的分区中。而在散列分区中，连续的分区键值不必储存在相同的分区中。
散列分区把记录分布在比范围分区更多的分区上，这减少了I/O
争用的可能性，实现均匀的负载值分配，增加 HASH 分区可以重新分布数据。

3.1.3.2 语法

-- 语法一:
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]
);

3.1.3.3 创建散列分区

SQL>
create table my_emp(empno number, ename varchar2(10))
partition by hash(empno)
(
partition p1,
partition p2
);

3.1.3.4 查看信息

SQL> select * from user_tab_partitions where table_name='MY_EMP';
SQL> col table_name for a10
SQL> select table_name,partition_name from user_tab_partitions where table_name='MY_EMP';
TABLE_NAME  PARTITION_NAME
----------  ---------------
MY_EMP    P1
MY_EMP    P2

3.1.3.5 插入数据

SQL>
insert into my_emp values(1,'A');
insert into my_emp values(2,'B');
insert into my_emp values(3,'C');

3.1.3.6 查询散列分区数据

SQL> select * from my_emp partition(P1);
EMPNO  ENAME
-----  ----------
2    B
SQL> select * from my_emp partition(P2);
EMPNO  ENAME
-----  ----------
1    A
3    C

3.1.4 列表分区 List Partitioning

3.1.4.1 概述

列表分区告诉 Oracle 所有可能的值，并指定应该插入相应行的分区，它适用于表的数据量很大但是某一列的值只有少量几种。
列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。
可以将不相关的数据组织在一起。

3.1.4.2 语法

PARTITION BY LIST (column_name)
(
  PARTITION part1 VALUES (values_list1),
  PARTITION part2 VALUES (values_list2),
  ...
  PARTITION partN VALUES (DEFAULT)
);

3.1.4.3 创建列表分区

SQL>
create table personcity (id number, name varchar2(10), city varchar2(10))
partition by list(city)
(
partition east values('tianjin','dalian'), 
partition west values('xian'),
partition south values ('shanghai'), 
partition north values ('herbin'), 
partition other values (default)
);

3.1.4.4 插入数据

insert into personcity values(1,'sohu','tianjin');
insert into personcity values(2,'sina','herbin');
insert into personcity values(3,'yahoo','dalian');
insert into personcity values(4,'360','zhengzhou');
insert into personcity values(5,'baidu','xian');

3.1.4.5 查看列表分区数据

select * from personcity partition(east);
ID  NAME  CITY
--  -------  ----------
1  sohu  tianjin
3  yahoo  dalian

3.1.5 复合分区 Composite Partitioning

3.1.5.1 概述

有时候需要根据范围分区后，每个分区内的数据再散列地分布在几个 TBS 中，这样就要使用复合分区。复合分区是先使用范围分区，然后在每个分区内再使用散列分区/列表分区的一种分区方法。如将物料交易的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的 TBS 中(把范围分区和散列分区相结合或者范围分区和列表分区相结合)。

3.1.5.2 语法

PARTITION BY RANGE (column_name1)
SUBPARTITION BY HASH (column_name2)
SUBPARTITIONS number_of_partitions
(
  PARTITION part1 VALUE LESS THAN(range1),
  PARTITION part2 VALUE LESS THAN(range2),
  ...
  PARTITION partN VALUE LESS THAN(MAXVALUE)
);

3.1.5.3 创建复合分区

SQL>
create table student( sno number, sname varchar2(10))
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(maxvalue)
);

3.1.5.4 查看分区信息

有三个range
分区，对每个分区会有 4 个hash
分区，共有 12 个分区。

SQL> select * from user_tab_partitions where table_name='STUDENT';
SQL> select * from user_tab_subpartitions where table_name='STUDENT';
select table_name,partition_name,high_value from user_tab_partitions where table_name='STUDENT';
TABLE_NAME  PARTITION_NAME  HIGH_VALUE
---------  --------------  -----------------
STUDENT    P1        1000
STUDENT    P2        2000
STUDENT    P3        MAXVALUE


col table_name for a10 
col partition_name for a15
col subpartition_name for a20
select table_name,partition_name,subpartition_name from user_tab_subpartitions where table_name='STUDENT';
TABLE_NAME  PARTITION_NAME  SUBPARTITION_NAME
---------  --------------  ---------------------------
STUDENT    P1        SYS_SUBP955
STUDENT    P1        SYS_SUBP954
STUDENT    P1        SYS_SUBP953
STUDENT    P1        SYS_SUBP952
STUDENT    P2        SYS_SUBP959
STUDENT    P2        SYS_SUBP958
STUDENT    P2        SYS_SUBP957
STUDENT    P2        SYS_SUBP956
STUDENT    P3        SYS_SUBP963
STUDENT    P3        SYS_SUBP962
STUDENT    P3        SYS_SUBP961
STUDENT    P3        SYS_SUBP960

Partition
(分区)，一直是 Oracle 数据库引以为荣的一项技术，正是分区的存在让 Oracle 高效的处理海量数据成为可能。

3.1.7 间隔分区 Interval Partitioning

3.1.7.1概述

实际上是由 range 分区引申而来，最终实现了 range 分区的自动化。当新分区键值超出现存最大范围时，通过自动分配新分区扩展范围分区。此用法可省去大量的分区创建工作，尤其是在数据不连续的情况下，以前只能手动一个个的分区的创建好才能使用，现在可以自动创建了。

3.1.7.2 特点

(1). 由 range 分区派生而来

(2). 以定长宽度创建分区(比如年，月，具体的数字 (比如100，500等))

(3). 分区字段必须是 number 或 date 类型

(4). 必须至少指定一个 range 分区 (永久分区)

(5). 当有记录插入时，系统根据需要自动创建新的分区和本地索引

(6). 已有的范围分区可被转换成间隔分区 (通过 ALTER TABLE SET INTERVAL 选项完成)

(7). Interval Partitioning 不支持支持索引组织表，也不能创建 domain index

(8). 对于采用date类型的 Interval Partitioning 可以支持按 year，month，day，hour，minute，second六种类型的定长宽度分区，分别通过如下函数转换：

numtoyminterval(n,{'YEAR'|'MONTH'})

numtodsinterval(n,{'DAY'|'HOUR'|'MINUTE'|'SECOND'})

(9). 对于采用 number 类型的 Interval Partitioning 必须按照固定的宽度分区

(10). 如果对分区需要单独存储，表也需要指定表空间，并且分区字段不能为空

3.1.7.3 创建间隔分区表

-- scott:
SQL>
create table interval_sales (s_id int,d_1 date)
partition by range(d_1) interval (numtoyminterval(1,'MONTH'))
(partition p1 values less than ( to_date('2020-02-01','yyyy-mm-dd') ));
-- 每个月一个分区

3.1.7.4 插入数据

SQL> insert into interval_sales values(1, to_date('**2020-01-21**','yyyy-mm-dd') );
1 row created.

3.1.7.5 查询分区

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';
TABLE_NAME    PARTITION_NAME
--------------  ------------------------
INTERVAL_SALES  P1

3.1.7.6 再次插入数据

SQL> insert into interval_sales values(1, to_date('**2020-02-01**','yyyy-mm-dd') );
1 row created.

3.1.7.7 自动创建分区

插入的数据超过 P1 分区上线，自动建立分区 sys_xxx

SQL> select table_name,partition_name from user_tab_partitions where table_name='INTERVAL_SALES';
TABLE_NAME    PARTITION_NAME
--------------  ------------------------
INTERVAL_SALES  P1
INTERVAL_SALES  SYS_P964

interval (numtoyminterval(1,'MONTH'))
的意思就是一个月的时间间隔，即每个月有一个分区，每当输入了新的月份的数据，这个分区就会自动建立，而不同年的相同月份是两个分区。具体参考 SQL 基础的numtoyminterval
等函数。

3.1.7.8 分区举例

interval(numtodsinterval(1,'hour'))         -- 按照小时建立分区
interval(numtodsinterval(1,'day'))          -- 按天
interval(numtoyminterval(1,'month'))       -- 按月
interval(numtoyminterval(1,'year'))         -- 按年

3.1.8 系统分区 System Partitioning

3.1.8.1 概述

在其他的Partition
类型中，都会面对一个问题就是分区键选择，就是将一个数据表段segment
拆分为多个存储段保存。传统意义上的Partition
停留在定义层面，只要我们在数据表定义的时候确定好分区键和分区策略。之后的使用数据表的过程中，我们其实对分区是 ”透明” 的。如果进行数据表的DML
操作和select
操作，我们是不需要指定、也无法控制数据记录插入到哪个分区中的。只有一种情况不同，就是修改分区键。如果修改分区键，并且修改分区键会影响到分区布局，这样的DML
操作是不允许的。

11g的System Partition
提供了不同选择。它提供给 SQL DML
操作者一种选择，让可以指定出”希望将数据保存”在哪个地方。创建System Partition
的过程中，也是在定义数据表的过程中需要确定。不需要指定任何分区键，即在Insert
语句中决定记录行插入到哪个分区

3.1.8.2 建立 TBS

-- 先建立三个 TBS tbs1，tbs2，tbs3，
-- sys:
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/orcl/tbs1.dbf' size 10m;
SQL> create tablespace tbs2 datafile '/u01/app/oracle/oradata/orcl/tbs2.dbf' size 10m;
SQL> create tablespace tbs3 datafile '/u01/app/oracle/oradata/orcl/tbs3.dbf' size 10m;

3.1.8.3 建立分区表

-- 再建立三个分区的system分区表，分布在三个 TBS上.
-- scott:
SQL>
create table test (c1 int,c2 int) 
partition by system
(
partition p1 tablespace tbs1, 
partition p2 tablespace tbs2, 
partition p3 tablespace tbs3
);

3.1.8.4 Insert 指定插入哪个分区

SQL> 
INSERT INTO test PARTITION (p1) VALUES (1,3);
INSERT INTO test PARTITION (p3) VALUES (4,5);

3.1.8.5 查看系统分区数据

SQL> select * from test;
C1    C2
----  -----------
1    3
4    5
SQL>  select * from test partition(p1);
C1    C2
----  -----------
1    3

3.1.8.6 删除 TBS

如果要删除以上 TBS，必须先删除其上的分区，否则会报错 ORA-14404: 分区表包含不同 TBS中的分区。

3.1.8.6.1 直接删除 TBS 报错

-- sys:
SQL> drop tablespace tbs1 including contents and datafiles;
drop tablespace tbs1 including contents and datafiles
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace

3.1.8.6.2 删除分区 p1，p2

-- scott:
SQL> alter table test drop partition p1;
SQL> alter table test drop partition p2;

3.1.8.6.3 查询 p1、p2分区数据已不存在

SQL> select * from test partition(p1);
select * from test partition(p1)
ERROR at line 1:
ORA-02149: Specified partition does not exist

3.1.8.6.4 再删除 TBS1、TBS2

-- sys:
SQL> drop tablespace tbs1 including contents and datafiles;
SQL> drop tablespace tbs2 including contents and datafiles;
Tablespace dropped.

3.1.8.6.5 删除最后一个分区 p3 报错

如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。

-- sys:
SQL> drop tablespace tbs1 including contents and datafiles;
SQL> drop tablespace tbs2 including contents and datafiles;
Tablespace dropped.

3.1.8.6.6 删除表

SQL> drop table test purge;

3.1.8.6.7 删除 TBS3

SQL> alter table test drop partition p3;
alter table test drop partition p3
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> drop tablespace tbs3 including contents and datafiles;
Tablespace dropped.

3.1.9 引用分区 Reference Partitioning

3.1.9.1 概述

当两个表是主外键约束关联时，我们可以利用父子关系对这两个表进行分区。只要对父表做形式上的分区，子表就可以继承父表的分区键。也就是说，要以某种方式对子表分区，使得各个子表分区分别与一个父表分区存在一对一的关系。如果没有 11g 的引用分区，若想在两个表上都建立对应的分区，那么需要使两表分别有相同名称的键值列。引用分区的好处是避免了在子表上也建立父表同样的一个分区键列，父表上的任何分区维护操作都将自动的级联到子表上。

例：假设有一个数据仓库，希望保证一定数量的数据在线 (例如最近 5 年的 ORDER 信息)，而且要确保相关联的子表数据(ORDER_LINE_ITEMS
数据)也在线。在这个经典的例子中，ORDERS 表通常有一个ORDER_DATE
列，所以可以很容易地按月分区，这也有利于保证最近5年的数据在线。随着时间推移，只需加载下一个新的分区，并删除最老的分区。不过，考虑ORDER_LINE_ITEMS
表时会存在一个问题:它没有ORDER_DATE
列，而且ORDER_LINE_ITEMS
表中根本没法有可以据以分区的列，因此无法帮助清除老信息或加载新信息。

过去，在引用分区出现之前，开发人员必须对数据逆规范化(denormalize
)，具体做法是：从父表 ORDERS 将ORDER_DATE
属性复制到子表ORDER_LINE_ITEMS
。这会引入冗余数据，相应地带来数据冗余存在的一系列常见问题，比如存储开销增加、数据加载资源增加、级联更新问题 (如果修改父表，还必须确保更新父表数据的所有副本)，等等。另外，如果在数据库中启用了外键约束 (而且确实应当启用外键约束)，会发现无法截除或删除父表中原来的分区。

3.1.9.2 传统的表

3.1.9.2.1 建 ORDER 表

-- part_2016和part_2017两个分区
create table orders
(
order#    number primary key,
order_date    date NOT NULL,
data      varchar2(30)
)
enable row movement
PARTITION BY RANGE (order_date)
(
PARTITION part_2016 VALUES LESS THAN (to_date('01-01-2017','dd-mm-yyyy')),
PARTITION part_2017 VALUES LESS THAN (to_date('01-01-2018','dd-mm-yyyy'))
);

3.1.9.2.2 插入数据

insert into orders values( 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx' );
insert into orders values( 2, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'xxx' );
commit;

3.1.9.2.3 建 ORDER_LINE_ITEMS 表

-- part_2016和part_2017两个分区
create table order_line_items
( 
order#    number,
line#      number,
order_date    date,      -- manually copied from ORDERS!
data       varchar2(30),
constraint c1_pk primary key(order#,line#),
constraint c1_fk_p foreign key(order#) references orders
)
enable row movement
PARTITION BY RANGE (order_date)
(
PARTITION part_2016 VALUES LESS THAN (to_date('01-01-2017','dd-mm-yyyy')) ,
PARTITION part_2017 VALUES LESS THAN (to_date('01-01-2018','dd-mm-yyyy')) 
);

3.1.9.2.4 插入数据

insert into order_line_items values ( 1, 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'yyy' );
insert into order_line_items values ( 2, 1, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'yyy' );
commit;

3.1.9.2.5 删除两表的 part_2016 分区

alter table order_line_items drop partition part_2016;
Table altered.
alter table orders drop partition part_2016;
alter table orders drop partition part_2016
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

3.1.9.3 引用分区表创建

采用引用分区，子表会继承父表的分区机制，而不必对分区键逆规范化，而且更重要的是，它会让数据库了解这个子表与父表之间存在对等分区特点。也就是说，截除或删除生意人子表分区时，也能删除或截除父表分区。

3.1.9.3.1 截除 ORDER 删除字表

drop table order_line_items cascade constraints;
truncate table orders;

3.1.9.3.2 ORDER 插入数据

insert into orders values( 1, to_date( '01-jun-2016', 'dd-mon-yyyy' ), 'xxx' );
insert into orders values( 2, to_date( '01-jun-2017', 'dd-mon-yyyy' ), 'xxx' );
commit;

3.1.9.3.3 新建引用分区子表

create table order_line_items
(
order#  number NOT NULL,
line#    number NOT NULL,
data    varchar2(30),
constraint c1_pk primary key(order#,line#),
constraint c1_fk_p foreign key(order#) references orders
)
enable row movement
partition by reference(c1_fk_p);
-- 在这里，将区间分区语句替换为PARTITION BY REFERENCE

这允许指定要使用的外键约束，从而发现分区机制.在这里可以看到外键指向 ORDERS 表——数据库读取 ORDERS 表的结构，并发现它有两个分区。因此，子表会有两个分区。如果现在查询数据字典可以得到。

3.1.9.3.4 插入子表数据

insert into order_line_items values ( 1, 1, 'yyy' );
insert into order_line_items values ( 2, 1, 'yyy' );
commit;

3.1.9.3.5 查询子表的继承的分区

select table_name,partition_name,high_value from user_tab_partitions where table_name in('ORDERS','ORDER_LINE_ITEMS');
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
----------------  -------------  ------------------------------------------------------------------------------
ORDERS        PART_2016    TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS        PART_2017    TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_LINE_ITEMS  PART_2016  
ORDER_LINE_ITEMS  PART_2017

3.1.9.3.6 删除父表分区级联子表

由于数据库知道这两个表是相关联的，可以删除父表分区，并让它自动清除相关的子表分区，因为子表从父表继承而来，所以父表分区结构的任何调整都会向下级联传递到子表分区。

alter table orders drop partition part_2016 update global indexes;
select table_name,partition_name,high_value from user_tab_partitions where table_name in('ORDERS','ORDER_LINE_ITEMS');
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
----------------  -------------  ------------------------------------------------------------------------------
ORDERS        PART_2017    TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_LINE_ITEMS  PART_2017

因此，传统的表之前不允许完成的 DROP 现在则是完全允许的，它会自动级联传递到子表。另外如果使用 ADD 增加一个分区。

3.1.9.3.7 增加父表分区级联子表

alter table orders add partition part_2018 values less than (to_date( '01-01-2019', 'dd-mm-yyyy' ));
select table_name,partition_name,high_value from user_tab_partitions where table_name in('ORDERS','ORDER_LINE_ITEMS');
TABLE_NAME      PARTITION_NAME  HIGH_VALUE
----------------  -------------  ------------------------------------------------------------------------------
ORDERS        PART_2017    TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDERS        PART_2018    TO_DATE(' 2019-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
ORDER_LINE_ITEMS  PART_2017  
ORDER_LINE_ITEMS  PART_2018  
-- ADD分区操作也会向下级联传递.父表与子表之间存在一种一对一的关系

3.1.9.4 范例-主表范围分区

SQL>
CREATE TABLE purchase_orders
(
po_id NUMBER(4),
po_date TIMESTAMP,
supplier_id NUMBER(6),
po_total NUMBER(8,2),
CONSTRAINT order_pk PRIMARY KEY(po_id)
)
PARTITION BY RANGE(po_date)
(
PARTITION Q1 VALUES LESS THAN (TO_DATE('2007-04-01','yyyy-mm-dd')),
PARTITION Q2 VALUES LESS THAN (TO_DATE('2007-06-01','yyyy-mm-dd')),
PARTITION Q3 VALUES LESS THAN (TO_DATE('2007-10-01','yyyy-mm-dd')),
PARTITION Q4 VALUES LESS THAN (TO_DATE('2008-01-01','yyyy-mm-dd'))
);
-- 父表做Range分区(可对引用分区使用除间隔分区外的所有分区策略)

3.1.9.5 范例-子表引用分区

SQL>
CREATE TABLE purchase_order_items
(
po_id NUMBER (4) NOT NULL,
product_id NUMBER(6) NOT NULL,
unit_price NUMBER(8,2),
quantity NUMBER(8),
CONSTRAINT po_items_fk FOREIGN KEY (po_id) REFERENCES purchase_orders(po_id)
)
PARTITION BY REFERENCE(po_items_fk);
-- 主表使用po_date键值列做范围分区,子表中没有po_date列，也想做相应的分区，那么可以使用引用分区.
-- 子表最后一句PARTITION BY REFERENCE()子句给出了引用分区约束名.
-- 子表的po_id列必须是NOT NULL。这与通常的外键可以是NULL是有区别的。
col table_name for a20
col partition_name for a20
SQL> select table_name,partition_name,high_value from user_tab_partitions where table_name in('PURCHASE_ORDERS','PURCHASE_ORDER_ITEMS');
TABLE_NAME        PARTITION_NAME  HIGH_VALUE
--------------------  --------------  -------------------------------
PURCHASE_ORDERS      Q1        TIMESTAMP' 2007-04-01 00:00:00'
PURCHASE_ORDERS      Q2        TIMESTAMP' 2007-06-01 00:00:00'
PURCHASE_ORDERS      Q3        TIMESTAMP' 2007-10-01 00:00:00'
PURCHASE_ORDERS      Q4        TIMESTAMP' 2008-01-01 00:00:00'
PURCHASE_ORDER_ITEMS  Q1
PURCHASE_ORDER_ITEMS  Q2
PURCHASE_ORDER_ITEMS  Q3
PURCHASE_ORDER_ITEMS  Q4
-- 子表purchase_order_items也自动产生了四个分区，Q1，Q2，Q3，Q4。高值为空，意味者此处边界由父表派生。
col partition_name for a20
col partitioning_type for a20
col ref_ptn_constraint_name for a20
SQL> select table_name,partitioning_type,ref_ptn_constraint_name from user_part_tables;
TABLE_NAME        PARTITIONING_TYPE  REF_PTN_CONSTRAINT_NAME
--------------------  -----------------  -----------------------
INTERVAL_SALES      RANGE
MY_EMP          HASH  
PERSONCITY        LIST
PURCHASE_ORDERS      RANGE
PURCHASE_ORDER_ITEMS  REFERENCE      PO_ITEMS_FK
SALE          RANGE
STUDENT          RANGE
-- PO_ITEMS_FK列是外键约束名称

3.1.10 虚拟列分区 Virtual Column-Based Partitioning

3.1.10.1 概述

（1). 只能在堆组织表(普通表)上创建虚拟列。

（2). 虚拟列的值并不是真实存在的，只有用到时才根据表达式计算出虚拟列的值，磁盘上并不存放。

（3). 可在虚拟列上建立索引。

（4). 如果在表中增加虚拟列时没有指定虚拟列的字段类型，ORACLE 会根据 generated always as 后的表达式计算的结果自动设置该字段的类型。

（5). 虚拟列的值由 ORACLE 根据表达式自动计算得出，不可以做 UPDATE 和 INSERT 操作，可以对虚拟列做 DELETE 操作。

（6). 表达式中的所有列必须在同一张表。

（7). 表达式不能使用其他虚拟列。

（8). 可以把虚拟列当做分区关键字建立虚拟列分区表，这就是虚拟列分区。

3.1.10.2 例一

3.1.10.2.1 建虚拟列分区表

-- 按星期分区表
CREATE TABLE PT
(
getdate  DATE NOT NULL,
wd    NUMBER GENERATED ALWAYS AS (TO_NUMBER (TO_CHAR (getdate, 'D'))) VIRTUAL
)
PARTITION BY LIST (wd)
(
    PARTITION Sun  VALUES (1),
    PARTITION Mon  VALUES (2),
    PARTITION Tue  VALUES (3),
    PARTITION Wed  VALUES (4),
    PARTITION Thu  VALUES (5),
    PARTITION Fri    VALUES (6),
    PARTITION Sat    VALUES (7)
);

3.1.10.2.2 插入数据

insert into pt(getdate) values(sysdate);
insert into pt(getdate) values(sysdate-1);
insert into pt(getdate) values(sysdate-2);
insert into pt(getdate) values(sysdate-3);
insert into pt(getdate) values(sysdate-4);
insert into pt(getdate) values(sysdate-5);
insert into pt(getdate) values(sysdate-6);
commit;

3.1.10.2.3 查询表

alter session set nls_date_format='YYYY-MM-DD';
SQL> select * from pt;
GETDATE        WD
---------------    ---
2020-09-20          1
2020-09-21          2
2020-09-22          3
2020-09-23          4
2020-09-24          5
2020-09-25          6
2020-09-26          7

3.1.10.2.4 查询分区信息

select * from pt partition(sun);
…
SQL> select * from user_tab_partitions;
select table_name,partition_name,high_value from user_tab_partitions where table_name = ' PT';
TABLE_NAME  PARTITION_NAME  HIGH_VALUE
----------  --------------  ------------
PT      FRI        6
PT      MON        2
PT      SAT        7
PT      SUN        1
PT      THU        5
PT      TUE        3
PT      WED        4
SQL> select * from user_part_key_columns;
NAME      OBJECT_TYPE  COLUMN_NAME    COLUMN_POSTION
---------------  -----------  ------------  ---------------
PT        TABLE    WD        1
INTERVAL_SALES  TABLE    D_1        1
MY_EMP      TABLE    EMPNO      1
ORDERS      TABLE    ORDER_DATE    1
ORDER_LINE_ITEMSTABLE    ORDER#      1
PERSONCITY    TABLE    CITY      1
SALE      TABLE    SALES_COUNT    1
STUDENT      TABLE    SNO        1

3.1.10.3 例二

3.1.10.3.1 建虚拟列分区表

create table emp1
(
empno number(4) primary key,
ename char(10) not null,
salary number(5) not null,
bonus number(5) not null,
total_sal AS (salary+bonus))
partition by range (total_sal)
(
partition p1 values less than (5000),
partition p2 values less than (maxvalue)
)
enable row movement;

3.1.10.3.2 插入数据

insert into emp1(empno,ename,salary,bonus) values(7788,'SCOTT',3000,1000);
insert into emp1(empno,ename,salary,bonus) values(7902,'FORD',4000,1500);
insert into emp1(empno,ename,salary,bonus) values(7839,'KING',5000,3500);
commit;

3.1.10.3.3 查询

SQL> select * from user_tab_partitions;
SQL> select * from user_part_key_columns;
SQL> select * from emp1 partition (p1);
EMPNO  ENAME  SALARY  BONUS  TOTAL_SAL
------  ------  ------  -----  --------------
7788  SCOTT    3000  1000  4000
SQL> select * from emp1 partition (p2);
EMPNO  ENAME  SALARY  BONUS  TOTAL_SAL
------  ------  ------  -----  --------------
7902  FORD     4000  1500  5500
7839  KING     5000  3500  8500

3.1.10.3.4 更新数据

update emp1 set bonus=500 where empno=7902;
1 row updated.

在建表时就使能了行移动(enable row movement
)，当更新分区键值时就不会报错 (ORA-14402: 更新分区关键字列将导致分区的更改)

3.1.11 More Composite Partitioning

10g 中，我们知道复合分区只支持Range-List和Range-Hash

11g 中复合分区的类型大大增加，Range，List，Interval都可以作为Top level分区，而Second level 则可以是Range，List，Hash多种复合分区，满足更多的业务需求。

oracle 分区表数据库分区 oracle分区表 partition

文章转载自DBA随笔记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。