11g分布表新特性——Interval分区

作为一个成熟的DBMS，在每个新的版本中Oracle都会推出一些有益的技术尝试和新特性。熟悉掌握这些新特性，有助于我们更快的适应新版本Oracle软件，掌握其发展动态脉络，及时优化我们的工作方式。

 

分区表是我们经常使用的一种堆表优化手段。借助分区表，我们可以将一个数据量巨大的表根据业务需求水平切分为不同的分区块。将数据访问处理和运维管理集中在特定的数据块内部，以期减少全表大面积搜索。

 

相对于普通数据表，分区表要花很多的规划和管理精力。规划包括分区类型的选择、分区键选择和物理段segment存储分配。管理包括分区预留和添加、对应分区数据管理等等。

 

在Oracle11g之前，分区表包括多少个分区，各分区都在什么位置上是在建表过程中确定的。之后的修改需要手工完成。Oracle11g推出了间距Interval-Partition的特性，实现一些类型分区表的自动分区管理。

 

1、 Before Oracle11g

 

首先，我们来看看Interval-Partition出现之前的特性。Oracle DBA和开发人员对分区表通常是需要进行预分区规划，也就是在部署系统的时候就预先建立出一些预留分区（通常出现在数字类型和日期类型，比如预先分配3年系统的数据分区）。因为如果数据表建立，特别是生产环境上的数据表建立，新增加和划分分区就有很多维护窗口问题，所以DBA经常对数据表进行预先分区。

 

在11g之前，如果数据中出现未能匹配分区条件的情况，系统会拒绝进行数据操作。

 

 

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0  Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

 

我们构建一个简单的Range-Partition Table。

 

SQL> create table t_part

  2  (

  3    EMPNO    NUMBER(4) not null,

  4    ENAME    VARCHAR2(10),

  5    JOB      VARCHAR2(9),

  6    MGR      NUMBER(4),

  7    HIREDATE DATE,

  8    SAL      NUMBER(7,2),

  9    COMM     NUMBER(7,2),

 10    DEPTNO   NUMBER(2)

 11  )

 12  partition by range(sal)

 13  (

 14     partition t_p1 values less than (1000) tablespace users,

 15     partition t_p2 values less than (2000) tablespace system,

 16     partition t_p3 values less than (3000) tablespace mytest

 17  );

 

Table created

 

 

如果此时试图插入数据，对未能映射到分区的数据是报错提示的。

 

 

SQL> insert into t_part select * from scott.emp;

 

insert into t_part select * from scott.emp

 

ORA-14400: 插入的分区关键字未映射到任何分区

 

SQL> select count(*) from scott.emp where sal>=3000;

 

  COUNT(*)

----------

         3

 

 

结论：在Interval-Partition特性出现之前，DBA必须预先对将来可能出现的数据值进行规划或者预测。如果是一个生产系统，Online状态添加或者修改分区存在一些操作风险。

 

 

2、 Interval-Partition feature

 

在Oracle 11g中，推出了Interval-Partition的新特性。Interval-Partition特性并不是提供了一种新的分区方案，而是提供了一种分区拓展方案。下面我们通过一个实验来演示Interval-Partition的使用。

 

 

SQL> select * from v$version;

 

BANNER

-------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0  Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 – Production

 

 

此时我们选择Oracle 11r2版本进行试验。

 

 

SQL> create table t_part

  2  (

  3    EMPNO    NUMBER(4) not null,

  4    ENAME    VARCHAR2(10),

  5    JOB      VARCHAR2(9),

  6    MGR      NUMBER(4),

  7    HIREDATE DATE,

  8    SAL      NUMBER(7,2),

  9    COMM     NUMBER(7,2),

 10    DEPTNO   NUMBER(2)

 11  )

 12  partition by range(sal)

 13  interval (1000)

 14  store in (users, system)

 15  (

 16     partition t_p1 values less than (1000),

 17     partition t_p2 values less than (2000),

 18     partition t_p3 values less than (3000)

 19  );

 

Table created

 

 

我们构建了一个和Oracle10g实验基本类似的环境。分区表分区键和分区类型相同，唯一较大的差异在于中间添加了子句Interval(1000)。在Oracle中，分区表对应的数据段是多个，我们通过视图dba_segments可以查看到特性信息。

 

 

SQL> select table_name, partition_name, high_value, PARTITION_POSITION, TABLESPACE_NAME from dba_tab_partitions where table_name='T_PART';

 

TABLE_NAME PARTITION_NAME       HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME

---------- -------------------- ---------- ------------------ ------------------------------

T_PART     T_P1                 1000                        1 SYSTEM

T_PART     T_P2                 2000                        2 SYSTEM

T_PART     T_P3                 3000                        3 SYSTEM

 

 

包括了在定义数据表时确定的三个数据段，符合条件的映射值分别为1000、2000和3000。我们对分区表可以使用dbms_stats方法进行适当的统计量收集。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

 

PL/SQL procedure successfully completed

 

SQL> select table_name, partition_name, high_value, PARTITION_POSITION, TABLESPACE_NAME, num_rows from dba_tab_partitions where table_name='T_PART';

 

TABLE_NAME PARTITION_NAME       HIGH_VALUE PARTITION_POSITION NUM_ROWS

---------- -------------------- ---------- ------------------ ----------

T_PART     T_P1                 1000                        1         0

T_PART     T_P2                 2000                        2         0

T_PART     T_P3                 3000                        3         0

 

 

接下来，向数据表t_part中灌入数据scott.t。

 

 

SQL> insert into t_part select * from scott.emp;

14 rows inserted

 

SQL> commit;

Commit complete

 

SQL> select * from scott.emp;

 

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO

----- ---------- --------- ----- ----------- --------- --------- ------

 7566 JONES      MANAGER    7839 1981/4/2      2975.00               20

7788 SCOTT      ANALYST    7566 1987/4/19     3000.00               20

 7839 KING       PRESIDENT       1981/11/17    5000.00               10

 （篇幅原因，有省略……）

 7902 FORD       ANALYST    7566 1981/12/3     3000.00               20

 7934 MILLER     CLERK      7782 1982/1/23     1300.00               10

 

14 rows selected

 

 

 

注意：插入的数据中包括了大于等于3000的数据值，但是还是成功插入到了分区表中。我们检查分区表段情况。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

 

PL/SQL procedure successfully completed

 

SQL> select table_name, partition_name, high_value, PARTITION_POSITION, TABLESPACE_NAME, num_rows from dba_tab_partitions where table_name='T_PART';

 

TABLE_NAME PARTITION_NAME       HIGH_VALUE PARTITION_POSITION TABLESPACE_NAME        NUM_ROWS

---------- -------------------- ---------- ------------------ -------------------- ----------

T_PART     T_P1                 1000                        1 SYSTEM                        2

T_PART     T_P2                 2000                        2 SYSTEM                        6

T_PART     T_P3                 3000                        3 SYSTEM                        3

T_PART     SYS_P21              4000                        4 SYSTEM                        2

T_PART     SYS_P22              6000                        5 SYSTEM                        1

 

 

注意：原有的三个数据段变成了五个数据段。出现了两个新的分区段sys_p21和sys_p22，分别对应Range分区上线4000和6000。统计值中表明插入的三个异常值存在于其内。

 

这样我们就能够明白interval-partition在Range分区数字类型分区键的作用了。简单的说，在定义分区表的时候，可以通过interval指定出一个分区拓展的规则方案。在例子中，我们选择了sal变化1000的时候，进行拓展分区。之后，当Oracle在对这个数据表进行操作的时候，如果发生了超过原有分区范围的情况，会按照1000的步长进行自动的分区建立。

 

例子中，我们有3000、3000和5000的异常值。两个3000拓展为以4000作为less than的分区。5000的异常值自然拓展为以6000为less than的分区。

 

 

数字类型分区键的interval分区在应对异常数据值和新增数据值可能的时候效果很好。Interval-Partition是在原有分区的基础上，提供异常值分区拓展方法。同时这样对一些有规则的分区拓展，就可以实现Oracle自动进行新分区的加入了。

目前的Interval-Partition支持的Range分区键类型只有number和date两种类型。在上面的示例中，我们已经演示了数字number类型的分区拓展，下面我们进行date类型演示。

 

1、 Interval-Partition for Date Range

 

笔者认为，相对于number类型的分区拓展，date类型interval-partition分区的应用空间更为广泛。因为实际生产环境下使用某个特定日期范围分区的场景非常多，比如特定年度的交易作为单独分区。

 

同样选择Oracle 11R2的scott用户schema作为实验环境。

 

 

SQL> select * from v$version;

 

BANNER

--------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0  Production

 

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

 

SQL> conn scott/tiger@wilson ;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

 

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SCOTT';

 

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

USERS                          TEMP

 

 

我们利用dba_objects数据视图创建分区表，使用last_ddl_time作为分区键。

 

 

SQL> create table t_part

  2  partition by range(last_ddl_time)

  3  interval (numtoyminterval(1,'year'))

  4  (

  5     partition p1 values less than (to_date('2003-1-1','yyyy-mm-dd'))

  6  )

  7  as

  8  select * from dba_objects where 1=0;

 

Table created

 

 

处理中间的interval(numtoyminterval(1,’year’))，该数据表和其他日期类型分区数据表没有差异。Numtoyminterval表示使用年作为时间间隔，每一年作为一个分区间距。

 

同时，在定义数据表的时候，定义了一个保存2003年之前所有数据的分区p1。此时，我们观察数据字典情况。

 

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    USERS

 

 

SQL> select table_name, partition_name, high_value, tablespace_name from user_tab_partitions where table_name='T_PART';

 

TABLE_NAME PARTITION_ HIGH_VALUE           TABLESPACE_NAME

---------- ---------- -------------------- ------------------------------

T_PART     P1         TO_DATE(' 2003-01-01 USERS

                       00:00:00', 'SYYYY-M

                      M-DD HH24:MI:SS', 'N

                      LS_CALENDAR=GREGORIA

 

 

分区p1为预定义分区。此时我们向其中插入数据。

 

 

SQL> insert into t_part select * from dba_objects where last_ddl_time<to_date('2003-1-1','yyyy-mm-dd');

3 rows inserted

 

SQL> insert into t_part select * from dba_objects where last_ddl_time>to_date('2003-1-1','yyyy-mm-dd');

72599 rows inserted

 

SQL> commit;

Commit complete

 

 

此时，其中插入的数据已经与原有分区不匹配了。

 

 

SQL> exec dbms_stats.gather_table_stats(user,'T_PART',cascade => true);

 

PL/SQL procedure successfully completed

 

 

 

SQL> select table_name, partition_name, high_value, tablespace_name, num_rows from user_tab_partitions where table_name='T_PART';

 

TABLE_NAME PARTITION_ HIGH_VALUE           TABLESPACE_NAME                  NUM_ROWS

---------- ---------- -------------------- ------------------------------ ----------

T_PART     P1         TO_DATE(' 2003-01-01 USERS                                   3

                       00:00:00', 'SYYYY-M                               

                      M-DD HH24:MI:SS', 'N                                

                      LS_CALENDAR=GREGORIA                               

 

T_PART     SYS_P24    TO_DATE(' 2009-01-01 USERS                                   1

                       00:00:00', 'SYYYY-M                                

                      M-DD HH24:MI:SS', 'N                               

                      LS_CALENDAR=GREGORIA                               

 

T_PART     SYS_P21    TO_DATE(' 2010-01-01 USERS                               71678

                       00:00:00', 'SYYYY-M                               

                      M-DD HH24:MI:SS', 'N                               

                      LS_CALENDAR=GREGORIA                               

 

T_PART     SYS_P22    TO_DATE(' 2011-01-01 USERS                                 711

                       00:00:00', 'SYYYY-M                               

                      M-DD HH24:MI:SS', 'N                               

                      LS_CALENDAR=GREGORIA                                

 

T_PART     SYS_P23    TO_DATE(' 2012-01-01 USERS                                 209

                       00:00:00', 'SYYYY-M                               

                      M-DD HH24:MI:SS', 'N                               

                      LS_CALENDAR=GREGORIA                               

 

 

结论：对于date类型分区键的interval-partition分区表，当不符合分区条件的记录，会自动生成分区进行保存。

 

另外，如果要求对每周的数据划分为分区，可以使用numtodsinterval函数。

 

 

INTERVAL(numtodsinterval(7,'day'))

 

 

2、 Partition Tablespace

 

分区表的创建目的，除了进行分区内局部扫描、便于管理外，还可以通过将分区存放在不同的表空间做到平衡分散IO的目的。所以，对分区的表空间规划，通常是DBA日常决策的一个重要内容。

 

在interval-partition中，分区的创建不是预创建过程，而是系统自动生成。那么，表空间storage如何规划设计呢？

 

从上面的t_part数据分区表中，无论是数据表体，还是各个分区，都没有显示的指明数据分区的表空间存储。从结果看，保存的位置都是users表空间，而users表空间是用户scott的默认表空间。

 

 

SQL> select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SCOTT';

 

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

USERS                          TEMP

 

 

说明：如果分区表没有明确表示使用的分区表空间，Oracle会选择用户schema对应的表空间作为分区所在表空间。

 

如果进行部分执行表空间的工作，会如何呢？

 

首先，调整scott用户对应的default tablespace。

 

 

SQL>  select DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE from dba_users where username='SCOTT';

 

DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE

------------------------------ ------------------------------

SYSTEM                         TEMP

 

 

进行分区表创建。

 

 

SQL> create table t_part

  2  partition by range(sal)

  3  interval (1000)

  4  (

  5     partition p1 values less than (1000),

  6     partition p2 values less than (2000) tablespace SYSAUX

  7  )

  8  as

  9  select * from emp where sal<2000;

 

Table created

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    SYSTEM

T_PART     P2         TABLE PARTITION    SYSAUX

 

 

在没有使用store in的情况下，我们可以使用tablespace关键字对特殊分区进行指定。如果没有指定，则选择用户默认表空间。

 

 

SQL> insert into t_part select * from emp where sal>=2000;

6 rows inserted

 

SQL> commit;

Commit complete

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    SYSTEM

T_PART     P2         TABLE PARTITION    SYSAUX

T_PART     SYS_P25    TABLE PARTITION    SYSTEM

T_PART     SYS_P26    TABLE PARTITION    SYSTEM

T_PART     SYS_P27    TABLE PARTITION    SYSTEM

 

 

如果没有明确的指定表空间，Oracle会将创建的分区段保存在所属用户schema对应的默认表空间里。如果需要对预定义分区进行分区指定，需要使用tablespace子句。

 

那么，如果是一个未建立的分区，如何制定创建分区呢？可以使用store in子句。

 

 

SQL> create table t_part

  2  partition by range(sal)

  3  interval (1000) store in (test,PERFSTAT)

  4  (

  5     partition p1 values less than (1000),

  6     partition p2 values less than (2000)

  7  )

  8  as

  9  select * from emp where sal<2000;

 

Table created

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    SYSTEM

T_PART     P2         TABLE PARTITION    SYSTEM

 

//预定义分区而言，如果不使用tablespace子句，会创建在默认表空间中；

 

SQL> insert into t_part select * from emp where sal>=2000;

6 rows inserted

 

SQL> commit;

Commit complete

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    SYSTEM

T_PART     P2         TABLE PARTITION    SYSTEM

T_PART     SYS_P28    TABLE PARTITION    TEST

T_PART     SYS_P29    TABLE PARTITION    PERFSTAT

T_PART     SYS_P30    TABLE PARTITION    PERFSTAT

 

 

如果在store in后面标注上tablespaces的列表，那么新创建出的分区就会依次循环的均匀存放在各个分区上。

 

 

SQL> insert into t_part (sal) values (6000);

1 row inserted

 

SQL> commit;

Commit complete

 

SQL> select segment_name, partition_name,SEGMENT_TYPE, tablespace_name from user_segments where segment_name='T_PART';

 

SEGMENT_NA PARTITION_ SEGMENT_TYPE       TABLESPACE_NAME

---------- ---------- ------------------ ------------------------------

T_PART     P1         TABLE PARTITION    SYSTEM

T_PART     P2         TABLE PARTITION    SYSTEM

T_PART     SYS_P28    TABLE PARTITION    TEST

T_PART     SYS_P29    TABLE PARTITION    PERFSTAT

T_PART     SYS_P30    TABLE PARTITION    PERFSTAT

T_PART     SYS_P31    TABLE PARTITION    TEST

 

6 rows selected

 

 

使用store in子句，可以方便的指定未生成创建分区的表空间分布。

 

3、 结论

 

Oracle 11g中，对于分区表技术提出了很多的新特性，包括新的子分区组织方法、Interval-Partition等等。Interval-Partition技术的推出，笔者认为还是建立在online生产环境的分区自动化管理理念上。在实际生产环境中，进行分区补充或者管理都需要在特定的维护窗口时间和预规划工作。Oracle提供的Interval-Partition，可以帮助DBA实现很多的分区维护工作。