Alter table move lob results in unusable indexes

Alter table move lob creates unusable indexes

Recently came across this problem after moving a LOB column. After LOB moved the indexes on the table become unusable, even if the LOB and its index do not have any relation to the other indexes existing on the table.

SQL> create table test (a number, b varchar2(20), c blob);
SQL> alter table test add constraint t_pk primary key (a);
SQL> create index t_ix1 on test(b);
SQL> insert into test values (1,'xx',empty_blob());
SQL> insert into test values (2,'yy',empty_blob());
SQL> commit;

— No UNUSABLE indexes, before LOB move

SQL> select index_name from user_indexes where status ='UNUSABLE';
no rows selected

— Move LOB to new tablespace

SQL> alter table test move lob (c) store as (tablespace idx_tbs);
Table altered.

— UNUSABLE indexes, after LOB move

SQL> select index_name from user_indexes where status ='UNUSABLE';
INDEX_NAME
-----------
T_PK
T_IX1

Index t_ix1 and t_pk are invalid after LOB move.

Initially I thought it’s a bug. I also found a metalink note 1228324.1. But at the end of the note, it points to Oracle Documentation, which states that it’s an expected behaviour. After moving a LOB column any existing table indexes must be rebuilt.

So in this case, rebuild indexes

SQL> alter index t_pk rebuild online; 
SQL> alter index t_ix1 rebuild online;

This could be problematic & should be carefully planned.

 

Advertisements
This entry was posted in Oracle Bugs, Oracle Index and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s