ITPub博客

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

oracle10g--11gR2分区表汇总八

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

11g(11.2.0.1)

1.      建分区表

10g中的用法在11仍可用,不再赘述,下面只是列出11gR2中特有的用法:

1.1 interval (range分区的扩展)

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

 

--按小时建分区

create table tab_1 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by range(time)

 interval(numtodsinterval(1,'hour')) store in (tbs_1)

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

;

 

--按天建分区

...

interval(numtodsinterval(1,'day'))

...

 

--按月建分区

 

interval(numtoyminterval(1,'month'))

 

--按年建分区

 

interval(numtoyminterval(1,'year'))

 

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

 

--number类型作为分区字段

 

create table tab_1 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by range(id)

 interval(100) store in (tbs_1)

 ( partition p_tab_1_0 values less than (1000) tablespace tbs_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> INSERT INTO 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> INSERT INTO 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> INSERT INTO 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 partition p000 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 partition sys_p169;

 

表已更改。

 

SQL> alter table tab_1 drop partition for(1004);

 

表已更改。

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

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

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

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

 

试验2drop 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_1 drop partition sys_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/10037372/viewspace-689885/,如需转载,请注明出处,否则将追究法律责任。

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

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    351318