WARM_CACHE

The CTX_QUERY.WARM_CACHE PL/SQL procedure improves query performance by limiting disk reads from Oracle Text secondary tables.

The first execution of an Oracle Text query can be slower in comparison with repeat invocations. This is primarily due to the requirement to load relevant $I and $G TOKEN_INFO data into the database buffer cache. You can use this API to read Oracle Text secondary tables (such as $I, $KG, $G, $P, $S, $SN, $ST, $SV, $SD, $D, or $O) and load them into the Shared Global Area (SGA) buffer cache.

To run this operation, ensure that you specify a large buffer cache by using the buffer cache initialization parameters.

Syntax

CTX_QUERY.WARM_CACHE(
        owner_name            IN VARCHAR2,
        index_name            IN VARCHAR2,
        table_identifier      IN VARCHAR2 default 'I',
        part_name             IN VARCHAR2 default null
);

owner_name

The name of the owner of the index for which you want to improve the query performance.

index_name

The name of the index.

table_identifier

The name of the Oracle Text secondary table that you want to load.

part_name

If your index is a local index, then you must specify the name of the index partition to synchronize otherwise an error is returned. If your index is a global, nonpartitioned index, then specify NULL, which is the default.

Example

CTX_QUERY.WARM_CACHE('owner_1', 'IDX', 'I', 'p1');

Related Topics

• Oracle AI Database Administrator’s Guide