Alter an annotation class' element attribute's LOB segment from NOCACHE to CACHE

Summary

Instructions provided show how to alter an annotation class' element attribute's LOB segment from NOCACHE to CACHE. If an annotation class table containing an element attribute is created without specifying the storage options for the LOB segment, Oracle's default setting is NOCACHE, meaning LOB values are not brought into the buffer cache.

To improve performance it is recommended the LOB segment which represents the element attribute is CACHED, reducing physical I/O operations.

Procedure

1. Identify if the annotation class' table element attribute's LOB segment is set to NOCACHE.
   
   

   Code:
   SQL> SELECT table_name, cache
      2  FROM user_lobs
      3  WHERE column_name = 'ELEMENT'
      4  AND cache = 'NO';
   
   TABLE_NAME              CACHE
   ----------------------- -------
   PARCEL_ANNOTATION       NO
   

   
   
2. Alter the table and verify that the LOB segment is now cached.
   
   

   Code:
   SQL> ALTER TABLE parcel_annotation
     2   MODIFY LOB (element)(CACHE);
   
   Table altered.
   
   SQL> SELECT table_name, cache
      2  FROM user_lobs
      3  WHERE column_name = 'ELEMENT'
      4  AND table_name = 'PARCEL_ANNOTATION';
   
   TABLE_NAME              CACHE
   ----------------------- -------
   PARCEL_ANNOTATION       YES
   

   
   
   The above example demonstrates changing the annotation class parcel_annotation element attribute's LOB segment from NOCACHE to CACHE.
   
   

   Note:
   See Oracle's documentation for additional information.