oracle 10g分区表,oracle10g-11gR2 分区表汇总一

在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,

2id number,

3city_id number,

4value1 number,

5value2 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')) ,

8partition 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

02-3月-111101 a

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

02-4月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

02-3月-111101 a

01-4月-112202 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,

2id number,

3city_id number,

4value1 number,

5value2 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')) ,

8partition 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_no_par;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

SQL> select * from t_no_par;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a----该数据符合分区0

02-4月-113303 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

02-4月-113303 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b ---该数据被强行交换到分区0

01-3月-111101 a

02-4月-113303 c

SQL> select * from t_par_range partition(p_t_par_range_0);

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

SQL> select * from t_par_range partition(p_t_par_range_1);

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

02-4月-113303 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,

2id number,

3city_id number,

4value1 number,

5value2 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')) ,

8partition 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

01-4月-112202 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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

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

INDEX_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0UNUSABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

---交换之后，分区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_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0UNUSABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

---交换回分区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_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0UNUSABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1UNUSABLE

--使用update indexes仍然无效

SQL> select * from t_no_par;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-4月-112202 b

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 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_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0UNUSABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1UNUSABLE

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

SQL> select * from t_no_par;

未选定行

SQL> select * from t_par_range;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a

01-4月-112202 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_NAMEPARTITION_NAMESTATUS

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

IND_T_PAR_RANGEP_T_PAR_RANGE_0UNUSABLE

IND_T_PAR_RANGEP_T_PAR_RANGE_1USABLE

----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;

TIMEIDCITY_IDVALUE1 VALUE2

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

01-3月-111101 a01-4月-112202 b