分区交换小实验

最新推荐文章于 2021-04-04 07:13:25 发布

cuiping5771

最新推荐文章于 2021-04-04 07:13:25 发布

阅读量101

点赞数

文章标签：数据库

关于分区表的分区交换（partition exchange）的小实验

今天学习到了分区表的分区交换，感觉这个功能很强大啊，
于是我就想，Oracle到底是怎么交换一个分区和另一个表里的数据的呢？
于是就做了下面的小实验，
结果证明了，在分区交换的时候，实际上分区和被交换表中的数据的物理存储都没有任何变化，
其实分区交换，实际上就是修改了两个 segment 的名字和相关的引用而已，
这也从另一个方面说明了，为什么在交换分区的时候，分区和被交换表的结构要一致的问题。

下面是实验的过程：
其中get_object 脚本是获取当前 schema 的所有对象信息。

TEST:154@HONGYE > @get_object

no rows selected

1、创建一个分区表，并基于分区键创建一个局部索引。

TEST:154@HONGYE > create table part(id number,name varchar2(10))
  2  partition by range(id)
  3  (partition p1 values less than(100),
  4  partition p2 values less than(10000));

Table created.

TEST:154@HONGYE > create index idx_part_id_local on part(id) local;

Index created.

2、向分区表中插入一些数据，保证在两个分区中都有数据，以便观察结果。

TEST:154@HONGYE > begin
  2  for i in 1..20 loop
  3  insert into part values(i*10,'name'||(i*10));
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

TEST:154@HONGYE > select * from part partition(p1);

      ID NAME
---------- ----------
      10 name10
      20 name20
      30 name30
      40 name40
      50 name50
      60 name60
      70 name70
      80 name80
      90 name90

9 rows selected.

TEST:154@HONGYE > select * from part partition(p2);

      ID NAME
---------- ----------
   100 name100
   110 name110
   120 name120
   130 name130
   140 name140
   150 name150
   160 name160
   170 name170
   180 name180
   190 name190
   200 name200

11 rows selected.

TEST:154@HONGYE > select * from part;

      ID NAME
---------- ----------
      10 name10
      20 name20
      30 name30
      40 name40
      50 name50
      60 name60
      70 name70
      80 name80
      90 name90
   100 name100
   110 name110
   120 name120
   130 name130
   140 name140
   150 name150
   160 name160
   170 name170
   180 name180
   190 name190
   200 name200

20 rows selected.

3、查看当前分区表和局部索引物理存储的文件号和块号。

TEST:154@HONGYE > desc dba_segments
Name                                                             Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER                                                                   VARCHAR2(30)
SEGMENT_NAME                                                       VARCHAR2(81)
PARTITION_NAME                                                    VARCHAR2(30)
SEGMENT_TYPE                                                       VARCHAR2(18)
TABLESPACE_NAME                                                 VARCHAR2(30)
HEADER_FILE                                                          NUMBER
HEADER_BLOCK                                                       NUMBER
BYTES                                                                      NUMBER
BLOCKS                                                                   NUMBER
EXTENTS                                                                   NUMBER
INITIAL_EXTENT                                                       NUMBER
NEXT_EXTENT                                                          NUMBER
MIN_EXTENTS                                                          NUMBER
MAX_EXTENTS                                                          NUMBER
PCT_INCREASE                                                          NUMBER
FREELISTS                                                                NUMBER
FREELIST_GROUPS                                                    NUMBER
RELATIVE_FNO                                                          NUMBER
BUFFER_POOL                                                          VARCHAR2(7)

TEST:154@HONGYE > col segment_name for a30

TEST:154@HONGYE > select segment_name,partition_name,segment_type,header_file,header_block from dba_segments where wner='TEST';

SEGMENT_NAME                PARTITION_NAME                SEGMENT_TYPE    HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ------------------ ----------- ------------
PART                                     P2                            TABLE PARTITION             6       123
PART                                     P1                            TABLE PARTITION             6       107
IDX_PART_ID_LOCAL             P2                            INDEX PARTITION             6       155
IDX_PART_ID_LOCAL             P1                            INDEX PARTITION             6       139

4、创建被交换表和索引，一定要保证该表与分区表的结构的完全一致，并且索引的列也完全一致。

TEST:154@HONGYE > create table exchange_with_p1(id number,name varchar2(10));

Table created.

TEST:154@HONGYE > create index idx_change on exchange_with_p1(id);

Index created.

5、查看交换之前的存储状态，注意相关对象的文件号和块号。

TEST:154@HONGYE > select segment_name,partition_name,segment_type,header_file,header_block from dba_segments where wner='TEST';

SEGMENT_NAME                PARTITION_NAME                SEGMENT_TYPE    HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ------------------ ----------- ------------
PART                                     P2                            TABLE PARTITION             6       123
PART                                     P1                           TABLE PARTITION             6       107
IDX_PART_ID_LOCAL             P2                            INDEX PARTITION             6       155
IDX_PART_ID_LOCAL             P1                           INDEX PARTITION             6       139
EXCHANGE_WITH_P1                                        TABLE                               6       171
IDX_CHANGE                                                           INDEX                               6       187

6 rows selected.

6、执行交换。

TEST:154@HONGYE > alter table part exchange partition p1 with table exchange_with_p1 including indexes;

Table altered.

7、查看交换之后的文件号和块号。

TEST:154@HONGYE > select segment_name,partition_name,segment_type,header_file,header_block from dba_segments where wner='TEST';

SEGMENT_NAME                PARTITION_NAME                SEGMENT_TYPE    HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ------------------ ----------- ------------
PART                                     P2                            TABLE PARTITION             6       123
PART                                     P1                             TABLE PARTITION             6       171
IDX_PART_ID_LOCAL             P2                            INDEX PARTITION             6       155
IDX_PART_ID_LOCAL             P1                             INDEX PARTITION             6       187
EXCHANGE_WITH_P1                                           TABLE                               6       107
IDX_CHANGE                                                       INDEX                               6       139

6 rows selected.

通过对比，可以发现，这次的交换过程中，
p1：(file#6 , block#107)--&gt(file#6 , block#171)
exchange_with_p1：(file#6 , block#171)--&gt(file#6 , block#107)
p1 index：(file#6 , block#139)--&gt(file#6 , block#187)
idx_change：(file#6 , block#187)--&gt(file#6 , block#139)
二者的物理存储刚好调换了，同样的情况在 index 上也是一样发生了交换，因为，在交换的时候指定了 including indexes。

8、查询交换后的数据，发现分区p1 中的数据已经没有了，而表 exchange_with_p1中则存储着 p1分区的数据，

若是没有 including indexes 呢？会发生什么情况呢，对此，再一次的交换，这次不指定 including indexes。

TEST:154@HONGYE > alter table part exchange partition p1 with table exchange_with_p1;

Table altered.

TEST:154@HONGYE > select segment_name,partition_name,segment_type,header_file,header_block from dba_segments where wner='TEST';

SEGMENT_NAME                PARTITION_NAME                SEGMENT_TYPE    HEADER_FILE HEADER_BLOCK
------------------------------ ------------------------------ ------------------ ----------- ------------
PART                                     P2                            TABLE PARTITION             6       123
PART                                     P1                         TABLE PARTITION             6       107
IDX_PART_ID_LOCAL             P2                            INDEX PARTITION             6       155
IDX_PART_ID_LOCAL             P1                         INDEX PARTITION             6       187
EXCHANGE_WITH_P1                                           TABLE                               6       171
IDX_CHANGE                                                         INDEX                               6       139

6 rows selected.

通过对比，可以发现，这次的交换过程中，
p1：(file#6 , block#171)--&gt(file#6 , block#107)
exchange_with_p1：(file#6 , block#107)--&gt(file#6 , block#171)
p1 index：(file#6 , block#187)--&gt(file#6 , block#187)
idx_change：(file#6 , block#139)--&gt(file#6 , block#139)
此次的交换中，表的存储发生了改变，但是索引还是原来的索引，

这是很危险的事情，可以查看 user_indexes 看看两个索引的状态

TEST:154@HONGYE > select index_name,status from user_indexes;

INDEX_NAME                   STATUS
------------------------------ --------
IDX_PART_ID_LOCAL             N/A
IDX_CHANGE                   UNUSABLE

可以发现，两个索引都不是正常状态了，必须重建，

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

转载于:http://blog.itpub.net/24465008/viewspace-680491/