LOB Indexing

Is LOB Index Created in Same Tablespace as LOB Data?

Question

Is the LOB index created for the LOB in the same tablespace as the LOB data?

Answer

The LOB index is created on the LOB column and it indexes the LOB data. The LOB index resides in the same tablespace as the locator.

Indexing: Why is a BLOB Column Removed on DELETing but not a BFILE Column?

Question

The promotion column could be defined and indexed as a BFILE, but if for example, a row is DELETEd, the Word document is removed with it when the promotion column is defined as BLOB, but it is not removed when the column is defined as a BFILE. Why?

Answer

We don't create an index for BFILE data. Also note that internal persistent LOBs are automatically backed up with the database whereas external BFILEs are not and modifications to the internal persistent LOB can be placed in the redo log for future recovery.

Which Views Can I Query to Find Out About a LOB Index?

Question

Which views can I query to find out about a LOB index?

Answer

• Internal Persistent LOBs:
  • ALL_INDEXES View: Contains all the indexes the current user has the ability to modify in any way. You will not see the LOB index in this view because LOB indexes cannot be renamed, rebuilt, or modified.

  • DBA_INDEXES View: Contains all the indexes that exist. Query this view to find information about the LOB index.

  • USER_INDEXES View: Contains all the indexes that the user owns. The LOB index will be in this view if the user querying it is the same user that created it.

• Temporary LOBs:

  For temporary LOBs, the LOB index information can be retrieved from the view, V$SORT_USAGE.

  For example:

  SELECT USER#, USERNAME, SEGTYPE, EXTENTS, BLOCKS 
         FROM v$sort_usage, v$session 
         WHERE SERIAL#=SESSION_NUM;