alter table modify Long=>LOB会重建整张表

refer to 11 Migrating Columns from LONGs to LOBs in 《Oracle Database Application Developer's Guide - Large Objects》：

Note:

The ALTER TABLE statement copies the contents of the table into a new space, and frees the old space at the end of the operation. This temporarily doubles the space requirements.

 

所以当我们使用ALTER TABLE MODIFY LONG to LOB之后，其实整张表被重建了，因此，所有的index需要重建。

以前我没注意这个知识点，特此记录一下。

 

这里可以通过rowid都变了来验证。

 

SQL> create table testlong (id number,name long);

Table created.

 

SQL> insert into testlong select object_id,object_name from dba_objects;

10065 rows created.

 

SQL> commit;

Commit complete.

 

SQL> create index testlongidx on testlong(id);

Index created.

 

SQL> create table beforeconvert as select id,rowid rid from testlong; 

Table created.

 

SQL>  alter table testlong modify name clob;

Table altered.

 

SQL> create table afterconvert as select id,rowid rid from testlong; 

Table created.

 

SQL> select count(*) from beforeconvert b ,afterconvert a
  2  where a.id=b.id
  3  and a.rid=b.rid;

  COUNT(*)
----------
     0
    
SQL> select status from dba_indexes where index_name='TESTLONGIDX';    

STATUS
--------
UNUSABLE

 

   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15415488/viewspace-672483/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15415488/viewspace-672483/