Oracle修改字段类型后索引错误的解决方案


起因:

由于在查询中需要用到 UNION ALL 操作,而表中的存在 LONG 类型字段,操作无法完成,根据具体业务场景,将 LONG 类型字段修改为 CLOB 类型。修改后,UNION ALL 操作可行,但是子表的增删改操作出现了问题,提示为父表的 INDEX 不可用。

这里顺便说一句:如果要从VARCHAR2类型修改为特殊类型CLOB,那么不能直接从VARCHAR2转换为CLOB,需要使用LONG类型,来做一个过渡(可以先修改为LONG类型,然后从LONG修改为CLOB)。

解决方案:

1.同事前不久也遇到了这个问题,他的解决方案是:备份旧的表,删除表,然后再导入数据,并且依然需要重建主键等。对没有直接操作权限的ORCALE服务器恐怕有难度。所以这里归根结底还是drop and re-create。

2.重建索引。Oracle重建索引有多种方式,如 drop and re-create、rebuild、rebuild online等。这里采用的是第一种方式删除创建。需要的一些脚本如下(TB_SCENERY是父表【景点表】,TB_SCENERY_TICKETS是子表【景点门票表】):

  1. --查询主外键,表名一定要大写   
  2. select constraint_name from user_constraints where table_name = 'TB_SCENERY';   
  3. select constraint_name from user_constraints where table_name = 'TB_SCENERY_TICKETS';   
  4.   
  5. --删除主外键,注意大小写   
  6. alter table TB_SCENERY drop constraint SCENERY_PK_ID;   
  7. alter table TB_SCENERY_TICKETS drop constraint SCENERY_ID;   
  8.   
  9. --增加主键(增加主键时会自动建立关于主键的索引)   
  10. alter table TB_SCENERY add constraint SCENERY_PK_ID primary key (ID) ;    
  11.   
  12. --增加外键   
  13. alter table TB_SCENERY_TICKETS add constraint SCENERY_PK_ID foreign key (SCENERY_ID)  references TB_SCENERY (ID);   
  14.   
  15. --查询表的相关索引   
  16. select index_name,index_type,table_name from user_indexes where table_name='TB_SCENERY';   
  17.   
  18. --删除索引[强制]   
  19. DROP INDEX SCENERY_PK_ID [FORCE];   
  20.   
  21. --查询哪些表没有建立索引   
  22. SELECT table_name FROM User_tables t  WHERE NOT EXISTS (SELECT table_name FROM User_constraints c WHERE constraint_type = 'P' AND t.table_name=c.table_name)  

这里的步骤是:

1.查询子表的外键名称,删除对应的外键,以及外键对应的索引

2.查询主表的主键名称,删除对应的主键,以及主键对应的索引

3.建立主表主键(主键索引会自动建立),建立子表外键,建立外键索引

3.drop and re-create 方式的有点是速度快,缺点是会影响原有的SQL查询,如果考虑这个影响那就可以采用rebuild的方式(IDX_TEST_C1这是索引名称):

ALTER INDEX IDX_TEST_C1 REBUILD;  

其实开始是尝试的是rebuild的方式,但是失败了,无可奈何只能采用drop and re-create 方式。

相关内容