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

Showing posts with label LOB. Show all posts
Showing posts with label LOB. Show all posts

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