Working with cache segments

You can create, assign, modify, and remove cache segments and query cache segment sizes and assignments.

Use the CREATE CACHE SEGMENT statement to define the name and size of the segment, see CREATE CACHE SEGMENT.

For example, to create a cache segment that is called HISTORY_CACHE that uses 20% percentage of the database cache, execute the following statement:

CREATE CACHE SEGMENT HISTORY_CACHE POOLSIZE 20

Use the ALTER TABLE ... ADD CACHE SEGMENT statement to assign a table or a data range to a segment, see ALTER TABLE.

For example, to assign the table HISTORY_DATA and all of its contents to the cache segment HISTORY_CACHE, execute the following statement:

ALTER TABLE HISTORY_DATA ADD CACHE SEGMENT HISTORY_CACHE

In addition to assigning entire tables, the data range can be defined by using a WHERE clause.

For example, if the table, HISTORY_DATA, contains a column, DT, of the data type DATE, you can assign data that is older than seven days to the cache segment HISTORY_CACHE, by executing the following statement:

ALTER TABLE HISTORY_DATA ADD CACHE SEGMENT HISTORY_CACHE WHERE DT OLDERTHAN 7 DAYS

Use the ALTER TABLE ... SET POOLSIZE statement to set the new segment size.

Note Restart the server to make the change effective immediately. If you do not restart the server, the change is gradual; the segment size increases (or decreases) as data is added to (or removed from) the cache segment.

Use the ALTER TABLE ... DROP CACHE SEGMENT statement to remove a table assignment from the segment.

Use the DROP CACHE SEGMENT statement to delete the cache segment definition from the database.

Note Restart the server to make the change effective immediately. If you do not restart the server, the change is gradual.

Querying cache segment sizes and assignments

The cache segments and table assignments are stored in the system tables SYS_CACHESEGMENTS and SYS_CACHESEGMENT_CONDITIONS. Use the following type of statements to query the cache segment information.

Listing tables that use a given cache segment

To list the tables that use a given cache segment, you can execute the following statement:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_TABLES, SYS_CACHESEGMENT_CONDITIONS
where ID = BASE_TABLE_ID
and CACHESEGMENT_NAME = 'segment_name'

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_TABLES, SYS_CACHESEGMENT_CONDITIONS
where ID = BASE_TABLE_ID
and CACHESEGMENT_NAME = 'CS1'

TABLE_NAME CACHESEGMENT_NAME
---------- -----------------
TAB_TIMESTAMP CS1
TAB_INTEGER CS1
2 rows fetched.

Listing cache segments that are used by a given table

To list the cache segments that are used by a given table, you can execute the following statement:

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_CACHESEGMENT_CONDITIONS, SYS_TABLES
WHERE SYS_TABLES.ID = SYS_CACHESEGMENT_CONDITIONS.BASE_TABLE_ID
and SYS_TABLES.TABLE_NAME = 'table_name'

SELECT TABLE_NAME, CACHESEGMENT_NAME from SYS_CACHESEGMENT_CONDITIONS, SYS_TABLES
WHERE SYS_TABLES.ID = SYS_CACHESEGMENT_CONDITIONS.BASE_TABLE_ID
and SYS_TABLES.TABLE_NAME = 'TAB_TIMESTAMP'

TABLE_NAME CACHESEGMENT_NAME
---------- -----------------
TAB_TIMESTAMP CS1
1 rows fetched.