EXCHANGE_SHADOW_INDEX

This procedure swaps the index (or index partition) metadata and index (or index partition) data.

For nonpartitioned indexes, this procedure swaps both the metadata and the index data, and processes the online pending queue.

Syntax

CTX_DDL.EXCHANGE_SHADOW_INDEX(
     idx_name        IN VARCHAR2
     partition_name  IN VARCHAR2 default NULL
);

idx_name

Specify the name of the CONTEXT indextype.

partition_name

Specify the name of the shadow index partition. May also be NULL.

Example

Example 8-4 Global Index RECREATE with Scheduled Swap

This example demonstrates running CTX_DDL.EXCHANGE_SHADOW_INDEX during non-business hours when query failures and DML blocking can be tolerated.

/* 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');

/* recreate index online with the new multip-lexer */
exec ctx_ddl.create_shadow_index('idx',
  'replace lexer m_lexer language column lang');
exec ctx_ddl.exchange_shadow_index('idx');

Notes

• Using EXCHANGE_SHADOW_INDEX with Nonpartitioned Indexes:

  For nonpartitioned indexes, this procedure will swap both metadata and index data, and will process the online pending queue.

  Queries will return column not indexed errors when swapping metadata and index data, but queries are processed normally when processing online pending queue. The period of errors being raised should be short.

  If you specify POPULATE when you create the shadow index, and if many DML operations have been issued since the creation of the shadow index, then there could be a large pending queue. However, if you use incremental recreate, that is, specify NOPOPULATE when you create the shadow index, and you then populate the pending queue and sync, then the online pending queue is always empty no matter how many DML operations have occurred since CREATE_SHADOW_INDEX was issued.

  When this procedure is running, DML will first fail with an error about index being in in-progress status. After that DML could be blocked if there are rows in online pending queue that need to be reapplied.

  Note: When this procedure is running, DML statements will fail with an error that the index is in “in-progress status.” If, when this error occurs, there are rows in the online pending queue that need to be reapplied, then the DML could be blocked and stop responding.

• Using EXCHANGE_SHADOW_INDEX with Partitioned Indexes:

  For partitions that are recreated with NOSWAP: when the index is partitioned, and if partition_name is a valid index partition, then this procedure will swap the index partition data and the index partition metadata, and will process the online pending queue for this partition.

  This procedure swaps only one partition at a time. When you run this procedure on partitions that are recreated with NOSWAP:

  • Queries that span multiple partitions will not return consistent results across all partitions.

  • Queries on the partition that is being swapped will return errors.

  • Queries on partitions that are already swapped will be based on the new index.

  • Queries on the partitions that haven’t been swapped will be based on the old index.

  If the partition_name is NULL, then this procedure will swap the index metadata. Run this procedure as the last step when recreating a local partitioned index online.

• Sync Behavior:

  After running EXCHANGE_SHADOW_INDEX, you must call the SYNC_INDEX operation to synchronize any DML that occurs during the build of the shadow index. If you have specified SYNC(ON COMMIT) or SYNC(EVERY), then the sync occurs automatically. However, if you have specified SYNC(MANUAL), then you must manually invoke SYNC_INDEX.

Related Topics

CTX_DDL.”RECREATE_INDEX_ONLINE”

CTX_DDL.”CREATE_SHADOW_INDEX”

CTX_DDL.”DROP_SHADOW_INDEX”