Alter the ST_Geometry points' LOB segment from NOCACHE to CACHE

Summary

Instructions provided describe how to alter the ST_Geometry points' LOB segment from NOCACHE to CACHE. If a table containing an ST_Geometry 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 that the LOB segment that represents the ST_Geometry points' attribute is CACHED, therefore, reducing physical I/O operations.

When creating a feature class with ArcGIS, ensure the ArcSDE dbtune keyword being used correctly sets the CACHE parameter for the LOB segment. The following keyword parameter ST_GEOM_LOB_STORAGE controls the st_geometry LOB attribute storage.

Code:
ST_GEOM_LOB_STORAGE  " STORE AS (
#               TABLESPACE <tablespace_name>
                ENABLE STORAGE IN ROW CHUNK 8K RETENTION CACHE
#               INDEX (TABLESPACE <tablespace_name>)
) "

Procedure

1. Identify if the table's ST_Geometry attribute's LOB segment is set to NOCACHE.
   
   

   Code:
   SQL> SELECT segment_name, cache
      2  FROM user_lobs
      3  WHERE table_name = 'STREETS' and column_name = 'SHAPE';
   
   SEGMENT_NAME              CACHE
   ------------------------- -------
   SYS_LOB0000346903C00017$$ NO
   
   

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

   Code:
   SQL> ALTER TABLE streets
     2   MODIFY LOB (shape.points)(CACHE);
   
   Table altered.
   
   SQL> SELECT segment_name, cache
      2  FROM user_lobs
      3  WHERE table_name = 'STREETS' and column_name = 'SHAPE';
   
   SEGMENT_NAME              CACHE
   ------------------------- -------
   SYS_LOB0000346903C00017$$ YES
   
   

   
   
   The above example demonstrates changing the 'STREETS' table's LOB segment from NOCACHE to CACHE.
   
   

   Note:
   See Oracle's documentation for additional information. A link to Oracle's documentation is located in the Related Information section below.