Use the CREATE CACHE SEGMENT statement to divide the database buffer pool (cache) into segments. The cache segment size is defined as a percentage of the total cache size. The total size for the assigned segments cannot be more than 80% of the total cache size. The total cache size is defined by using the IndexFile.CacheSize parameter, see IndexFile section.
After you have created cache segments with the CREATE CACHE SEGMENT statement, assign tables or data ranges to the segment with the ALTER TABLE .. ADD CACHE SEGMENT statement, see ALTER TABLE ... ADD or DROP CACHE SEGMENT.
To check the names and sizes of cache segments, query the system table SYS_CACHESEGMENTS, see SYS_CACHESEGMENTS.
Parameters, clauses, keywords, and variables
▪ name: Identifies the cache segment name. The name must be a valid SQL identifier.
▪ percentage: Defines the size of the cache segment as a percentage of the total cache size. The segment size must be 1 - 80. The total size of all cache segments cannot exceed 80.
Example
To create a cache segment that is called HISTORY_CACHE and uses 20% percentage of the database cache, use the following statement: