CREATE_SHADOW_INDEX

Creates index metadata (or policy) for the specified index. If the index is not partitioned, then it also creates the index tables. This procedure is only supported in Enterprise Edition of Oracle AI Database.

The following changes are not supported:

Transition from non-composite domain index to composite, or changing the composite domain index columns.
Rebuild indexes that have partitioned index tables, for example, $I, $P, $K.

Note:

For a partitioned index, you must first call this procedure to create the shadow index metadata. This procedure will not create index tables. It has no effect on query, DML, sync, or optimize operations.
The CREATE_SHADOW_INDEX and RECREATE_INDEX_ONLINE procedures do not support section group with the XML_ENABLE attribute on CONTEXT indexes. Doing so results in the “DRG-10521: Operation not supported with XML_ENABLE on a CONTEXT Index” error.

Syntax

CTX_DDL.CREATE_SHADOW_INDEX(
  idx_name          IN VARCHAR2,
  parameter_string  IN VARCHAR2 DEFAULT NULL,
  parallel_degree   IN NUMBER DEFAULT 1
);

idx_name

The name of a valid CONTEXT indextype.

parameter_string

For nonpartitioned index, the same string as in ALTER INDEX. For partitioned index, the same string as in ALTER INDEX PARAMETER.

parallel_degree

Reserved for future use. Specify the degree of parallelism. Parallel operation is not currently supported.

Example

Example 8-1 Scheduled Global Index RECREATE (Incremental Rebuild)

In this example, you have the finest control over each stage of RECREATE_INDEX_ONLINE. Since SYNC_INDEX can take a time limit, you can limit SYNC_INDEX during non-business hours and incrementally recreate the index.

/* create lexer and original index */
exec ctx_ddl.create_preference('us_lexer','basic_lexer');
create index idx on tbl(text) indextype is ctxsys.context
  parameters('lexer us_lexer');

/* create a new lexer */
begin
  ctx_ddl.create_preference('e_lexer','basic_lexer');
  ctx_ddl.set_attribute('e_lexer','base_letter','yes');
  ctx_ddl.create_preference('m_lexer','multi_lexer');
  ctx_ddl.add_sub_lexer('m_lexer','default','us_lexer');
  ctx_ddl.add_sub_lexer('m_lexer','e','e_lexer');
end;
/

/* add new language column to the table for multi-lexer */
alter table tbl add(lang varchar2(10) default 'us');

/* create shadow index */
exec ctx_ddl.create_shadow_index('idx',
  'replace lexer m_lexer language column lang NOPOPULATE');

declare
  idxid integer;
begin
  /* figure out shadow index name */
  select idx_id into idxid from ctx_user_indexes
     where idx_name ='IDX';
  /* populate pending */
  ctx_ddl.populate_pending('RIO$'||idxid);
  /* time limited sync */
  ctx_ddl.sync_index(idx_name =>'RIO$'||idxid,
                     maxtime =>480);
  /* more sync until no pending rows for the shadow index */
end;
/* swap in the shadow index */
exec ctx_ddl.exchange_shadow_index('idx');

Notes

The index name for the shadow index is RIO$index_id. By default, it also populates index tables for nonpartitioned indexes, unless NOPOPULATE is specified in CREATE INDEX or in ALTER INDEX. For a local partitioned index, it only creates index metadata without creating the index tables for each partition. Each index can have only one shadow index.
When building a nonpartitioned index online, you can first call this procedure to create index metadata and index tables. If you specify POPULATE, then this procedure populates the index, but does not do swapping. You can schedule the swapping at a later, preferred time.

If you specify NOPOPULATE, it only creates metadata for the index tables, but does not populate them. You must perform POPULATE_PENDING (CTX_DDL.POPULATE_PENDING) to populate the pending queues after running this procedure, and then sync the indexes. This is referred to as incremental re-create.

Queries are all processed normally when this procedure is running.
If POPULATE is specified, then DML is blocked for a very short time at the beginning of populate, after which all further DML is logged into an online pending queue and processed later.
In case of NOPOPULATE shadow indexes, ensure that you execute the POPULATE_PENDING procedure before calling a DML operation. If you call a DML operation before executing the POPULATE_PENDING procedure, then the same tokens appear twice in the $I index table.
Sync with CTX_DDL.SYNC_INDEX runs normally on the index.OPTIMIZE_INDEX runs without doing anything, but does not return an error.
When you change the SYNC type for a shadow index, the EXCHANGE_SHADOW_INDEX procedure swaps the main index with the new SYNC type. However, the shadow index continues to use MANUAL synchronization as the SYNC type. This feature enables you to control when you want to populate or exchange the shadow index.

Related Topics