Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Friday, November 16, 2007

LOB indexes cannot be renamed, rebuilt, or modified

Today, Anand asked me if it is possible to rebuild lob indexes. I knew that it is not possible, but wanted to explore why ? This is what Tom Kyte says in one of his responses in asktom.oracle.com:

You have a table with a LOB column in it.

A LOB is simply a pointer. It points to an index. the index points to the chunks that make up the LOB.

Hence when you create a LOB, you will always get a lob index created (to find the chunks
for the lob fast) and a segment that holds the lob data (chunks).

So by design, LOB indexes can not be renamed, rebuilt or modified.

If you ever try to issue alter index rebuild command on a LOB index you'll get an
ORA-02327 cannot create index on column with datatype LOB

1 comment:

raova said...

can we gather stats on CLOB index?. or do we really need stats on CLOB indexes?.
Ananda