今天有人在QQ上问，如何将global的索引改成分区索引？由于在同一个列上不能建不同名的索引，不然会报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> create index ind_tab2_1 on tab2(a); Index created. Elapsed: 00:00:01.20 SQL> SQL> create index ind_tab2_pindex on tab2(a) 2 local (partition ip1,partition ip2,partition ip3); create index ind_tab2_pindex on tab2(a) * ERROR at line 1: ORA-01408: such column list already indexed |
除了删除重建索引外，我们其实可以用在线重定义的方法来重构索引。
下面是一个例子：
1、原表和其索引的建立：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SQL> create table tab2 (a varchar2(20),b varchar2(20),c varchar2(20),d varchar2(20)) 2 partition by hash(a) 3 (partition p1,partition p2,partition p3); Table created. Elapsed: 00:00:00.98 SQL> SQL> SQL> SQL> SQL> SQL> create index idx_tab2 on tab2(a); Index created. Elapsed: 00:00:00.18 SQL> SQL> |
2、插入数据：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
SQL> insert into tab2 values('aa','bb','cc','dd'); 1 row created. Elapsed: 00:00:02.17 SQL> / 1 row created. Elapsed: 00:00:00.01 SQL> / 1 row created. Elapsed: 00:00:00.06 SQL> insert into tab2 select * from tab2; 3 rows created. Elapsed: 00:00:00.42 SQL> / 6 rows created. Elapsed: 00:00:00.00 SQL> / 12 rows created. Elapsed: 00:00:00.03 SQL> / 24 rows created. Elapsed: 00:00:00.01 SQL> commit; Commit complete. Elapsed: 00:00:00.01 |
3、创建目标表，且索引是local的分区索引：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SQL> create table tab3 (a varchar2(20),b varchar2(20),c varchar2(20),d varchar2(20)) 2 partition by hash(a) 3 (partition p1,partition p2,partition p3); Table created. Elapsed: 00:00:03.59 SQL> SQL> SQL> create index idx_tab1 on tab3(a) 2 local 3 (partition ip1,partition ip2,partition ip3); Index created. Elapsed: 00:00:01.87 SQL> |
4、进行在线重定义，注意我们在这里是用rowid在进行重定义的，因此options_flag=2,不然会报错ora-12089:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SQL> conn / as sysdba Connected. SQL> SQL> SQL> SQL> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','TAB2'); BEGIN dbms_redefinition.CAN_REDEF_TABLE('TEST','TAB2'); END; * ERROR at line 1: ORA-12089: cannot online redefine table "TEST"."TAB2" with no primary key ORA-06512: at "SYS.DBMS_REDEFINITION", line 137 ORA-06512: at "SYS.DBMS_REDEFINITION", line 1478 ORA-06512: at line 1 Elapsed: 00:00:01.59 SQL> SQL> exec dbms_redefinition.CAN_REDEF_TABLE('TEST','TAB2',options_flag=>2); PL/SQL procedure successfully completed. Elapsed: 00:00:00.06 SQL> SQL> SQL> SQL> SQL> SQL> exec dbms_redefinition.START_REDEF_TABLE('TEST','TAB2','TAB3',options_flag=>2); PL/SQL procedure successfully completed. Elapsed: 00:00:24.07 SQL> SQL> |
5、最后完成在线重定义（之前可以多次同步目标表）：
|
1 2 3 4 5 6 |
SQL> exec dbms_redefinition.FINISH_REDEF_TABLE('TEST','TAB2','TAB3'); PL/SQL procedure successfully completed. Elapsed: 00:00:09.57 SQL> |
至此，tab2表上的global索引已经改成local索引，后续的措施就是rename索引名至正常的索引名和去掉hidden列，在这里就不继续展开了。
一条评论
之前也碰到过一次这样的问题，不过是先挑一个列最短的然后和原先列一起建一个复合索引，然后删除原索引，让SQL暂时使用复合索引。然后再建一个分区的索引，然后再把符合索引删除。这样风险其实比较高的，主要在如果删除原索引后SQL不走复合索引就玩完拉。但如果数据量很大，SQL执行又不是很频繁的话，应该比在线重定义简单点，而且少产生很多REDO