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博客

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

最新文章