将global的索引改成分区索引

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/study-note/change-global-index-to-local/

今天有人在QQ上问，如何将global的索引改成分区索引？由于在同一个列上不能建不同名的索引，不然会报错：

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、原表和其索引的建立：

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、插入数据：

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的分区索引：

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:

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、最后完成在线重定义（之前可以多次同步目标表）：

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列，在这里就不继续展开了。

· 【文章发布信息】发表于： 2009-02-07 @ 01:30:40 · ||分类： Study note

1 条评论 »

  1. big_bear 于 2009-02-09 @ 17:02:39 留言 ：

    之前也碰到过一次这样的问题，不过是先挑一个列最短的然后和原先列一起建一个复合索引，然后删除原索引，让SQL暂时使用复合索引。然后再建一个分区的索引，然后再把符合索引删除。这样风险其实比较高的，主要在如果删除原索引后SQL不走复合索引就玩完拉。但如果数据量很大，SQL执行又不是很频繁的话，应该比在线重定义简单点，而且少产生很多REDO

RSS 为此帖反馈评论 · 反向跟踪 网站

留条评论