利用分区表归档历史数据主要利用exchange partition,下图是一个将普通表转换为分区表并对历史数据进行归档的示图.
在将历史数据归档时使用了两次exchange partition,因为分区表不能直接和分区表的分区交换,在这里利用了records_exchanger表作为中间表进行分区交换.
这里不介绍DBMS_REDEFINITION包如何使用,如何利用DBMS_REDEFINITION包将普通表转换为分区表,这只对将普遍表转换为分区表,原表(在这个例子中是records)的索引会建立什么类型的索引,进行exchange partition时索引status的变化.
将普通表转换为之前的索引的类型
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
-------------------- ---------- ------------------ ----------------
INDEX_RECORDS_01 NORMAL NONUNIQUE VALID
PK_RECORDS NORMAL UNIQUE VALID
SQL> SELECT index_name, partition_name FROM user_ind_partitions;
no rows selected
将表转换为分区表后索引的类型
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
-------------------- ---------- ------------------ ----------------
INDEX_RECORDS_01 NORMAL NONUNIQUE N/A
PK_RECORDS NORMAL UNIQUE VALID
SQL> SELECT index_name, partition_name FROM user_ind_partitions;
INDEX_NAME PARTITION_NAME
-------------------- -------------------------
INDEX_RECORDS_01 P_20090601
INDEX_RECORDS_01 P_20090901
--STATUS:用于标记非分区索引是否可用
从上面的结果可以看出非unique的索引INDEX_RECORDS_01转换为了分区索引即local索引
执行exchange partition前中间表(records_exchanger)索引的状态:
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS_EXCHANGER';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
---------------------- ---------- ---------- --------
IND_RECORDS_EXCHANGER NORMAL NONUNIQUE VALID
PK_RECORDS_EXCHANGER NORMAL UNIQUE VALID
执行exchange partiton因为有非local的索引,不能使用including indexes选项,下面分别对使用和不使用update global indexes做测试
不使用update global indexes
SQL> ALTER TABLE records EXCHANGE PARTITION p_20090601 WITH TABLE records_exchanger WITHOUT VALIDATION;
查看索引的状态
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS_EXCHANGER';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
---------------------- ---------- ---------- --------
IND_RECORDS_EXCHANGER NORMAL NONUNIQUE UNUSABLE
PK_RECORDS_EXCHANGER NORMAL UNIQUE UNUSABLE
中间表的索引全部失效
SQL> SELECT index_name, partition_name, status FROM user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
---------------------- ------------------------------ --------
INDEX_RECORDS_01 P_20090601 UNUSABLE
INDEX_RECORDS_01 P_20090901 USABLE
local索引对应的分区的索引失效
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
---------------------- ---------- ---------- --------
INDEX_RECORDS_01 NORMAL NONUNIQUE N/A
PK_RECORDS NORMAL UNIQUE UNUSABLE
global索引失败
重建索引,使用update global indexes进行测试
SQL> ALTER TABLE records EXCHANGE PARTITION p_20090601 WITH TABLE records_exchanger WITHOUT VALIDATION UPDATE GLOBAL INDEXES;
records表索引的状态:
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
---------------------- ---------- ---------- --------
INDEX_RECORDS_01 NORMAL NONUNIQUE N/A
PK_RECORDS NORMAL UNIQUE VALID
SQL> SELECT index_name, partition_name, status FROM user_ind_partitions;
INDEX_NAME PARTITION_NAME STATUS
---------------------- ------------------------------ --------
INDEX_RECORDS_01 P_20090901 USABLE
INDEX_RECORDS_01 P_20090601 UNUSABLE
可以看到global索引仍然有郊,但在P_20090601 上local索引已失效
records_exchanger索引的状态:
SQL> SELECT index_name, index_type, uniqueness, status FROM user_indexes
2 WHERE table_name='RECORDS_EXCHANGER';
INDEX_NAME INDEX_TYPE UNIQUENESS STATUS
---------------------- ---------- ---------- --------
IND_RECORDS_EXCHANGER NORMAL NONUNIQUE UNUSABLE
PK_RECORDS_EXCHANGER NORMAL UNIQUE UNUSABLE
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7419833/viewspace-611500/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7419833/viewspace-611500/