ITPub博客

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

oracle10g-11gR2 分区表汇总一

原创 Linux操作系统 作者:jack22220613 时间:2011-03-17 16:56:42 0 删除 编辑

oracle10g(在10.2.0.4中测试)中:分区表共分为:rangelisthash,复合分区可以是:range-listrange-hash

测试之前先创建4个表空间 tbs_1,tbs_2,tbs_3,tbs_4

1.      建分区表

2.         Range

SQL> create table tab_1 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by range(time)

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

;

 

 

3.         List

 

SQL> create table tab_2 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by list(city_id)

 ( partition p_tab_2_0 values(10) tablespace tbs_1)

;

 

 

 

4.         Hash

 

SQL> create table tab_3 (time date,

id number,

city_id number,

value1 number,

value2 varchar2(10)

) partition by hash(id)

 Partitions 2

Store in (tbs_1,tbs_2)

;

 

 

5.         复合

1)         Range-list

 

create table tab_4(time date,

id number,

city_id number,

value1 number,

value2 varchar2(10))

partition by range(time)

  subpartition by list(city_id)

  subpartition template

  (

   subpartition p_tab_4_01 values(10) tablespace tbs_1,

   subpartition p_tab_4_02 values(20) tablespace tbs_2,

   subpartition p_tab_4_03 values(30) tablespace tbs_3,

   subpartition p_tab_4_04 values(default) tablespace tbs_4)

  (

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

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

   ) ;

 

2)         Range-hash

 

create table tab_5(time date,

id number,

city_id number,

value1 number,

value2 varchar2(10))

partition by range(time)

  subpartition by hash(id)

  subpartition template

  (

   subpartition p_tab_5_01 tablespace tbs_1,

   subpartition p_tab_5_02 tablespace tbs_2,

   subpartition p_tab_5_03 tablespace tbs_3,

   subpartition p_tab_5_04 tablespace tbs_4)

  (

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

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

   ) ;

 

2. 建分区表index

分区表中的indexglobal-indexlocal-index

1.         Unique index

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

 

SQL> create unique index ind_ttt on tab_1(id,city_id) local;

 

create unique index ind_ttt on tab_1(id,city_id) local

 

ORA-14039: 分区列必须构成 UNIQUE 索引的关键字列子集

SQL> create unique index ind_ttt on tab_1(time,city_id) ;

 

Index created

 

如果此时需要创建的unique index是要在复合分区表上创建,那么该index创建时就必须包含两个分区键(一级、二级分区键),才能创建为local index;而创建global index没有此限制。

 

Global index 在遇到下列操作时,会标记为unusable

ADD (HASH)        -------- > range/list 不会无效

COALESCE (HASH)  -------- > range/list 不会无效

DROP

EXCHANGE

MERGE

MOVE

SPLIT

TRUNCATE

 

有一个办法能使上述情况下失效的index不失效,就是加上 update indexes;

如: alter table xx drop partition xx update indexes;

3.    交换分区

3.1 非分区表与非分区表的交换   

不支持;

 

试验:

SQL> create table t_no_par_01(id number,var varchar2(100));

 

表已创建。

 

SQL> create table t_no_par_02(id number,var varchar2(100));

 

表已创建。

 

SQL> insert into t_no_par_01 values(1,'a');

 

已创建 1 行。

 

SQL> insert into t_no_par_01 values(2,'b');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> alter table t_no_par_01 exchange with table t_no_par_02;

alter table t_no_par_01 exchange with table t_no_par_02

                                 *

1 行出现错误:

ORA-14155: 缺失 PARTITION SUBPARTITION 关键字

 

3.2 分区表与非分区表的交换   

 支持;

 

试验:

3.2.1 没有任何约束和索引(正常交换)

SQL> create table t_par_range (time date,

  2      id number,

  3      city_id number,

  4      value1 number,

  5      value2 varchar2(10)

  6      ) partition by range(time)

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

  8         partition p_t_par_range_1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

  9         )

 10     ;

 

表已创建。

 

SQL> create table t_no_par(time date,id number,city_id number,value1 number,value2 varchar2(10));

 

表已创建。

 

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

 

已创建 1 行。

 

SQL> insert into t_par_range values(to_date('2011-4-1 00:00:00','yyyy-mm-dd hh24:mi:ss'),2,20,2,'b');

 

已创建 1 行。

 

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

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

 

表已更改。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

02-3 -11              1         10          1 a                               

01-4 -11              2         20          2 b                              

 

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

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-4 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

02-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

3.2.2 没有任何约束和索引(尝试非正常交换—without validation)

 

SQL> drop table t_par_range purge;

 

表已删除。

 

SQL> drop table t_no_par purge;

 

表已删除。

 

SQL> create table t_par_range (time date,

  2  id number,

  3  city_id number,

  4  value1 number,

  5  value2 varchar2(10)

  6  ) partition by range(time)

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

  8     partition p_t_par_range_1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

  9     )

 10  ;

 

表已创建。

 

SQL> create table t_no_par(time date,id number,city_id number,value1 number,value2 varchar2(10));

 

表已创建。

 

SQL>

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

 

已创建 1 行。

 

SQL> insert into t_par_range values(to_date('2011-4-1 00:00:00','yyyy-mm-dd hh24:mi:ss'),2,20,2,'b');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;                 -------- 先交换分区 p_t_par_range_1

 

表已更改。

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                               

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par                        -------- 再交换分区 p_t_par_range_0,出错

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;

 

表已更改。

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;                  -------交换了分区0

 

表已更改。

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> insert into t_no_par values(to_date('2011-4-2 00:00:00','yyyy-mm-dd hh24:mi:ss'),3,30,3,'c');

                                           ----该数据不符合分区0

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                          

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

01-3 -11              1         10          1 a       ----该数据符合分区0                  

02-4 -11              3         30          3 c       ----该数据符合分区1                        

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_0,p_t_par_range_1 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0,p_t_par_range_1 with table t_no_par

                                                          *

1 行出现错误:

ORA-00970: 缺失 WITH 关键字

-----两个分区同时都交换,语法不支持

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par without validation; ---使用without validation

 

表已更改。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                          

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

01-3 -11              1         10          1 a                              

02-4 -11              3         30          3 c                               

-------由于交换时使用了without validation,这两个数据都被交换到了分区1

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par

                                                                      *

1 行出现错误:

ORA-14099: 未对指定分区限定表中的所有行

 

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par without validation;

 

表已更改。

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b ---该数据被强行交换到分区0                          

01-3 -11              1         10          1 a                              

02-4 -11              3         30          3 c                              

 

SQL> select * from t_par_range partition(p_t_par_range_0);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select * from t_par_range partition(p_t_par_range_1);

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

02-4 -11              3         30          3 c                              

---这两个查询结果证明了without validation的强制性

SQL> drop table t_par_range purge;

 

表已删除。

 

SQL> drop table t_no_par purge;

 

表已删除。

3.2.3 没有任何约束和索引(尝试非正常交换数据类型大小不一致)

SQL> create table t_par_range (time date,

  2  id number,

  3  city_id number,

  4  value1 number,

  5  value2 varchar2(10)

  6  ) partition by range(time)

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

  8     partition p_t_par_range_1 values less than (to_date('2011-4-10 10:00:00','yyyy-mm-dd hh24:mi:ss'))

  9     )

 10  ;

 

表已创建。

 

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

 

已创建 1 行。

 

SQL> insert into t_par_range values(to_date('2011-4-1 00:00:00','yyyy-mm-dd hh24:mi:ss'),2,20,2,'b');

 

已创建 1 行。

 

SQL> commit;

 

提交完成。

 

SQL> create table t_no_par(time date,id number,city_id number,value1 number,value2 varchar2(20));

 

表已创建。

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par

*

1 行出现错误:

ORA-14097: ALTER TABLE EXCHANGE PARTITION 中的列类型或大小不匹配

 

 

SQL> alter table t_no_par modify value2 varchar2(10);

 

表已更改。

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

 

表已更改。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

 

表已更改。

 

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

3.2.4 normal索引(分区表有本地索引,非分区表无索引)

SQL> create index ind_t_par_range on t_par_range(id) local;

 

索引已创建。

 

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

 

表已更改。

 

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                               

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                UNUSABLE         

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

---交换之后,分区0索引失效

SQL> alter table t_par_range exchange partition p_t_par_range_0 with table t_no_par;

 

表已更改。

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                UNUSABLE         

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE           

---交换回分区0,索引仍然失效

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par UPDATE INDEXes;

 

表已更改。

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                UNUSABLE         

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                UNUSABLE         

--使用update indexes仍然无效

SQL> select * from t_no_par;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-4 -11              2         20          2 b                              

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

 

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par UPDATE INDEXes;

 

表已更改。

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                UNUSABLE         

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                UNUSABLE         

                                                                                

--交换回分区,使用update indexes仍然无效

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                              

01-4 -11              2         20          2 b                              

 

SQL> alter index ind_t_par_range rebuild partition p_t_par_range_1;

 

索引已更改。

 

SQL> select INDEX_NAME,PARTITION_NAME,STATUS from User_Ind_Partitions where INDEX_name='IND_T_PAR_RANGE';

 

INDEX_NAME                     PARTITION_NAME                 STATUS           

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

IND_T_PAR_RANGE                P_T_PAR_RANGE_0                UNUSABLE         

IND_T_PAR_RANGE                P_T_PAR_RANGE_1                USABLE         

----rebuild 之后,索引usable

SQL> alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par including indexes;

alter table t_par_range exchange partition p_t_par_range_1 with table t_no_par including indexes

                                                                      *

1 行出现错误:

ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配

 

---交换时,使用including indexes,此时非分区表无对应index,报错

SQL> select * from t_no_par;

 

未选定行

 

SQL> select * from t_par_range;

 

TIME                   ID    CITY_ID     VALUE1 VALUE2                         

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

01-3 -11              1         10          1 a                               

01-4 -11              2         20          2 b               

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

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

注册时间:2009-05-13

  • 博文量
    94
  • 访问量
    357098