间隔分区使用

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/167606/viewspace-744742/