oracle10g-11gR2 分区表汇总一-CSDN博客

在oracle10g（在10.2.0.4中测试）中：分区表共分为：range、list、hash，复合分区可以是：range-list、range-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

分区表中的index：global-index、local-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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10037372/viewspace-689793/