ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 间隔分区使用

间隔分区使用

原创 Linux操作系统 作者:outdo 时间:2012-09-21 15:44:55 0 删除 编辑

1.1 interval (range分区的扩展)
此用法可省去大量的分区创建工作,尤其是在数据不连续的情况下,更为有用,以前只能手动一个分区一个分区的创建好,才能使用,现在可以自动创建了,不过也有些限制,接下来就简述下此功能的优点和限制。

 

--按小时建分区

createtabletab_1(timedate,

idnumber,

city_idnumber,

value1number,

value2varchar2(10)

)partitionbyrange(time)

 interval(numtodsinterval(1,'hour'))storein(tbs_1)

 (partitionp_tab_1_0valueslessthan(to_date('2011-3-10 1:00:00','yyyy-mm-dd hh24:mi:ss'))tablespacetbs_1)

;

 

--按天建分区

...

interval(numtodsinterval(1,'day'))

...

 

--按月建分区

 

interval(numtoyminterval(1,'month'))

 

--按年建分区

 

interval(numtoyminterval(1,'year'))

 

以上都是以time这个date数据类型的作为分区字段,interval分区还允许number类型的作为分区字段,其他的数据类型均不可以。

 

--以number类型作为分区字段

 

createtabletab_1(timedate,

idnumber,

city_idnumber,

value1number,

value2varchar2(10)

)partitionbyrange(id)

 interval(100)storein(tbs_1)

 (partitionp_tab_1_0valueslessthan(1000)tablespacetbs_1)

;

 

 

试验:

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  interval(numtodsinterval(1,'hour')) store in (tbs_1)

 8  ( 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)

 9 ;

 

表已创建。

 

SQL>

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

 

PARTITION_NAME                PARTITION_POSITION                             

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

P_TAB_1_0                                      1                             

 

SQL>INSERTINTO tab_1 values(to_date('2011-3-10 2: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                             

SYS_P161                                       2                             

 

SQL> select * from tab_1;

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11             1        10         1 a                             

 

SQL>INSERTINTO tab_1 values(to_date('2011-3-10 5: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                             

SYS_P161                                       2                             

SYS_P162                                       3                             

---增长了一个,而不是多个

SQL> rollback;

 

回退已完成。

 

SQL> select * from tab_1;

 

未选定行

---在rollback之后,经由自动创建的分区并没有隐含commit之前insert的数据,而interval分区不能手动add partition,只能drop,当然drop也有限制,见下方描述。

 

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_P161                                       2                             

SYS_P162                                       3                             

 

SQL> alter table tab_1 drop partition p_tab_1_0;

alter table tab_1 drop partition p_tab_1_0

                                *

第1行出现错误:

ORA-14758:不能删除范围段中的最后一个分区

 

 

SQL> alter table tab_1 drop partition sys_p161;

 

表已更改。

 

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_P162                                       2

 

SQL> alter table tab_1 drop partition sys_p162;

 

表已更改。

 

SQL> truncate table tab_1;

 

表被截断。

 

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

 

已创建1行。

 

SQL> select * from tab_1;

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11             1        10         1 a                             

 

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_P164                                       2                                                         

 

SQL> alter table tab_1

 2 add partitionp000 values less than (to_date('2011-3-11 19:00:00','yyyy-mm-dd hh24:mi:ss'))

 3 ;

alter table tab_1

           *

第1行出现错误:

ORA-14760:不允许对间隔分区对象执行ADD PARTITION

 

--已经隐含commit了,drop partition也会隐含commit了,有兴趣的可以试试

SQL> select * from tab_1;

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11             1        10         1 a                             

 

SQL>rollback;

 

回退已完成。

 

SQL> select * from tab_1;

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11             1        10         1 a

 

试验:drop partition

 

SQL> alter table tab_1 drop partitionsys_p169;

 

表已更改。

 

SQL> alter table tab_1 drop partitionfor(1004);

 

表已更改。

以上两种方式均可,第一种是传统删除分区的方式,第二种方式是根据数值来删除分区,其中,在for()中,需指定所要删除的分区中的任一数值。

在merge操作时,也可以用for()这种写法,如:

Alter table xxx merge partitions for(),for() into partition xxx;

另外,所有其他的分区类型、其他的分区命令也都支持这种写法。

 

试验2:drop partition之后,再插入属于被删除分区范围内的数据时,分区会再次重新创建,这个与range不同,在range时,drop掉其中一个分区之后,如再插入属于原分区的数据时,会直接到该分区的下一个分区,这也是interval分区方式与range的区别之一。

 

SQL>

SQL>

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_P172                                       2                             

SYS_P171                                       3                             

 

SQL> select * from tab_1 partition(p_tab_1_0);

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11           999        10         1 a                             

10-3月-11            10        10         1 a                             

 

SQL> select * from tab_1 partition(sys_p172);

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11          1000        10         1 a                             

 

SQL> select * from tab_1 partition(sys_p171);

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11          1100        10         1 a                             

 

SQL> alter table tab_1droppartitionsys_p172;

 

表已更改。

 

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_P171                                       2                             

 

SQL> INSERT INTO tab_1 values(to_date('2011-3-10 0:00:00','yyyy-mm-dd hh24:mi:ss'),1000,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> select * from tab_1 partition(p_tab_1_0);

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11           999        10         1 a                             

10-3月-11            10        10         1 a                             

 

SQL> select * from tab_1 partition(sys_p173);

 

TIME                  ID   CITY_ID    VALUE1 VALUE2                        

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

10-3月-11          1000        10         1 a                             

 

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

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

注册时间:2009-03-10

  • 博文量
    30
  • 访问量
    775793