--创建分区表create table test(x int,y int) partition by range(x) ( partition part0 values less than (100), partition p
--创建分区表
create table test(x int,y int)
partition by range(x)
(
partition part0 values less than (100),
partition part1 values less than (maxvalue)
);
--创建索引
create index idx_test_x on test(x) local;
create index idx_test_y on test(y);
--创建交换堆表
create table tmp_test(x int, y int);
--创建索引
create index idx_tmp_test_x on tmp_test(x);
--初始化分区表数据
begin
for i in 1..200 loop
insert into test values(i,i-1);
end loop;
commit;
end;
--初始化堆表数据
begin
for i in 1..50 loop
insert into tmp_test values(i,i-1);
end loop;
commit;
end;
--查看表的元数据
sql> select object_name,
2 subobject_name,
3 object_id,
4 data_object_id,
5 object_type,
6 status
7 from dba_objects
8 where object_name in ('test', 'tmp_test')
9 order by object_name;
object_name subobject_name object_id data_object_id object_type status
-------------------- -------------------- ---------- -------------- ------------------- -------
test part1 60040 60040 table partition valid
test part0 60039 60039 table partition valid
test 60038 table valid
tmp_test 60045 60045 table valid
----索引的元数据
sql> select object_name,
2 subobject_name,
3 object_id,
4 data_object_id,
5 object_type,
6 status
7 from dba_objects
8 where object_name in ('idx_test_x', 'idx_test_y','idx_tmp_test_x');
object_name subobject_name object_id data_object_id object_type status
-------------------- -------------------- ---------- -------------- ------------------- ------
idx_tmp_test_x 60047 60047 index valid
idx_test_y 60044 60044 index valid
idx_test_x 60041 index valid
idx_test_x part0 60042 60042 index partition valid
idx_test_x part1 60043 60043 index partition valid
--交换表及已有的索引
alter table test exchange partition part0 with table tmp_test including indexes;
--查看数据已交换成功
sql> select count(*) from tmp_test;
count(*)
----------
99
sql> select count(*) from test partition(part0);
count(*)
----------
50
--查看表元数据的变化，可以得出结论exchange 只是交换的是数据段编号
sql> select object_name,
2 subobject_name,
3 object_id,
4 data_object_id,
5 object_type,
6 status
7 from dba_objects
8 where object_name in ('test', 'tmp_test')
9 order by object_name;
object_name subobject_name object_id data_object_id object_type status
-------------------- -------------------- ---------- -------------- ------------------- -------
test part1 60040 60040 table partition valid
test part0 60039 60045 table partition valid
test 60038 table valid
tmp_test 60045 60039 table valid
--查看索引元数据的变化，可以看出index的变化:交换了段编号
sql> select object_name,
2 subobject_name,
3 object_id,
4 data_object_id,
5 object_type,
6 status
7 from dba_objects
8 where object_name in ('idx_test_x', 'idx_test_y','idx_tmp_test_x','idx_tmp_test_y');
object_name subobject_name object_id data_object_id object_type status
-------------------- -------------------- ---------- -------------- ------------------- -------
idx_tmp_test_x 60047 60042 index valid
idx_test_y 60044 60044 index valid
idx_test_x 60041 index valid
idx_test_x part0 60042 60047 index partition valid
idx_test_x part1 60043 60043 index partition valid
--查看索引的状态
--发现分区表test的global索引已不可用，需要重新创建，local的分区索引显示为n/a，我们需要查询另外一个视图来确定是否可用
--经测试在交换分区的时候 加上 update indexes 则可以避免global索引失效的情况。
sql> select index_name,table_name,status from dba_indexes where table_name in ('test','tmp_test');
index_name table_name status
------------------------------ ------------------------------ --------
idx_test_x test n/a
idx_test_y test unusable
idx_tmp_test_x tmp_test valid
--local分区索引仍然是有效的
sql> select index_name,status from user_ind_partitions where index_name in ('idx_test_x');
index_name status
------------------------------ --------
idx_test_x usable
idx_test_x usable