Oracle 11g-Interval Partitioning

数据库管理员日常要做的一件重复而无聊的工作比如每隔一天要生成新的24个分区，用以存储第二天的数据。而在11g中这项工作可以交由Oracle自动完成了，基于Range和List的Interval Partitioning分区类型登场。

在 11g 里的 Interval 创建，这种方法对没有写全的分区会自动创建。比如我

这里只写了 1 月日期，如果插入的数据有其他月份的，会自动生成对应的分区。

CREATE TABLE TB_INTERVAL 
PARTITION BY RANGE (time_col) 
INTERVAL(NUMTOYMINTERVAL(1, 'month')) 
(PARTITION P0 VALUES LESS THAN (TO_DATE('1-1-2016', 'dd-mm-yyyy')));

指定需要Oracle自动创建分区的间隔时间，上面这个例子是1个月，然后至少创建一个基本分区，上面这个例子是在2016-1-1之前的所有数据都在P0分区中，以后每个月的数据都会存放在Oracle自动创建的一个新分区中。

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

select count(*) from TB_INTERVAL partition (p1);

创建按月分区的分区表

创建分区表

/* Formatted on 2010/6/10 20:21:12 (QP5 v5.115.810.9015) */
create table intervalpart (c1 number, c3 date)
partition by range (c3)
interval ( numtoyminterval (1, 'month') )
(partition part1
values less than (to_date ('01/12/2010', 'mm/dd/yyyy')),
partition part2
values less than (to_date ('02/12/2010', 'mm/dd/yyyy'))
)

注意：如果在建 Interval 分区表是没有把所有的分区写完成，在插入相关数据后会自动生成分区

2. 查看现在表的分区：

SQL> select table_name,partition_name from user_tab_partitions where
table_name='INTERVALPART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART PART1
INTERVALPART PART2

插入测试数据：

SQL> begin
2 for i in 0 .. 11 loop
3 insert into intervalpart
values(i,add_months(to_date('2010-1-1','yyyy-mm-dd'),i));
4 end loop ;
5 commit;
6 end;
7 /

PL/SQL 过程已成功完成。

补充： add_months() 函数获取前一个月或者下一个月的月份，参数中负数代

表往前，正数代表往后。

–上一个月

select to_char(add_months(trunc(sysdate),-1),'yyyymm') from dual;

–下一个月

select to_char(add_months(trunc(sysdate),1),'yyyymm') from dual;

观察自动创建的分区：

SQL> select table_name,partition_name from user_tab_partitions where
table_name='INTERVALPART';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
INTERVALPART PART1
INTERVALPART PART2
INTERVALPART SYS_P22
INTERVALPART SYS_P23
INTERVALPART SYS_P24
INTERVALPART SYS_P25
INTERVALPART SYS_P26
INTERVALPART SYS_P27
INTERVALPART SYS_P28
INTERVALPART SYS_P29
INTERVALPART SYS_P30
INTERVALPART SYS_P31

已选择 12 行。

5. 查看分区内容：

SQL> select * from INTERVALPART;
C1 C3
--------- ----------
1 2010-01-01
0 2010-01-01
1 2010-02-01
2 2010-03-01
3 2010-04-01
4 2010-05-01
5 2010-06-01
6 2010-07-01
7 2010-08-01
8 2010-09-01
9 2010-10-01
10 2010-11-01
11 2010-12-01
已选择 13 行。
SQL> select * from INTERVALPART partition(part1);
C1 C3
--------- ----------
1 2010-01-01
0 2010-01-01
SQL> select * from INTERVALPART partition(part2);
C1 C3
--------- ----------
1 2010-02-01

创建一个以天为间隔的分区表

创建分区表：

SQL> create table dave
2 (
3 id number,
4 dt date
5 )
6 partition by range (dt)
7 INTERVAL (NUMTODSINTERVAL(1,'day'))
8 (
9 partition p100101 values less than (to_date('2010-01-01','yyyy-mm-dd'))
10 );

查看表分区：

SQL> select table_name,partition_name from user_tab_partitions where table_name='DAVE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DAVE P100101

插入测试数据：

SQL> begin
2 for i in 1 .. 12 loop
3 insert into dave values(i,trunc(to_date('2010-1-1','yyyy-mm-dd')+i));
4 end loop;
5 commit;
6 end;
7 /

PL/SQL 过程已成功完成。

4. 观察自动创建的分区：

SQL> select table_name,partition_name from user_tab_partitions where
table_name='DAVE';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DAVE P100101
DAVE SYS_P32
DAVE SYS_P33
DAVE SYS_P34
DAVE SYS_P35
DAVE SYS_P36
DAVE SYS_P37
DAVE SYS_P38
DAVE SYS_P39
DAVE SYS_P40
DAVE SYS_P41
DAVE SYS_P42
DAVE SYS_P43

已选择 13 行。

5. 查看分区内容：

SQL> select * from dave partition(SYS_P32);
ID DT
---------- ----------
1 2010-01-02
SQL> select * from dave partition(SYS_P33);
ID DT
-------- ----------
2 2010-01-03
SQL> select * from dave partition(SYS_P34);
ID DT
-------- ----------
3 2010-01-04
SQL> select * from dave;
ID DT
-------- ----------
1 2010-01-02
2 2010-01-03
3 2010-01-04
4 2010-01-05
5 2010-01-06
6 2010-01-07
7 2010-01-08
8 2010-01-09
9 2010-01-10
10 2010-01-11
11 2010-01-12
12 2010-01-13
已选择 12 行。

System Partitioning

系统分区，在这个新的类型中，我们不需要指定任何分区键，数据会进入哪个分区完全由应用程序决定，实际上也就是由SQL来决定，终于，我们在Insert语句中可以指定插入哪个分区了。

假设我们创建了下面这张分区表，注意，没有指定任何分区键：

CREATE TABLE systab (c1 integer, c2 integer) 
PARTITION BY SYSTEM 
( 
PARTITION p1 TABLESPACE tbs_1, 
PARTITION p2 TABLESPACE tbs_2, 
PARTITION p3 TABLESPACE tbs_3, 
PARTITION p4 TABLESPACE tbs_4 
);

现在由SQL语句来指定插入哪个分区：

– 数据插入p1分区

INSERT INTO systab PARTITION (p1) VALUES (4,5);

– 数据插入第2个分区，也就是p2分区

INSERT INTO systab PARTITION (2) VALUES (7,8);

– 为了实现绑定变量，用pno变量来代替实际分区号，以避免过度解析

INSERT INTO systab PARTITION (:pno) VALUES (9,10);

由于System Partitioning的特殊性，所以很明显，这种类型的分区将不支持Partition Split操作，也不支持create table as select操作。

12C 对表分区维护的增强

Oracle Database 12c对表分区变化比较多，共分为下面几点

1.在线移动分区:通过MOVE ONLINE关键字实现在线分区移动。移动过程中，对表和被移动的分区可以执行查询操作，

DML语句以及分区的创建和维护操作。整个移动过程对用户来说是透明的。

2.多个分区同时操作：可以对多个分区同时进行维护操作，如将一年的12个分区合并到一个新的分区中，或者将一个分区

分成多个分区。可以通过FOR语句指定操作的每个语句，对于RANGE分区而言，也可以通过TO来指定处理分区的范围。

多个分区操作自动并行完成。

3.INTERVAL-REFERENCE分区：把11g的interval分区和reference分区结合，这样主表自动增加一个分区后，所有字表，

孙子表·····重重孙子表上都会自动随着外界列数据增加，自动创建新的分区。

4.TRUNCATE和EXCHANGE分区及子分区。五分是TRUNCATE还是EXCHANGE分区，在主表上执行，都可以级联的作用在字表，

孙子吧·····重重孙子表上同时执行。对于TRUNCATE而言，所有表的TRUNACATE操作在同一个事务中，如果中途失败，

会回滚到之前的状态。通过关键字CASCADE实现。

5.异步全局索引维护：对于非常大的分区表而言，UPDATE GLOBAL INDEX不再是痛苦。Oracle可以实现了异步维护的

功能，即使是几亿条的记录的全局索引，在分区维护操作，比如DROP或TRUNCATE后，仍然是VALID状态,索引不会失效，

不过索引的状态是包含OBSOLETE数据，当维护操作完成，索引状态恢复。

6.部分本地和全局索引：Oracle的所有可以在分区级别定义。无论全局索引还是本地所有都可以在分区表的部分分区

上建立，其他分区上则没有所有。当通过所有列访问全表数据时，Oracle通过UNION ALL实现，一部分通过索引扫描，

另一部分通过全分区扫描。这可以减少对历史数据的索引量，增强了灵活性。

具体例子：

1)添加多个新分区：

在12c之前，一次只能添加一个新分区到一个已存在的分区表。在12c中只需要一条单独的ALTER TABLE ADD PARTITION

命令就可以添加N个新分区。

 ALTER TABLE EMP_PART ADD PARTITION
 PARTITION P4 VALUES LESS THAN(35000)
 PARTITION P5 VALUES LESS THAN(40000)

同样，只要MAXVALUE分区不存在，可以添加多个新分区到一个列表和系统分区表

2)删除、截断多个分区/子分区

通过在此之前，一次只能删除/截断一个分区。12c中通过ALTER TABLE table_name {TRUNCAT|DROP} PARTITIONS

 ALTER TABLE EMP_PART DROP PARTITIONS P4,P5;
 ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5;

要保持索引更新，使用UPDATE INDEXES或UPDATE GLOBAL INDEXES语句。

 ALTER TABLE EMP_PART DROP PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;
 ALTER TABLE EMP_PART TRUNCATE PARTITIONS P4,P5 UPDATE GLOBAL INDEXES;

如果没有使用UPDATE GLOBAL INDEXES更新索引，也可以通过查询ORPHANED_ENTRIES字段找出是否有索引包含过期的条目

3)将单个分区分割为多个新分区

在此之前是无法单个命令完成这个操作的。

 ALTER TABLE EMP_PART SPLIT PARTITIONS p_max INTO
 (PARTITION P4 VALUES LESS THAN (30000),
 PARTITION P5 VALUES LESS THAN(40000),
 PARTITION P_MAX);

4)将多个分区合并为一个分区

ALTER TABLE EMP_PART MERGE PARTITIONS P2,P3,P4 INTO PARTITION P_MERGE;

如果是连续分区，可以通过TO来

ALTER TABLE EMP_PART MERGE PARTITIONS P2 TO P4 INTO PARTITION P_MERGE;

普通表转分区表方法

将普通表转换成分区表有 4 种方法：

1. Export/import method

2. Insert with a subquery method

3. Partition exchange method

4. DBMS_REDEFINITION

详情参考

How to Partition a Non-partitioned Table [ID 1070693.6]

或者

oracle将普通表改为分区表

表分区的相关操作

1.添加分区

添加新的分区有 2 中情况：

（ 1）原分区里边界是 maxvalue 或者 default。这种情况下，我们需要把边界分区 drop 掉，加上新分区后，在添加上新的分区。或者采用 split，对边界分区进行拆分。

（ 2）没有边界分区的。这种情况下，直接添加分区就可以了。

以边界分区添加新分区示例：

（ 1）分区表和索引的信息如下：

SQL> create table custaddr
2 (
3 id varchar2(15 byte) not null,
4 areacode varchar2(4 byte)
5 )
6 partition by list (areacode)
7 (
8 partition t_list556 values ('556') tablespace icd_service,
9 partition p_other values (default)tablespace icd_service
10 );

表已创建。

SQL> create index ix_custaddr_id on custaddr(id)
2 local (
3 partition t_list556 tablespace icd_service,
4 partition p_other tablespace icd_service
5 );

索引已创建。

（ 2）插入几条测试数据：

SQL> insert into custaddr values('1','556');
已创建 1 行。
SQL> insert into custaddr values('2','551');
已创建 1 行。
SQL> insert into custaddr values('3','555');
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from custaddr;
ID AREA
--------------- ----
1 556
2 551
3 555
SQL> select * from custaddr partition(t_list556);
ID AREA
--------------- ----
1 556
SQL>

（ 3）删除 default 分区

sql> alter table custaddr drop partition p_other;
表已更改。
sql> select table_name,partition_name from user_tab_partitions where
table_name='CUSTADDR';
table_name partition_name
------------------------------ ------------------------------
custaddr t_list556

（ 4）添加新分区

SQL> alter table custaddr add partition t_list551 values('551') tablespace icd_service;
表已更改。
SQL> select table_name,partition_name from user_tab_partitions where
table_name='CUSTADDR';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR T_LIST556
CUSTADDR T_LIST551

（ 5）添加 default 分区

SQL> alter table custaddr add partition p_other values (default) tablespace
icd_service;
表已更改。
SQL> select table_name,partition_name from user_tab_partitions where
table_name='CUSTADDR';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR T_LIST556
CUSTADDR T_LIST551
CUSTADDR P_OTHER

（ 6）对于局部索引， oracle 会自动增加一个局部分区索引。验证一下：

sql> select owner,index_name,table_name,partitioning_type from dba_part_indexes
where index_name='ix_custaddr_id';
owner index_name table_name
---------------------- ------------------------------ ------------------
icd ix_custaddr_id custaddr
sql> select index_owner,index_name,partition_name from dba_ind_partitions where
index_name='ix_custaddr_id';
index_owner index_name partition_name
------------------------------ ------------------------------ ------------------
icd ix_custaddr_id p_other
icd ix_custaddr_id t_list551
icd ix_custaddr_id t_list556

分区索引自动创建了。

用 split方法示例

sql> alter table custaddr split partition p_other values('552') into (partition t_list552
tablespace icd_service, partition p_other tablespace icd_service);
表已更改。

–注意values(‘552’)，如果是 Range 类型的，使用 at， List 使用 Values。

SQL> select table_name,partition_name from user_tab_partitions where
table_name='CUSTADDR';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
CUSTADDR T_LIST556
CUSTADDR T_LIST551
CUSTADDR T_LIST552
CUSTADDR P_OTHER
SQL> select index_owner,index_name,partition_name from dba_ind_partitions
where index_name='IX_CUSTADDR_ID';
index_owner index_name partition_name
------------------------------ ------------------------------ ------------------
icd ix_custaddr_id p_other
icd ix_custaddr_id t_list551
icd ix_custaddr_id t_list552
icd ix_custaddr_id t_list556

注意：分区表会自动维护局部分区索引。全局索引会失效，需要进行 rebuild。

2.删除分区

alter table T_TRACK drop partition p_2005_04;

3.添加子分区

alter table T_TRACK
        modify partition P_2005_01
        add subpartition P_2005_01_P1017 values('P1017');

4.删除子分区

alter table T_TRACK drop subpartition p_2005_01_p1017;

5.截断一个分区表中的一个分区的数据：

  alter table sales3  truncate partition sp1
           --这种方式会使全局分区索引无效
  alter table sales3 truncate partition sp1 update indexes
          --这种方式全局分区索引不会无效

说明：

Truncate 相对 delete 操作很快，数据仓库中的大量数据的批量数据加载可能

会有用到；截断分区同样会自动维护局部分区索引，同时会使全局索引 unusable,需要重建

6.截断分区表的子分区

alter table comp truncate subpartition sub1

7.截断带有约束的分区表

 a、禁用约束
          alter table sales disable constraint dname_sales1
        b、截断分区
          alter table sales truncate partitoin dec
        c、启用约束
          alter table sales enable constraint dname_sales1

8.查看一个表是不是分区表

  select table_name,partitioned from user_tables;
        TABLE_NAME                     PAR
        ------------------------------ ---
        DEPT                           NO
        DEPT3                          YES

9.将一个表的分区从一个表空间移动到另一个表空间

 a、查看分区在哪个表空间
          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,
          SUBPARTITION_COUNT
          FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';
        b、移动分区
          alter table sales move partiton sp1 tablespace tp;
        c、检查是否移动成功
          SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME,
          SUBPARTITION_COUNT
          FROM DBA_TAB_PARTITIONS WHERE TABLE_OWNER='SCOTT';
        移动表空间后，要重建索引，否则索引会变得无效
        alter index xxx rebuild

注意：分区移动会自动维护局部分区索引， oracle 不会自动维护全局索引，所以需要我们重新 rebuild 分区索引，具体需要 rebuild 哪些索引，可以通过

dba_part_indexes,dba_ind_partitions 去判断

SQL> Select index_name,status From user_indexes Where
table_name='CUSTADDR';
INDEX_NAME STATUS
------------------------------ --------
IX_CUSTADDR_ID N/A

10.合并分区：

相邻的分区可以 merge 为一个分区，新分区的下边界为原来边界值较低的分区，上边界为原来边界值较高的分区，原先的局部索引相应也会合并，全局索引会失效，需要 rebuild。

alter table sales3 merge partitons sp1,sp3 into partition sp3

合并后的分区名，不能是边界值较低的那个

11.与分区表相关的数据字典视图：

        DBA_TAB_PARTITIONS
        DBA_IND_PARTITIONS
        DBA_TAB_SUBPARTITIONS
        DBA_IND_SUBPARTITIONS

Oracle-分区表解读（下）

Oracle 11g-Interval Partitioning

创建按月分区的分区表

创建一个以天为间隔的分区表

System Partitioning

12C 对表分区维护的增强

普通表转分区表方法

表分区的相关操作

1.添加分区

以边界分区添加新分区示例：

用 split方法示例

2.删除分区

3.添加子分区

4.删除子分区

5.截断一个分区表中的一个分区的数据：

6.截断分区表的子分区

7.截断带有约束的分区表

8.查看一个表是不是分区表

9.将一个表的分区从一个表空间移动到另一个表空间

10.合并分区：

11.与分区表相关的数据字典视图：

热门文章

最新文章

相关课程

相关电子书

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle-分区表解读（下）

Oracle 11g-Interval Partitioning

创建按月分区的分区表

创建一个以天为间隔的分区表

System Partitioning

普通表转分区表方法

表分区的相关操作

1.添加分区

以边界分区添加新分区示例：

用 split方法示例

2.删除分区

3.添加子分区

4.删除子分区

5.截断一个分区表中的一个分区的数据：

6.截断分区表的子分区

7.截断带有约束的分区表

8.查看一个表是不是分区表

9.将一个表的分区从一个表空间移动到另一个表空间

10.合并分区：

11.与分区表相关的数据字典视图：

热门文章

最新文章

相关课程

相关电子书

推荐镜像