Incrementally Creating a CONTEXT Index

The ALTER INDEX and CREATE INDEX statements support incrementally creating a CONTEXT index.

You can incrementally create Oracle Text indexes, which means that the index structure is immediately created but the data is not populated during the index creation or rebuild process. You populate the index later at a suitable time. This procedure is useful for creating indexes in large installations that cannot afford to have the indexing process running continuously. It provides finer control over the creation of indexes, allowing you to avoid building indexes in a single operation.

Incremental index creation involves the following steps:

  1. Create an empty index:

    If you specify the NOPOPULATE keyword at the time of index creation or rebuild, it only creates metadata for the index tables but does not populate them.

    • Global index:

      For a global index, use CREATE INDEX to support the NOPOPULATE keyword in the REPLACE parameter of the REBUILD clause.

    • Local index partition:

      For a local index partition, modify the ALTER INDEX ... REBUILD partition ... parameters (‘REPLACE ...’) parameter string to support the NOPOPULATE keyword.

      For a partition on a local index, CREATE INDEX ... LOCAL ... (partition ... parameters (‘NOPOPULATE’)) is supported. The partition-level POPULATE or NOPOPULATE keywords override any POPULATE or NOPOPULATE specified at the index level.

  2. Place all ROWIDs into the pending queue:

    Use the CTX_DDL.POPULATE_PENDING procedure to populate the pending queues with every ROWID in the base table or table partition.

  3. Populate the index:

    Use the CTX_DDL.SYNC_INDEX procedure to populate the index with the queued data.

    The SYNC_INDEX procedure includes the maxtime argument that indicates a suggested time limit in minutes for the operation. The indexing process runs in an estimate of the given maxtime instead of running to completion. You might need to run multiple SYNC_INDEX calls until the index is fully synced.

    You can choose to run both the POPULATE_PENDING and SYNC_INDEX calls separately so that the population of the pending queue and the population of the index happen at different times, thereby optimizing system performance.

Example 4-1 Incrementally Build an Empty Global Index

-- Create an empty index

CREATE INDEX ctx_ind ON ctx_tab(doc) INDEXTYPE IS CTXSYS.CONTEXT
   PARAMETERS ('NOPOPULATE');
declare
 n_pending number;
 function get_pending return number is
    n_pending number;

 begin
   execute immediate 'SELECT COUNT(*) FROM CTX_USER_PENDING WHERE PND_INDEX_NAME = :1'
       into n_pending using 'CTX_IND';
   return n_pending;
 end get_pending;

begin
 -- Fill in the pending queue
 CTX_DDL.POPULATE_PENDING('CTX_IND');
 n_pending := get_pending;
 while (n_pending > 0) loop
 -- Populate the index through sync_index
    CTX_DDL.SYNC_INDEX('CTX_IND', maxtime => 1);
    n_pending := get_pending;
 end loop;
end;
/

Related Topics