With cache segment partitioning, you can divide the database file cache into multiple segments. By default, no cache segments are defined. You create and modify the cache segments using SQL statements.

Each cache segment must have a unique name. The size of the segment is specified 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 with the IndexFile.CacheSize parameter.

You can assign the entire table or a part of a table to a cache segment. A part of a table is assigned to a cache segment by giving a column range. A single table can belong to only one cache segment, but a single cache segment can have multiple tables.

When an SQL statement accesses data that is associated to a cache segment, the entire SQL statement is assigned to that cache segment. All database caching for the SQL statement goes through that cache segment. If the SQL statement later accesses data from some other cache segment, it does not change the cache segment used for the statement.

From the application perspective, you do not need make any application level or SQL statement level changes to use cache segment partitioning. If cache pages are used for other purposes such as sorting or query buffering, the number of pages allocated to cache segments is adjusted accordingly. The system tries to keep the percentage of the cache allocated to a segment correct, even when the available size of the cache changes.

If there are no SQL statements using a cache segment, the cache pages can be used for the (unassigned) default segment, which ensures that cache pages are always fully utilized.

Using cache segment partitioning does not change the physical data representation or storage. The data association to a cache segment works only at a logical level based on column values. If the cache segments are changed (or removed), the new segments are taken into use after a server restart. If you do not restart the server, the cache segment usage is changed gradually.