ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 范围分区表和INTERVAL分区表相互转化

范围分区表和INTERVAL分区表相互转化

原创 Linux操作系统 作者:organist 时间:2012-05-08 12:49:05 0 删除 编辑

INTERVAL分区其实是一种比较特殊的范围分区,因此可以很方便的将RANGE分区表转化为INTERVAL分区表,同样可以将INTERVAL分区表转化为RANGE分区表。


对于一个普通的范围分区表:

SQL> CREATE TABLE T_PART
2 (ID NUMBER,
3 NAME VARCHAR2(30),
4 CREATE_DATE DATE)
5 PARTITION BY RANGE (ID)
6 (PARTITION P1 VALUES LESS THAN (100),
7 PARTITION P2 VALUES LESS THAN (200),
8 PARTITION P3 VALUES LESS THAN (300));

表已创建。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL
2 FROM USER_PART_TABLES
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION INTERVAL
------------------------------ --------- ----------------------------------------
T_PART RANGE

SQL> INSERT INTO T_PART
2 VALUES (100, 'A', SYSDATE);

已创建 1 行。

SQL> INSERT INTO T_PART
2 VALUES (240, 'A', SYSDATE);

已创建 1 行。

SQL> INSERT INTO T_PART
2 VALUES (360, 'TEST', SYSDATE);
INSERT INTO T_PART
*
1 行出现错误:
ORA-14400:
插入的分区关键字未映射到任何分区

插入超过分区上限的记录会报错,下面通过一个简单ALTER TABLE语句,将RANGE分区表转化为INTERVAL分区表:

SQL> ALTER TABLE T_PART SET INTERVAL (100);

表已更改。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL
2 FROM USER_PART_TABLES
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION INTERVAL
------------------------------ --------- ----------------------------------------
T_PART RANGE 100

SQL> INSERT INTO T_PART
2 VALUES (360, 'TEST', SYSDATE);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
2 FROM USER_TAB_PARTITIONS
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ ----------------------
T_PART P1 100
T_PART P2 200
T_PART P3 300
T_PART SYS_P97 400

对于INTERVAL分区表,新增的超过分区上限的数据会自动导致对应的INTERVAL分区被建立。

同样INTERVAL分区表可以方便的转化为RANGE分区表,只需要不输入INTERVAL的值即可:

SQL> ALTER TABLE T_PART SET INTERVAL ();

表已更改。

SQL> SELECT TABLE_NAME, PARTITIONING_TYPE, INTERVAL
2 FROM USER_PART_TABLES
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION INTERVAL
------------------------------ --------- ----------------------------------------
T_PART RANGE

SQL> INSERT INTO T_PART
2 VALUES (450, 'B', SYSDATE);
INSERT INTO T_PART
*
1 行出现错误:
ORA-14400:
插入的分区关键字未映射到任何分区

同时需要注意,不输入INTERVAL的值不等价于输入NULL

SQL> ALTER TABLE T_PART SET INTERVAL (NULL);
ALTER TABLE T_PART SET INTERVAL (NULL)
*
1 行出现错误:
ORA-14752:
间隔表达式不是正确类型的常数

除了RANGE分区表和INTERVAL分区表之间可以相互转化,INTERVAL分区也可以转化为RANGE分区:

SQL> ALTER TABLE T_PART SET INTERVAL (100);

表已更改。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL
2 FROM USER_TAB_PARTITIONS
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- --------------- ---------------------------------------- ----------------------
T_PART P1 100 NO
T_PART P2 200 NO
T_PART P3 300 NO
T_PART SYS_P97 400 NO

SQL> INSERT INTO T_PART
2 VALUES (453, 'A', SYSDATE);

已创建 1 行。

SQL> INSERT INTO T_PART
2 VALUES (743, 'B', SYSDATE);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL
2 FROM USER_TAB_PARTITIONS
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- --------------- ---------------------------------------- --------------------
T_PART P1 100 NO
T_PART P2 200 NO
T_PART P3 300 NO
T_PART SYS_P97 400 NO
T_PART SYS_P98 500 YES
T_PART SYS_P99 800 YES

已选择6行。

除了MERGE PARTITIONSPLIT PARTITION之外,直接修改分区表为RANGE分区,也会使得所有现存的INTERVAL分区变成RANGE分区:

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_PART')
2 FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_PART')
--------------------------------------------------------------------------------

CREATE TABLE "YANGTK"."T_PART"
( "ID" NUMBER,
"NAME" VARCHAR2(30),
"CREATE_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK"
PARTITION BY RANGE ("ID") INTERVAL (100)
(PARTITION "P1" VALUES LESS THAN (100)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P2" VALUES LESS THAN (200)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P3" VALUES LESS THAN (300)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFA
ULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "SYS_P97" VALUES LESS THAN (400)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C
ELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS )

SQL> ALTER TABLE T_PART SET INTERVAL ();

表已更改。

SQL> SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE, INTERVAL
2 FROM USER_TAB_PARTITIONS
3 WHERE TABLE_NAME = 'T_PART';

TABLE_NAME PARTITION_NAME HIGH_VALUE INTERVAL
---------- --------------- ---------------------------------------- ----------------
T_PART P1 100 NO
T_PART P2 200 NO
T_PART P3 300 NO
T_PART SYS_P97 400 NO
T_PART SYS_P98 500 NO
T_PART SYS_P99 800 NO

已选择6行。

SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'T_PART')
2 FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_PART')
--------------------------------------------------------------------------------

CREATE TABLE "YANGTK"."T_PART"
( "ID" NUMBER,
"NAME" VARCHAR2(30),
"CREATE_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK"
PARTITION BY RANGE ("ID")
(PARTITION "P1" VALUES LESS THAN (100)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P2" VALUES LESS THAN (200)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C
ELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "P3" VALUES LESS THAN (300)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_
CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "SYS_P97" VALUES LESS THAN (400)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "SYS_P98" VALUES LESS THAN (500)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFA
ULT)
TABLESPACE "YANGTK" NOCOMPRESS ,
PARTITION "SYS_P99" VALUES LESS THAN (800)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "YANGTK" NOCOMPRESS )

需要注意,INTERVAL分区在DBMS_METADATA获取的表结构中并不会出现,一旦转化为RANGE分区,则DBMS_METADATA获取的源数据会包括分区信息。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26953882/viewspace-722988/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2012-05-08

  • 博文量
    17
  • 访问量
    20538