ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle10g--11gR2分区表汇总九

oracle10g--11gR2分区表汇总九

原创 Linux操作系统 作者:jack22220613 时间:2011-03-18 17:48:47 0 删除 编辑

试验3:修改interval

 

试验开始前介绍:目前有一个interval分区表tab_1,分区键是 id number类型的,interval设定是100,第一个分区范围是1000,目前tab_1表中共有4条记录,分别对应分区如下:

      分区一:  P_TAB_1_0(1000以下)   对应记录  id=999/10

      分区二:  SYS_P173(1000-1099)  对应记录  id=1000

      分区三:  SYS_P171(1100-1199)  对应记录  id=1100

 

开始试验:

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

SYS_P173                                        2                              

SYS_P171                                        3                               

 

SQL> select * from tab_1 ;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11            999         10          1 a                              

10-3 -11             10         10          1 a                              

10-3 -11           1000         10          1 a                              

10-3 -11           1100         10          1 a                               

 

SQL> alter table tab_1 set interval(1000);

 

表已更改。

---修改interval1000

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1001,10,1,'a');

 

已创建 1 行。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

SYS_P173                                        2                              

SYS_P171                                        3                              

 

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1200,10,1,'a');

 

已创建 1 行。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                               

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

P_TAB_1_0                                       1                              

SYS_P173                                        2                               

SYS_P171                                        3                              

SYS_P175                                        4                              

--新增了一个分区

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1300,10,1,'a');

 

已创建 1 行。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

SYS_P173                                        2                               

SYS_P171                                        3                              

SYS_P175                                        4                              

---没有再新增分区

SQL> select * from tab_1 partition(sys_p171);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11           1100         10          1 a                              

 

SQL> select * from tab_1 partition(sys_p175);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11           1200         10          1 a                              

10-3 -11           1300         10          1 a     

 

通过试验可得出:原有记录分区规则保持不变,一旦超出原有分区的最后一个分区的最高值将新增分区,并且分区interval也随之改变。

 

试验4range分区表改变为interval分区表

 

SQL> create table tab_1 (time date,

  2  id number,

  3  city_id number,

  4  value1 number,

  5  value2 varchar2(10)

  6  ) partition by range(time)

  7   ( partition p_tab_1_0 values less than (to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss')) tablespace tbs_1)

  8  ;

 

表已创建。

 

SQL> insert into tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> alter table tab_1

  2  add partition p0 values less than (to_date('2011-3-10 3:00:00','yyyy-mm-dd hh24:mi:ss'))

  3  tablespace tbs_2

  4  ;

 

表已更改。

 

SQL> insert into tab_1 values(to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

 

已创建 1 行。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

P0                                              2                              

 

SQL> select * from tab_1;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11              1         10          1 a                              

10-3 -11              1         10          1 a                              

 

SQL> alter table tab_1 set interval(numtodsinterval(1,'hour'));

 

表已更改。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

P0                                              2                              

 

SQL> insert into tab_1 values(to_date('2011-3-10 3:00:00','yyyy-mm-dd hh24:mi:ss'),1,10,1,'a');

 

已创建 1 行。

 

SQL> select PARTITION_NAME,partition_position  from user_tab_partitions where table_name = 'TAB_1';

 

PARTITION_NAME                 PARTITION_POSITION                              

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

P_TAB_1_0                                       1                              

P0                                              2                              

SYS_P177                                        3                              

 

SQL> select * from tab_1;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11              1         10          1 a                              

10-3 -11              1         10          1 a                              

10-3 -11              1         10          1 a                              

 

SQL> select * from tab_1 partition(p_tab_1_0);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11              1         10          1 a                              

 

SQL> select * from tab_1 partition(p0);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11              1         10          1 a                              

 

SQL> select * from tab_1 partition(sys_p177);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

10-3 -11              1         10          1 a          

 

试验5range-* 复合分区表改变为interval分区表

 

结论与试验4一致

 

 

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

请登录后发表评论 登录
全部评论

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    357953