ALTER INDEX

Use the ALTER INDEX statement to change or rebuild an existing index, such as Oracle Text index, Oracle Text search index, JSON search index, XML search index, or hybrid vector index.

Note: This section describes the ALTER INDEX statement as it pertains to managing an Oracle Text domain index. For a complete description of the ALTER INDEX statement, see Oracle Database SQL Language Reference.

ALTER INDEX Purpose

To make changes to or perform maintenance tasks for a CONTEXT, CTXCAT, or CTXRULE index.

Note:

• When you use ALTER INDEX to shift from FAST_DML to FAST_QUERY, you might encounter the DRG-11380 "operation is not allowed on unsynced index" error. To overcome this error, run the SYNC command on the index and then retry ALTER INDEX.

• When you run any DML or query workload during ALTER INDEX, you might encounter an ORA-00060 or other error that may mark the index UNUSABLE. This is because ALTER INDEX behaves like a DDL operation and is not performed online by default. To overcome this error, set the ONLINE parameter in the ALTER INDEX statement.

• The FAST_DML and FAST_QUERY options are not supported for online operations.

All Index Types

Use ALTER INDEX to perform the following tasks on all Oracle Text index types:

• Rename the index or index partition. See ALTER INDEX RENAME Syntax.

• Add stopwords to the index. See ALTER INDEX REBUILD Syntax.

• Add or remove a sub_lexer, and remove a stopword or set of stopwords for a given symbol (language or language-independent). See ALTER INDEX Sub_Lexer Syntax.

• Rebuild the index using different preferences. Some restrictions apply for the CTXCAT index type. See ALTER INDEX REBUILD Syntax.

Note:

Both CTXCAT and the use of CTXCAT grammar as an alternative grammar for CONTEXT queries is deprecated. Instead, Oracle recommends that you use the CONTEXT indextype, which can provide all the same functionality, except that it is not transactional. Near-transactional behavior in CONTEXT can be achieved by using SYNC(ON COMMIT) or, preferably, SYNC(EVERY [time-period]) with a short time period.

CTXCAT was introduced when indexes were typically a few megabytes in size. Modern, large indexes, can be difficult to manage with CTXCAT. The addition of index sets to CTXCAT can be achieved more effectively by the use of FILTER BY and ORDER BY columns, or SDATA, or both, in the CONTEXT indextype. CTXCAT is therefore rarely an appropriate choice. Oracle recommends that you choose the more efficient CONTEXT indextype.

CONTEXT and CTXRULE Index Types

Use ALTER INDEX to perform the following tasks on CONTEXT and CTXRULE index types:

• Resume a failed index operation (creation/optimization).

• Add sections and stop sections to the index.

• Replace index metadata.

  See Also: ALTER INDEX REBUILD Syntax to learn more about performing these tasks

Overview of ALTER INDEX Syntax

The syntax for ALTER INDEX is fairly complex. The major divisions are covered in the following sections:

• ALTER INDEX MODIFY PARTITION Syntax: Use this to modify an index partition’s metadata.

• ALTER INDEX PARAMETERS Syntax: Use this to modify the parameters of a nonpartitioned index, or to modify all partitions of a local partitioned index, without rebuilding the index.

• ALTER INDEX RENAME Syntax: Use this to rename an index or index partition.

• ALTER INDEX REBUILD Syntax: Use this to rebuild an index or index partition. With this statement, you can also replace index metadata; add stopwords, sections, and stop sections to an index; and resume a failed operation.

  The parameters for ALTER INDEX REBUILD have their own syntax, which is a subset of the syntax for ALTER INDEX. For example, the ALTER INDEX REBUILD PARAMETERS statement can take either REPLACE or RESUME as an argument, and ALTER INDEX REBUILD PARAMETERS ('REPLACE') can take several arguments. Valid examples of ALTER INDEX REBUILD include the following statements:

ALTER INDEX REBUILD PARALLEL n
ALTER INDEX REBUILD PARAMETERS ('REPLACE DATASTORE datastore_pref')
ALTER INDEX REBUILD PARAMETERS ('REPLACE WORDLIST wordlist_pref')

• ALTER INDEX Syntax for JSON Search Index: Use this to modify the JSON search index preferences, such as DATAGUIDE and SEARCH_ON.

• ALTER INDEX Syntax for XML Search Index: Use this to modify the XML search index preferences, such as SEARCH_ON.

• ALTER INDEX Syntax for Hybrid Vector Index: Use this to modify or rebuild an existing hybrid vector index.

ALTER INDEX MODIFY PARTITION Syntax

Use the following syntax to modify the metadata of an index partition:

ALTER INDEX index_name MODIFY PARTITION partition_name PARAMETER (paramstring)

index_name

Specify the name of the index whose partition metadata you want to modify.

partition_name

Specify the name of the index partition whose metadata you want to modify.

paramstring

The only valid argument here is ‘REPLACE METADATA’. This follows the same syntax as ALTER INDEX REBUILD PARTITION PARAMETERS ('REPLACE METADATA'); see the REPLACE METADATA subsection of the ALTER INDEX REBUILD Syntax section for more information. (The two statements are equivalent. ALTER INDEX MODIFY PARTITION is offered for ease of use, and is the recommended syntax.)

ALTER INDEX PARAMETERS Syntax

The parameter string now supports READ ONLY MDATA. Use the following syntax to modify the parameters either of nonpartitioned or local partitioned indexes, without rebuilding the index. For partitioned indexes, this statement works at the index level, not at the partition level. This statement changes information for the entire index, including all partitions.

ALTER INDEX index_name PARAMETERS (paramstring)

paramstring

ALTER INDEX PARAMETERS accepts the following arguments for paramstring:

• ‘REPLACE METADATA’

  Replaces current metadata. See the REPLACE METADATA subsection of the ALTER INDEX REBUILD Syntax section for more information.

• ‘ADD MDATA SECTION secname TAG sectag READ ONLY’

  Creates non-updatable MDATA sections so that queries on these MDATA sections do not require extra cursors to be opened on $I table.

• ‘ADD STOPWORD’

  Dynamically adds a stopword to an index. See the ADD STOPWORD subsection of the “ALTER INDEX REBUILD Syntax” section for more information.

• ‘ADD FIELD SECTION’

  Dynamically adds a field section to an index. See the ADD FIELD subsection of the “ALTER INDEX REBUILD Syntax” section for more information. You can add an unlimited number of field sections.

• ‘ADD ZONE SECTION’

  Dynamically adds a zone section to an index. See the ADD ZONE subsection of the “ALTER INDEX REBUILD Syntax” section for more information.

• ‘ADD ATTR SECTION’

  Dynamically adds an attribute section to an index. See the ADD ATTR subsection of the ALTER INDEX REBUILD Syntax section for more information.

• ‘ADD SDATA SECTION’

  Dynamically adds an SDATA section to an index. An SDATA section can only be added to BASIC, HTML, XML, and NEWS section groups. It supports both global as well as local indexes. New documents synchronized into the index reflect this new preference. The syntax is:

index_name

The datatype can be VARCHAR2, CHAR, NUMBER, DATE, or RAW.

See Adding an SDATA Section for more information.

Note: Documents that were indexed before adding an SDATA section do not reflect this new preference. Rebuild the index in this case.

Each of the above described parameters has an equivalent ALTER INDEX REBUILD PARAMETERS version, except ADD SDATA SECTION.

For example, ALTER INDEX PARAMETERS ('REPLACE METADATA') is equivalent to ALTER INDEX REBUILD PARAMETERS ('REPLACE METADATA'). However, the ALTER INDEX PARAMETERS versions work on either partitioned or nonpartitioned indexes, whereas the ALTER INDEX REBUILD PARAMETERS versions work only on nonpartitioned indexes.

ALTER INDEX RENAME Syntax

Use the following syntax to rename an index or index partition:

ALTER INDEX [schema.]index_name RENAME TO new_index_name;

ALTER INDEX [schema.]index_name RENAME PARTITION part_name TO new_part_name;

[schema.]index_name

Specify the name of the index to rename.

new_index_name

Specify the new name for schema.index. The new_index_name parameter can be no more than 25 bytes, and 21 bytes for a partitioned index in earlier releases of Oracle Database that have not been upgraded to Oracle Database 12c Release 2 ( 12.2). If you specify a name longer than 25 bytes (or longer than 21 bytes for a partitioned index), then Oracle Text returns an error and the renamed index is no longer valid.

Note: When new_index_name is more than 25 bytes (21 for local partitioned index) and less than 30 bytes, Oracle Text renames the index, even though the system returns an error. To drop the index and associated tables, you must drop new_index_name with the DROP INDEX statement and then re-create and drop index_name.

The upgraded databases that do not have the compatible parameter set to 12.2 can have the new_index_name parameter no more than 30 bytes, and 30 bytes for a partitioned index.

The upgraded databases that have the compatible parameter set to 12.2 or new Oracle Database 12c Release 2 (12.2) installations can have the new_index_name parameter no more than 128 bytes, and 128 bytes for a partitioned index.

part_name

Specify the name of the index partition to rename.

new_part_name

Specify the new name for partition.

ALTER INDEX REBUILD Syntax

Use ALTER INDEX REBUILD to rebuild an index, rebuild an index partition, resume a failed operation, replace index metadata, add stopwords to an index, or add sections and stop sections to an index.

The ALTER INDEX REBUILD syntax has its own subsyntax. That is, its parameters have their own syntax. For example, the ALTER INDEX REBUILD PARAMETERS statement can take either REPLACE or RESUME as an argument, and ALTER INDEX REBUILD PARAMETERS ('REPLACE') has several arguments it can take.

Note: You cannot use the ALTER INDEX REBUILD syntax to add or remove the INMEMORY option associated Text index tables.

Valid examples of ALTER INDEX REBUILD include the following statements:

ALTER INDEX REBUILD PARALLEL n
ALTER INDEX REBUILD PARAMETERS (REPLACE DATASTORE datastore_pref)
ALTER INDEX REBUILD PARAMETERS (REPLACE WORDLIST wordlist_pref)

This is the syntax for ALTER INDEX REBUILD:

ALTER INDEX [schema.]index [REBUILD] [PARTITION partname] [ONLINE]
  [PARAMETERS(paramstring)][PARALLEL N];

PARTITION partname

Rebuilds the index partition partname. Only one index partition can be built at a time.

When you rebuild a partition you can specify only RESUME or REPLACE in paramstring. These operations work only on the partname you specify.

With the REPLACE operation, you can specify MEMORY, STORAGE, and SYNC for each index partition.

Adding Partitions To add a partition to the base table, use the ALTER TABLE SQL statement. When you add a partition to an indexed table, Oracle Text automatically creates the metadata for the new index partition. The new index partition has the same name as the new table partition. If you must change the index partition name, then use ALTER INDEX RENAME.

Splitting or Merging Partitions Splitting or merging a table partition with ALTER TABLE renders the index partitions invalid. You must rebuild them with ALTER INDEX REBUILD.

ONLINE

Enables you to continue to perform updates, insertions, and deletions on a base table. It does not enable you to query the base table. The ONLINE keyword can only be used with the Enterprise Edition of Oracle Database.

Note: You can specify REPLACE or RESUME when rebuilding an index or an index partition ONLINE.

PARAMETERS (paramstring)

Optionally, specify paramstring. If you do not specify paramstring, then Oracle Text rebuilds the index with existing preference settings.

Note: Oracle Text rebuilds the index using metadata values that have been deep-copied into the index. You can use the CTX_REPORT.CREATE_INDEX_SCRIPT procedure to recreate the user preferences. This procedure generates a script with the preferences that are identical to those used in the original Text index. However, the names of the preferences will be system-generated.

The syntax for paramstring is as follows:

paramstring =
'REPLACE
     [DATASTORE datastore_pref]
     [FILTER filter_pref]
     [LEXER lexer_pref]
     [WORDLIST wordlist_pref]
     [STORAGE storage_pref]
     [STOPLIST stoplist]
     [SECTION GROUP section_group]
     [MEMORY memsize
     [[POPULATE | NOPOPULATE]
     [INDEX SET index_set]

		[METADATA preference new_preference]
     [METADATA FORMAT COLUMN format_column_name]
     [[METADATA] MAINTENANCE AUTO | MAINTENANCE MANUAL]
     [[METADATA]SYNC (MANUAL | EVERY "interval-string"[MEMORY memsize] [PARALLEL n] | ON COMMIT)]
     [[METADATA] TRANSACTIONAL | NONTRANSACTIONAL
     [[METADATA] [ASYNCHRONOUS_UPDATE | SYNCHRONOUS_UPDATE]]

     [[METADATA] OPTIMIZE (MANUAL | AUTO_DAILY [PARALLEL n] | EVERY "interval-string"[PARALLEL n] )]

|[DATAGUIDE [ON | OFF | ON CHANGE [ADD_VC|Function_name]]
|[SEARCH_ON (NONE | TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING)]
| RESUME [memory memsize]
| ADD STOPWORD word [language language]
| ADD ZONE SECTION section_name tag tag
| ADD FIELD SECTION section_name tag tag [(VISIBLE | INVISIBLE)]
| ADD ATTR SECTION section_name tag tag@attr
| ADD STOP SECTION tag'

REPLACE [optional_preference_list]

Rebuilds an index. You can optionally specify your own preferences or system-defined preferences.

You can replace only the preferences that are supported for that index type. For instance, you cannot replace index set for a CONTEXT or CTXRULE index. Similarly, for the CTXCAT index type, you can replace lexer, wordlist, storage index set, and memory preferences.

The POPULATE parameter is the default and need not be specified. If you want to empty the index of its contents, then specify NOPOPULATE. Clear an index of its contents when you must rebuild your index incrementally. The NOPOPULATE choice is available for a specific partition of the index, and not just for the entire index.

Note that ALTER INDEX REBUILD creates a populated index by default, unless you explicitly specify the NOPOPULATE keyword. The outputs of CTX_REPORT.CREATE_INDEX_SCRIPT and CTX_REPORT.DESCRIBE_INDEX include the NOPOPULATE keyword for such indexes.

If you are rebuilding a partitioned index using the REPLACE parameter, then you can specify only STORAGE, MEMORY, and NOPOPULATE.

A new wordlist preference SEPARATE_OFFSETS specifies that the token_info in the index is stored as docids only in one place, and offsets is stored only in another place. Refer to Oracle Text Application Developer’s Guide for information on improved response time using the SEPARATE_OFFSETS option of CONTEXT index.

If this procedure modifies the existing index tables for only the following storage attributes of the BASIC_STORAGE type (any one of them), then it will not result in re-indexing of data:

• BIG_IO

• I_INDEX_CLAUSE

• I_TABLE_CLAUSE

• SEPARATE_OFFSETS

Note:

• The BIG_IO attribute of the CONTEXT indextype is deprecated with Oracle AI Database 26ai, and can be disabled or removed in a future release.

• Oracle recommends that you allow this value to be set to its default value of N. BIG_IO was introduced to reduce the cost of seeks when index postings exceeded 4KB in length. However, the internal code is relatively inefficient, and the attribute cannot be combined with newer index options. Seek cost is much less relevant for solid state disks or non-volatile memory devices (NVMe), and seek cost is irrelevant when postings are cached. This setting is therefore of little benefit for most indexes.

REPLACE METADATA preference new_preference

Replaces the existing preference class settings, including SYNC parameters, of the index with the settings from new_preference. Only index preferences and attributes are replaced. The index is not rebuilt.

This statement is useful when you want to replace a preference and its attribute settings after the index is built, without re-indexing all data. re-indexing data can require significant time and computing resources.

This statement is also useful for changing the SYNC parameter type, which can be automatic, manual, or on-commit.

The ALTER INDEX REBUILD PARAMETER ('REPLACE METADATA') statement does not work for a local partitioned index at the global level for the index. You cannot, for example, use this syntax to change a global preference, such as filter or lexer type, without rebuilding the index. Use ALTER INDEX PARAMETERS instead to change the metadata of an index at the global level, including all partitions. See ALTER INDEX PARAMETERS Syntax.

Note: The ALTER INDEX REPLACE METADATA option is essentially a DDL operation (and not an ONLINE operation), so it may fail if there are any concurrent DML operations requesting locks on the underlying table, including queries. You must perform ALTER INDEX REPLACE METADATA operations during a quiet time on the system when other user operations are not ongoing on the table or index.

When should I use the METADATA keyword? REPLACE METADATA should be used only when the change in index metadata will not lead to an inconsistent index, which can lead to incorrect query results.

For example, use this statement in the following instances:

• To go from a single-language lexer to a multilexer in anticipation of multilingual data. For an example, see Replacing Index Metadata: Changing Single-Lexer to Multilexer.

• To change the WILDCARD_MAXTERMS setting inBASIC_WORDLIST.

• To change the SYNC parameter type, which can be automatic, manual, or on-commit.

These changes are safe and will not lead to an inconsistent index that might adversely affect your query results.

WARNING: The REPLACE METADATA statement can result in inconsistent index data, which can lead to incorrect query results. As such, Oracle does not recommend using this statement, unless you carefully consider the effect it will have on the consistency of your index data and subsequent queries.

There can be many instances when changing metadata can result in inconsistent index data. For example, Oracle recommends against using the METADATA keyword after performing the following procedures:

• Changing the USER_DATASTORE procedure to a new PL/SQL stored procedure that has different output.

• Changing theBASIC_WORDLIST attribute PREFIX_INDEX from NO to YES because no prefixes have been generated for existing documents. Changing it from YES to NO is safe.

• Adding or changing BASIC_LEXER printjoin and skipjoin characters, because new queries with these characters would be lexed differently from how these characters were lexed at index time.

• Do not use REPLACE METADATA with FORWARD_INDEX. Instead use REPLACE STORAGE.

In these unsafe cases, Oracle recommends rebuilding the index.

REPLACE [METADATA] MAINTENANCE AUTO | MAINTENANCE MANUAL

Specifies the maintenance type for synchronization of the CONTEXT and search indexes when there are inserts, updates, or deletes to the base table. The maintenance type specified for an index applies to all index partitions.

You can specify one of the following maintenance types:

Table 1 Maintenance Types

Maintenance Type	Description
MAINTENANCE AUTO	This is the default method for synchronizing Oracle Text CONTEXT and search indexes. This method sets your index to automatic maintenance, that is, the index is automatically synchronized in the background at optimal intervals. You do not need to manually configure a SYNC type or set any synchronization interval. The background mechanism automatically determines the synchronization interval and schedules background SYNC.INDEX operations by tracking the DML queue. Note: Shadow indexes do not support automatic maintenance. For a complete list of requirements and restrictions to follow in an automatic maintenance mode, see Oracle Text Application Developer's Guide.
MAINTENANCE MANUAL	This method sets your index to manual maintenance. This is a non-automatic maintenance (synchronization) mode in which you can specify SYNC types, such as MANUAL, EVERY, or ON COMMIT.

For guidelines and examples on switching between the MAINTENANCE AUTO and MAINTENANCE MANUAL methods, see Oracle Text Application Developer’s Guide.

REPLACE [METADATA] SYNC (MANUAL | EVERY “interval-string“[MEMORY memsize] [PARALLEL n] | ON COMMIT)]

Specifies the SYNC type for synchronization of the CONTEXT and search indexes when there are inserts, updates, or deletes to the base table.

These SYNC settings are applicable only to the indexes that are set to manual maintenance.

Note: By default, the CONTEXT and search indexes run in an automatic maintenance mode (MAINTENANCE AUTO), which means that your DMLs are automatically synchronized into the index in the background at optimal intervals. Therefore, you do not need to manually configure a SYNC method. However, if required, you can do so if you want to modify the default settings for an index.

You can specify one of the following SYNC methods:

Table 2 ALTER INDEX SYNC Methods

SYNC Type	Description
MANUAL	This is the default synchronization method for CONTEXT index. In this method, automatic synchronization is not provided. You must manually synchronize the index using CTX_DDL.SYNC_INDEX. Use MANUAL to disable ON COMMIT and EVERY synchronization.
EVERY interval-string	The default synchronization interval is set to 30 seconds. Automatically synchronize the index at a regular interval specified by the value of interval-string, which takes the same syntax as that for scheduler jobs. Automatic synchronization using EVERY requires that the index creator have CREATE JOB privileges. Ensure that interval-string is set to a considerable time period so that any previous synchronization jobs will have completed. Otherwise, the synchronization job may stop responding. The interval-string argument must be enclosed in double quotation marks ('' ''). See Enabling Automatic Index Synchronization at Regular Intervals for an example of automatic synchronization syntax.
ON COMMIT	Synchronize the index immediately after a commit. The commit does not return until the sync is complete. Before Oracle Database Release 18c, the synchronization was performed as a separate transaction. There was a time period, usually small, when the data was committed but index changes were not. Starting with Oracle Database Release 18c, the synchronization is performed as part of the same transaction. The operation uses the memory specified with the memory parameter. Before Oracle Database Release 18c, the sync operation had its own transaction context. If the operation failed, the data transaction still committed. Starting with Oracle Database Release 18c, if there is an irrecoverable index synchronization error, the entire data transaction is rolled back. Recoverable (individual row) synchronization errors are logged in the CTX_USER_INDEX_ERRORS view but the transaction still completes. See Viewing Index Errors under CREATE INDEX. ON COMMIT sync works best when the STAGE_ITAB index option is enabled, otherwise it causes significant fragmentation of the main index, requiring frequent OPTIMIZE calls. ON COMMIT sync is the default synchronization method for SEARCH INDEX and JSON search index. See Enabling Automatic Index Synchronization at Regular Intervals for an example of ON COMMIT syntax. See Oracle Text Application Developer's Guide for more information about the STAGE_ITAB option of the CONTEXT index.

Each partition of a locally partitioned index can have its own type of sync: (ON COMMIT, EVERY, or MANUAL). The type of sync specified in primary parameter strings applies to all index partitions unless a partition specifies its own type.

With automatic (EVERY) synchronization, you can specify memory size and parallel synchronization. The syntax is:

... EVERY interval_string MEMORY mem_size PARALLEL paradegree ...

ON COMMIT synchronizations can only be executed serially and at the same memory size as what was specified at index creation.

Note: This command rebuilds the index. When you want to change the SYNC setting without rebuilding the index, use the REBUILD REPLACE METADATA SYNC (MANUAL | ON COMMIT) operation.

REPLACE [METADATA] TRANSACTIONAL | NONTRANSACTIONAL

This parameter enables you to turn the TRANSACTIONAL property on or off. For more information, see “TRANSACTIONAL”.

Using this parameter only succeeds if there are no rows in the DML pending queue. Therefore, you may need to sync the index before issuing this command.

To turn on the TRANSACTIONAL index property:

ALTER INDEX myidx REBUILD PARAMETERS('replace metadata transactional');

or

ALTER INDEX myidx REBUILD PARAMETERS('replace  transactional');

To turn off the TRANSACTIONAL index property:

ALTER INDEX myidx REBUILD PARAMETERS('replace metadata nontransactional');

or

ALTER INDEX myidx REBUILD PARAMETERS('replace  nontransactional');

REPLACE [METADATA] [ASYNCHRONOUS_UPDATE | SYNCHRONOUS_UPDATE]

When you update the column in a document on which an Oracle Text index is based, that document is marked as invalid for search operations until index synchronization is performed. Enabling asynchronous update for an index enables a document to be searchable even though its index has not yet been synchronized after the index column was updated. Until the index is synchronized, Oracle Text uses the contents of the old document to answer user queries.

Note: Synchronous update is not supported with the TRANSACTIONAL option and for updates that cause row movement.

To enable asynchronous update for a Text index:

ALTER INDEX idx PARAMETERS ('REPLACE METADATA asynchronous_update');

To disable asynchronous update for a Text index:

ALTER INDEX idx PARAMETERS ('REPLACE METADATA synchronous_update');

Note:

The ASYNCHRONOUS_UPDATE setting of the CONTEXT indextype is deprecated in Oracle AI Database 26ai, and can be ignored or removed in a future release.

Oracle can ignore or remove this attribute in a future release. Oracle recommends that you allow this value to be set to its default value, SYNCHRONOUS_UPDATE. To avoid unexpected loss of results during updates, use SYNC (ON COMMIT) or SYNC(EVERY [time-period]) with a short time period.

The ASYNCHRONOUS_UPDATE setting was introduced as a workaround for the fact that updates are implemented as “delete followed by insert,” and that deletes are immediate (on commit), while inserts are only performed during an index sync. However, this setting is incompatible with several other index options. Oracle recommends that you discontinue its use.

REPLACE [[METADATA] OPTIMIZE (MANUAL | AUTO_DAILY [PARALLEL n] | EVERY “interval-string” [PARALLEL n])]

Specify OPTIMIZE to enable automatic background index optimization. You can specify any one of the following OPTIMIZE methods:

Table 3 ALTER INDEX OPTIMIZE Types

OPTIMIZE Type	Description
MANUAL	Provides no automatic optimization. You must manually optimize the index with CTX_DDL.OPTIMIZE_INDEX.
AUTO_DAILY	When you specify OPTIMIZE (AUTO_DAILY) in the create index parameter list, a repeatedly running optimize token job and a repeatedly running optimize full job are scheduled for each index and partition: The Optimize token job is scheduled to run weekly from 12 A.M. every Saturday night to optimize $S* tables. This job runs on tables with non-JSON data type (VARCHAR2, CLOB, or BLOB) to optimize the top 10 most fragmented tokens (determined automatically). The Optimize full job is scheduled to run every midnight from 12 A.M. to 3 A.M. except on Saturday night. Jobs that are not started before 3 A.M. are skipped. These skipped jobs are started before the other jobs that are scheduled to run at 12 A.M. the next day. This job runs on tables with JSON data type or the IS JSON check constraint. Existing indexes do not have OPTIMIZE (AUTO_DAILY) by default. You must use ALTER INDEX to enable automatic background index optimization.
EVERY “interval-string”	Automatically runs at a regular interval specified by the value interval-string, which takes the same syntax as scheduler jobs. The Optimize token job is scheduled for tables with non-JSON data type. This job runs optimize token for the top 10 most fragmented tokens at an interval specified by the user. The Optimize full job is scheduled for tables with JSON data type or the IS JSON check constraint. This job runs optimize full weekly at 12 A.M. every Saturday night for $S* tables. Ensure that interval-string is set to a considerable time period so that any previous optimize jobs are complete. The interval-string value must be enclosed in double quotes, and any single quote within interval-string must be preceded by the escape character with another single quote. If multiple indexes use the OPTIMIZE EVERY "interval-string" option, then different jobs are created for each index. These jobs are run concurrently.

With AUTO_DAILY | EVERY "interval-string" setting, you can specify parallel optimization. That syntax is:

... [AUTO_DAILY | EVERY "interval-string"] PARALLEL paradegree ...

RESUME [MEMORY memsize]

Resumes a failed index operation. You can optionally specify the amount of memory to use with memsize.

Note: This ALTER INDEX operation applies only to CONTEXT and CTXRULE indexes. It does not apply to CTXCAT indexes.

ADD STOPWORD word[language language]

Dynamically adds a stopword word to the index.

Index entries for word that existed before this operation are not deleted. However, subsequent queries on word are treated as though it has always been a stopword.

When your stoplist is a multilanguage stoplist, you must specify language.

The index is not rebuilt by this statement.

ADD ZONE SECTION section_name tagtag

Dynamically adds the zone section section_name identified by tag to the existing index.

The added section section_name applies only to documents indexed after this operation. For the change to take effect, you must manually re-index any existing documents that contain the tag.

The index is not rebuilt by this statement.

Note: This ALTER INDEX operation applies only to CONTEXT and CTXRULE indexes. It does not apply to CTXCAT indexes.

See Also: Notes

ADD FIELD SECTION section_name tag tag[(VISIBLE | INVISIBLE)]

Dynamically adds the field section section_name identified by tag to the existing index. There is no limit to the number of field sections that can be added.

Optionally specify VISIBLE to make the field sections visible. The default is INVISIBLE.

See Also: CTX_DDL.ADD_FIELD_SECTION for more information on visible and invisible field sections

The added section section_name applies only to documents indexed after this operation. For the change to affect previously indexed documents, you must explicitly re-index the documents that contain the tag.

This statement does not rebuild the index.

Note: This ALTER INDEX operation applies only to CONTEXT CTXRULE indexes. It does not apply to CTXCAT indexes.

See Also: Notes

ADD ATTR SECTION section_name tag tag@attr

Dynamically adds an attribute section section_name to the existing index. You must specify the XML tag and attribute in the form tag@attr. You can add attribute sections only to XML section groups.

The added attribute section section_name applies only to documents indexed after this operation. For the change to take effect, you must manually re-index any existing documents that contain the tag.

The index is not rebuilt by this statement.

Note: This ALTER INDEX operation applies only to CONTEXT CTXRULE indexes. It does not apply to CTXCAT indexes.

See Also: Notes

ADD STOP SECTION tag

Dynamically adds the stop section identified by tag to the existing index. As stop sections apply only to automatic sectioning of XML documents, the index must use the AUTO_SECTION_GROUP section group. The tag you specify must be case sensitive and unique within the automatic section group or else ALTER INDEX raises an error.

The added stop section tag applies only to documents indexed after this operation. For the change to affect previously indexed documents, you must explicitly re-index the documents that contain the tag.

The text within a stop section can always be searched.

The number of stop sections you can add is unlimited.

The index is not rebuilt by this statement.

See Also: Notes

This ALTER INDEX operation applies only to CONTEXT indexes. It does not apply to CTXCAT indexes.

PARALLEL n

Using n, you can optionally specify the parallel degree for parallel indexing. This parameter is supported only when you use SYNC, REPLACE, and RESUME in paramstring. The actual degree of parallelism might be smaller depending on your resources.

Parallel indexing can speed up indexing when you have large amounts of data to index and when your operating system supports multiple CPUs.

ALTER INDEX Syntax for JSON Search Index

ALTER INDEX [schema.]index REBUILD
PARAMETERS(
 [DATAGUIDE ON [CHANGE (ADD_VC | function_name)] | OFF]
 [SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING)
 [ADD SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]
 [REMOVE SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]
 [REPLACE SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]
 [ADD PATHLIST pathlist_identifier]
 [REMOVE PATHLIST pathlist_identifier]
 [REPLACE PATHLIST pathlist_identifier]
);

Note:

• The REPLACE keyword is not required with the ALTER INDEX REBUILD PARAMETERS statement when changing the DATAGUIDE or SEARCH_ON JSON search index parameters.

• You cannot change both the JSON and Oracle Text search index preferences in a single ALTER INDEX statement.

• If you specify the JSON search index preferences (such as DATAGUIDE and SEARCH_ON), other preferences in the PARAMETERS clause are not updated. Similarly, if you specify the Oracle Text search index preferences (such as STORAGE and LEXER), the JSON preferences are not updated.

[schema.]index

Specifies the name of JSON search index that you want to modify.

DATAGUIDE ON | OFF

Modifies data guide support for an existing JSON search index. By default, a JSON search index is created without data guide support. If you enable the JSON data guide support, then you can also define change-trigger procedures.

Note: You use the DATAGUIDE clause only for JSON search indexes.

Specify one of the following options:

• ON: Enables data guide support. If you set the value of DATAGUIDE to ON, then you can also define your own PL/SQL procedure or use the predefined change-trigger procedure ADD_VC.

  ADD_VC indicates if virtual columns are created based on the data guide.

  function_name specifies the function to be executed when the data guide changes.

• OFF: Disables both the data guide support and change-trigger procedures. Provides only general search-index functionality.

  Note: You cannot turn off the DATAGUIDE clause if the SEARCH_ON clause value is set to NONE.

See Change Triggers For Data Guide-Enabled Search Index in Oracle AI Database JSON Developer’s Guide.

SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING)

Modifies search preferences specified for an existing JSON search index.

Note: You can use the SEARCH_ON clause only for JSON and XML search indexes.

You can specify one of the following SEARCH_ON options:

Table 4 ALTER_INDEX SEARCH_ON Options

Option	Description
TEXT	Enables full-text search component, which indicates that only textual data is indexed for full-text search queries. This also includes queries that rely on path information. The index is used for JSON_TEXTCONTAINS predicates and for JSON_VALUE or JSON_EXISTS predicates that manipulate strings when using JSON search index. If your queries involve only full-text search and not string-range search or numeric search, then you can save some index maintenance time and disk space by specifying this option. Example: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON TEXT);
VALUE[(data_types)]	Enables range-search component for the specified data types. This allows the index to be picked up for predicates using relational operators (>, <, ==, >=, <=, !=). A JSON search index that is created with only SEARCH_ON VALUE cannot answer full-text queries by using the JSON_TEXTCONTAINS operator. Supported data types: NUMBER for indexing numeric values. TIMESTAMP for indexing date-time values. VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed. If you do not specify any data type, then the index enables range-search indexing on all supported data types. Note: The BINARY_DOUBLE data type is allowed only for XML search indexes. Examples: This example specifies the default behavior: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON VALUE'); These examples explicitly specify data types using the VALUE(data_types) syntax: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON VALUE(NUMBER)'); ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON VALUE(NUMBER, TIMESTAMP, VARCHAR2)');
TEXT_VALUE[(data_types)]	Enables both the full-text and range-search components for the specified data types. Supported data types: NUMBER for indexing numeric values. TIMESTAMP for indexing date-time values. VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed. If you do not specify any data type, then the index enables full-text search and range-search indexing on the NUMBER and TIMESTAMP data types. Examples: This example specifies the default behavior: ALTER INDEX [schema.]index REBUILD PARAMETERS('SEARCH_ON TEXT_VALUE'); These examples explicitly specify data types using the TEXT_VALUE(data_types) syntax: ALTER INDEX [schema.]index REBUILD PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER)'); ALTER INDEX [schema.]index REBUILD PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER, TIMESTAMP)');
TEXT_VALUE_STRING	Indicates that text and range-based indexes are created for numeric, date-time, and complete string values. This enables both the full-text and range-search components on the NUMBER, TIMESTAMP, and VARCHAR2 data types. String values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed. Example: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON TEXT_VALUE_STRING');

Guidelines for specifying SEARCH_ON transitions:

When you specify the SEARCH_ON clause in the ALTER INDEX REBUILD statement, the system determines both your current configuration and the set of components that you want to enable. The statement then enables any new components and rebuilds the index. If all requested components have already been enabled, this action is the same as an index rebuild. Rebuilding allows the JSON search index to be regenerated with newly enabled indexing and query components.

Note that range-search components of different data types are considered as independent components.

You can disable only the VARCHAR2 range-search component. To disable other components, you must first drop the index using the DROP INDEX statement and then re-create the index with the required components enabled.

Path Subsetting

You can add path subsetting specifications with SEARCH_ON to identify the fields in a document that you want to include or exclude from indexing. The excluded fields are not indexed, and the JSON search index is not used for them when querying. Filtering out irrelevant paths from documents can reduce the amount of data indexed, thereby minimizing disk space and the index creation or rebuild time.

Syntax for SEARCH_ON with path subsetting specifications:

[ADD SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]
 [REMOVE SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]
 [REPLACE SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]]

As an alternative to specifying the INCLUDE or EXCLUDE clause , you can use the PATHLIST parameter to specify a list of the paths to be included or excluded. You use PL/SQL subprograms CTX_DDL.CREATE_PATH_LIST and CTX_DDL.ADD_PATH to specify the list of the paths. See CREATE_PATH_LIST.

Syntax for adding PATHLIST

[ADD PATHLIST pathlist_identifier]
[REMOVE PATHLIST pathlist_identifier]
[REPLACE PATHLIST pathlist_identifier]

Note the following:

• You cannot specify both the INCLUDE and EXCLUDE clauses for a single index.

• You can specify a path subsetting with SEARCH_ON TEXT, TEXT_VALUE, TEXT_VALUE_STRING, and VALUE (not with NONE).

• Specifying any SEARCH_ON clause while there is also a PATHLIST parameter results in an error. Similarly, you cannot specify a PATHLIST parameter for an index that has the Dataguide feature enabled.

Table 5 SEARCH_ON Options With Path Subsetting

Option	Path Subsetting Example
TEXT	This example modifies a JSON search index with path subsetting to only index path $.LineItems.Part.Description for full-text and string-equality searches. ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD SEARCH_ON TEXT INCLUDE ($.LineItems.Part.Description)'); This example modifies a JSON search index with path subsetting to exclude paths for indexing for full-text and string-equality searches. It excludes the field located at paths $."Special Instructions" and $.ShippingInstructions.Address.street. ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD SEARCH_ON TEXT EXCLUDE ($."Special Instructions", $.ShippingInstructions.Address.street)');
VALUE[(data_types)]	This example modifies a JSON search index with path subsetting for range searches. It indexes fields $.PONumber and $.LineItems.Part.UnitPrice for numeric-value ranges, and fields $.Reference, $.User, $.ShippingInstructions.Address.state, and $.ShippingInstructions.Address.zipCode for string-value ranges. ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD SEARCH_ON VALUE(NUMBER) INCLUDE ($.PONumber, $.LineItems.Part.UnitPrice) VALUE(VARCHAR2) INCLUDE ($.User, $.Reference, $.ShippingInstructions.Address.state, $.ShippingInstructions.Address.zipCode) '); Alternatively, you can modify the index using the PATHLIST parameter, whose value is a named list of the paths to be included, created using PL/SQL subprograms CTX_DDL.create_path_list and CTX_DDL.add_path, as follows: BEGIN CTX_DDL.create_path_list('json_pl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE); CTX_DDL.add_path('json_pl', 'NUMBER', '$.PONumber'); CTX_DDL.add_path('json_pl', 'NUMBER', '$.LineItems.Part.UnitPrice'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.state'); CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode'); END; / ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD PATHLIST JSON_PL'); This example modifies a JSON search index with path subsetting for numeric-value ranges, where it excludes the field located at path $.PONumber . ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD SEARCH_ON VALUE(NUMBER) EXCLUDE ($.PONumber)');
TEXT_VALUE[(data_types)]	This example modifies a JSON search index with path subsetting for range searches and full text searches. It indexes fields $.PONumber and $.LineItems.UnitPrice exclusively for numeric range search, while also indexing the fields $.Reference, $.User, $.ShippingInstructions.Address.state, and $.ShippingInstructions.Address.zipCode for both full text searches and range searches. ALTER INDEX json_search_idx REBUILD PARAMETERS ('ADD SEARCH_ON VALUE(NUMBER) INCLUDE ($.PONumber, $.LineItems.Part.UnitPrice) TEXT_VALUE(VARCHAR2) INCLUDE ($.User, $.Reference, $.ShippingInstructions.Address.state, $.ShippingInstructions.Address.zipCode) ');

Removing indexing components using REMOVE SEARCH_ON

You can remove an existing indexing target (such as a data type component) from an index by using REMOVE SEARCH_ON.

The syntax is:

ALTER INDEX <idx> REBUILD PARAMETERS ('REMOVE SEARCH_ON <target>');

Note the following:

• Usage search targets like TEXT_VALUE, VALUE, or TEXT_VALUE_STRING is not supported.

• You cannot remove a component that is currently not enabled.

• You cannot remove all enabled components.

Removing fields from thepath-subsetting specification using REMOVE SEARCH_ON

You can remove specific paths from an index’s data type path subsetting specification by using REMOVE SEARCH_ON.

The syntax is:

ALTER INDEX <idx> REBUILD PARAMETERS ('REMOVE SEARCH_ON <target> [(INCLUDE | EXCLUDE) (<path subsetting list>)]');

Note the following:

• You cannot modify the index with INCLUDE path subsetting specifications using the REMOVE SEARCH_ON <target> (EXCLUDE).

• You cannot modify the index with EXCLUDE path subsettingspecifications using the REMOVE SEARCH_ON <target> (INCLUDE).

• You cannot specify both the INCLUDE and EXCLUDE clauses.

Removing PATHLIST parameter

You can remove PATHLIST parameter used for path subsetting specifications.

The syntax is:

REMOVE PATHLIST <pathlist_identifier>

Replacing an existing path subsetting specification for an index with a new specification

You can remove the existing path subsetting specification and replace it with the new ones by using REPLACE SEARCH_ON. The REPLACE SEARCH_ON statement serves as a shorthand for sequentially executing REMOVE SEARCH_ON followed by ADD SEARCH_ON. This consolidated operation efficiently clears existing path subsetting specifications and adds new ones in a single step, simplifying the process of modifying the index without the need for separate execution of REMOVE SEARCH_ON and ADD SEARCH_ON statements.

The syntax is:

REPLACE SEARCH_ON (TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)])
   [(INCLUDE | EXCLUDE) (path_subsetting_list)]

Note the following:

• You cannot modify the index with INCLUDE path subsetting specifications using the REPLACE SEARCH_ON (EXCLUDE).

• You cannot modify the index with EXCLUDE path subsettingspecifications using the REPLACE SEARCH_ON (INCLUDE).

• You cannot specify both the INCLUDE and EXCLUDE clauses.

You can also replace the existing path subsetting specification with a new specification by using REPLACE PATHLIST. The PATHLIST parameter value is a named list of the paths to be included, created using PL/SQL subprograms CTX_DDL.create_path_list and CTX_DDL.add_path.

The REPLACE PATHLIST statement serves as a short-hand to sequentially remove the existing path subsetting specification from the index, followed by adding the new path subsetting specification in the specified pathlist preference. The consolidated operation simplifies the process of modifying the index without the need for separate execution of multiple ALTER INDEX operations

The syntax is:

REPLACE PATHLIST <pathlist_identifier>

ALTER INDEX Syntax for XML Search Index

ALTER INDEX [schema.]index REBUILD
PARAMETERS(
  [SEARCH_ON (TEXT | TEXT_VALUE(data_types) | VALUE (data_types))]
  [REMOVE SEARCH_ON VALUE(VARCHAR2)]
  );

[schema.]index

Specifies the name of the XML search index that you want to modify.

SEARCH_ON (TEXT | TEXT_VALUE(data_types) | VALUE(data_types))

Modifies search preferences specified for an existing XML search index.

Note: You can use the SEARCH_ON clause only for JSON and XML search indexes.

You can specify one of the following SEARCH_ON options:

Table 6 ALTER_INDEX SEARCH_ON Options

Option	Description
TEXT	Enables full-text search component, which indicates that only textual data is indexed for full-text search queries. This also includes queries that rely on path information. The index is used for XMLEXISTS predicates that references the XQuery Full Text operators and clauses. If your queries involve only full-text search and not string-range search or numeric search, then you can save some index maintenance time and disk space by specifying this option. For example: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON TEXT');
VALUE(data_types)	Enables range-search component for the specified data types. This allows the index to be picked up for predicates using relational operators (>, <, ==, >=, <=, !=). An XML search index that only has the SEARCH_ON VALUE component enabled cannot answer full-text queries, if XQuery Full Text operators are present in an XMLEXISTS predicate. You must specify one or more data types: BINARY_DOUBLE and NUMBER for indexing numeric values. TIMESTAMP for indexing date-time values. VARCHAR2 for indexing complete string values. The string values are indexed as is without tokenization or other transformations. All the strings that are smaller than or equal to 237 bytes are indexed. For example: ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON VALUE(BINARY_DOUBLE)'); ALTER INDEX [schema.]index REBUILD PARAMETERS ('SEARCH_ON VALUE(BINARY_DOUBLE, NUMBER, TIMESTAMP, VARCHAR2)');
TEXT_VALUE(data_types)	Enables both the full-text and range-search components for the specified data types. For range-search queries, you must specify one or more data types, such as NUMBER (for indexing numeric values) and TIMESTAMP (for indexing date-time values). For example: ALTER INDEX [schema.]index REBUILD PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER)'); ALTER INDEX [schema.]index REBUILD PARAMETERS('SEARCH_ON TEXT_VALUE(NUMBER, TIMESTAMP)');

Note:

You cannot use SEARCH_ON NONE and SEARCH_ON TEXT_VALUE_STRING for an XML search index.

You must explicitly specify a data type with the TEXT_VALUE and VALUE options for an XML search index, otherwise the statement will result in an error.

Guidelines for specifying SEARCH_ON transitions:

When you specify the SEARCH_ON clause in the ALTER INDEX REBUILD statement, the system determines both your current configuration and the set of components that you want to enable. The statement then enables any new components and rebuilds the index. If all requested components have already been enabled, this action is the same as an index rebuild. Rebuilding allows the XML search index to be regenerated with newly enabled indexing and query components.

Note that range-search components of different data types are considered as independent components.

You can disable only the VARCHAR2 range-search component. To disable other components, you must first drop the index using the DROP INDEX statement and then re-create the index with the required components enabled.

Removing indexing components:

You can remove the VARCHAR2 data type from any range-search components (VALUE or TEXT_VALUE). Removing the VARCHAR2 data type can save you index maintenance time and disk space.

The syntax is:

REMOVE SEARCH_ON VALUE(VARCHAR2)

ALTER INDEX Syntax for Hybrid Vector Index

ALTER INDEX [schema.]index_name REBUILD
  PARAMETERS(
      ['UPDATE VECTOR INDEX [VECTOR_IDXTYPE HNSW/IVF]']
      ['REPLACE vectorizer vectorizer_pref_name']
      )
  [PARALLEL n];

Note:

• If you do not specify the PARAMETERS clause, then all parts of the hybrid vector index (both Oracle Text index and vector index) are recreated with existing preference settings.

• Renaming hybrid vector indexes using the ALTER INDEX RENAME syntax is not supported.

• The ALTER INDEX parameter UDPATE VECTOR INDEX is not supported for Local HVI and HNSW vector indexes.

[schema.]index_name

Specifies name of the hybrid vector index that you want to modify.

PARAMETERS(UPDATE VECTOR INDEX)

Rebuilds both text part and vector part of the hybrid vector index. For text part, the rebuild uses the preferences which are specified during the index creation or the default preferences if not specified. For the vector part (chunking, embedding and vector index creation), rebuild uses the new vectorizer preference specified in the ALTER INDEX REBUILD syntax. See CREATE HYBRID VECTOR INDEX for detailed information on the preferences set during the index creation.

PARAMETERS(REPLACE vectorizer vectorizer_pref_name)

Recreates only the vector index part of a hybrid vector index with the specified vectorizer preference settings.

Note: For non HVI index, replace operation would throw an error, as it cannot replace something that was not present.

PARALLEL

Specifies parallel indexing, as described for the CREATE HYBRID VECTOR INDEX statement.

For detailed information on the PARALLEL clause, see CREATE HYBRID VECTOR INDEX.

Examples:

Here are some examples on how you can modify existing hybrid vector indexes:

• To rebuild all parts of a hybrid vector index:

  Use the following syntax to rebuild all parts of a hybrid vector index (both Oracle Text index and vector index) with the original preference settings:

  Syntax:

  ALTER INDEX index_name REBUILD [PARALLEL n];

  Note that you do not need to specify any PARAMETERS clause when rebuilding both parts of a hybrid vector index.

  Example:

  ALTER INDEX my_hybrid_idx REBUILD;
  
  SELECT (select id from doc_table where rowid = jt.doc_rowid) as doc,
         jt.chunk
  FROM JSON_TABLE(
           DBMS_HYBRID_VECTOR.SEARCH(
             json(
               '{ "hybrid_index_name" : "my_hybrid_idx",
                  "vector" :
                   { "search_text"    : "vector based search capabilities",
                     "search_mode"    : "CHUNK"
                   },
                  "return" :
                   { "topN"           : 10 }
               }')
             ),
             '$[*]' COLUMNS doc_rowid  PATH '$.rowid',
                            chunk      PATH '$.chunk_text') jt;

• To rebuild only the vector index part:

  Use the following syntax to rebuild only the vector index part of a hybrid vector index with the original preference settings:

  Syntax:

  ALTER INDEX index_name REBUILD
    PARAMETERS('UPDATE VECTOR INDEX') [PARALLEL n];

  Example:

  ALTER INDEX my_hybrid_idx REBUILD
    PARAMETERS('UPDATE VECTOR INDEX') PARALLEL 3;
  
  SELECT (select id from doc_table where rowid = jt.doc_rowid) as doc,
         jt.chunk
  FROM JSON_TABLE(
           DBMS_HYBRID_VECTOR.SEARCH(
             json(
               '{ "hybrid_index_name" : "my_hybrid_idx",
                  "vector" :
                   { "search_text"    : "vector based search capabilities",
                     "search_mode"    : "CHUNK"
                   },
                  "return" :
                   { "topN"           : 10 }
               }')
             ),
             '$[*]' COLUMNS doc_rowid  PATH '$.rowid',
                            chunk      PATH '$.chunk_text') jt;

• To recreate indexes with a vectorizer preference:

  You can create a vectorizer preference using the DBMS_VECTOR_CHAIN.CREATE_PREFERENCE PL/SQL function. For detailed information on how to create a vectorizer preference, see CREATE_PREFERENCE. After creating the preference, use the REPLACE vectorizer parameter to pass the preference name here.

  Syntax:

  ALTER INDEX index_name REBUILD
    parameters('REPLACE vectorizer vectorizer_pref_name') [PARALLEL n];

  Note: For non-HVI index, the REPLACE operation would throw an error.

  Example:

  ALTER INDEX my_hybrid_idx REBUILD
    parameters('REPLACE vectorizer my_vectorizer_pref') [PARALLEL n];

• To replace only the model and/or vector index type

  For an existing HVI index, you can replace the model and/or the index type without specifying the full vectorizer preference using the following syntax.

  Syntax:

  ALTER INDEX schema.index_name REBUILD[
  parameters('REPLACE MODEL model_name VECTOR_IDXTYPE hnsw/ivf')];

  Example:

  ALTER INDEX schema.my_hybrid_idx REBUILD[
  parameters('REPLACE MODEL my_model_name VECTOR_IDXTYPE ivf')];

  Note: For non HVI indexes, this syntax would throw an error. If a vectorizer is also specified alongside the model and/or vector_idxtype, it would lead to an error, as only one of either vectorizer or model/vector_idxtype is allowed.

For detailed information on managing hybrid vector indexes, see Oracle AI Database AI Vector Search User’s Guide.

ALTER INDEX Sub_Lexer Syntax

New paramstring =
'REPLACE
     [DATASTORE datastore_pref]
     [FILTER filter_pref]
     [LEXER lexer_pref]
     [WORDLIST wordlist_pref]
     [STORAGE storage_pref]
     [STOPLIST stoplist]
     [SECTION GROUP section_group]
     [MEMORY memsize
     [[POPULATE | NOPOPULATE]
     [INDEX SET index_set]
     [METADATA preference new_preference]
     [[METADATA] MAINTENANCE AUTO | MAINTENANCE MANUAL]
     [[METADATA] SYNC (MANUAL | EVERY "interval-string" | ON COMMIT)]
     [[METADATA] TRANSACTIONAL|NONTRANSACTIONAL

| RESUME [memory memsize]
| OPTIMIZE [token index_token | fast | full [maxtime (time | unlimited)]
| SYNC [memory memsize]
| ADD STOPWORD word [language language][LANGUAGE_DEPENDENT(TRUE|FALSE)]
| ADD ZONE SECTION section_name tag tag
| ADD FIELD SECTION section_name tag tag [(VISIBLE | INVISIBLE)]
| ADD ATTR SECTION section_name tag tag@attr
| ADD STOP SECTION tag
| ADD SUB_LEXER sub_lexer_name LANGUAGE language [ALT_VALUE alternate_value_for_language] [LANGUAGE_DEPENDENT (TRUE|FALSE)]
| REMOVE SUB_LEXER LANGUAGE language
| REMOVE STOPWORD word [LANGUAGE language]
| REMOVE STOPWORDS FOR LANGUAGE language
| MIGRATE to MULTI_STOPLIST [LANGUAGE COLUMN lang]
| MIGRATE FIELD SECTION field_section_name to [READ ONLY] MDATA
| UPDATE SUB_LEXER LANGUAGE language TO sub_lexer_preference
| ADD MDATA SECTION secname TAG sectag READ ONLY

Sub_Lexer Example

ALTER INDEX myidx PARAMETERS('ADD SUB_LEXER mycompany_lexer LANGUAGE mycompany LANGUAGE_DEPENDENT FALSE');

ALTER INDEX myidx PARAMETERS('REMOVE STOPWORDS FOR LANGUAGE mycompany');

Sub_Lexer Notes

The language can be Oracle predefined language symbols (globalization support name or abbreviation of an Oracle Text-supported language), or user-defined symbols for language independent sub_lexer or stopword.

ADD SUB_LEXER

The following conditions apply:

• If LANGUAGE_DEPENDENT clause is not provided, it will default TRUE.

• Sync will be blocked (or it will be blocked by sync).

• If adding first language independent sub_lexer, then base table will also be locked.

• Adding first language independent sub_lexer or stopword will take longer to complete. Otherwise, it should take fraction of a second to complete unless it’s being blocked by ongoing sync process on the same index.

REMOVE SUB_LEXER

Will succeed only if there are no documents with language column set to the symbol for the sub_lexer being removed.

REMOVE STOPWORD

The following conditions apply:

• If LANGUAGE clause is not specified, it is assumed that the index is using basic_stoplist. If the index is not using basic_stoplist, an error will be raised.

• If the index is using basic_stoplist (instead of multi_stoplist), then it will succeed only if the base table is empty.

• If the index is using multi_stoplist, and user specifies “ALL” for LANGUAGE clause, then it will succeed only if the base table is empty.

• If the index is using multi_stoplist, and user specifies a symbol for LANGUAGE clause, then it will succeed only if there are no documents with language column set to the symbol for the stopword being removed.

See Also: ALTER INDEX REBUILD Syntax

MIGRATE TO MULTI_STOPLIST [LANGUAGE COLUMN lang]

The following conditions apply:

• Migrate the stoplist of an existing Text index to multi_stoplist. The language of the existing stopwords will have the value of ALL.

• If LANGUAGE column has already been defined for the index:

  • LANGUAGE COLUMN can be skipped (old language column is retained for the index).

  • If LANGUAGE COLUMN is specified and there is a mismatch between index language column and the one specified, an error will be raised.

• LANGUAGE COLUMN must be specified for the index; otherwise, an error is raised.

MIGRATE FIELD SECTION TO MDATA SECTION

The following conditions apply:

• Allow user to convert a field section to MDATA section. Specify READ ONLY if the MDATA section is meant to be a READ_ONLY MDATA section (ADD and REMOVE not allowed).

• Limitation: Tokens in migrated MDATA sections will not have typical MDATA characteristics - case information, tokens being stored as it is in the document, etc. To retain these, those documents need to be reindexed.

UPDATE SUB_LEXER LANGUAGE SUB_LEXER_SYMBOL TO SUB_LEXER_PREFERENCE

The following conditions apply:

• Allows user to update sublexer dynamically.

• Language, alt_value, language dependency should remain same for the old and new sublexer preference.

• For updating the default sublexer, the syntax is:

UPDATE SUB_LEXER DEFAULT TO SUB_LEXER_PREFERENCE

ADD MDATA SECTION secname TAG sectag READ ONLY

The following conditions apply:

• Allows users to add MDATA section to the index.

• Cannot be used with NULL/AUTO/PATH section groups.

ALTER INDEX Examples

Resuming Failed Index

The following statement resumes the indexing operation on newsindex with 2 megabytes of memory:

ALTER INDEX newsindex REBUILD PARAMETERS('resume memory 2M');

Rebuilding an Index

The following statement rebuilds the index, replacing the stoplist preference with new_stop.

ALTER INDEX newsindex REBUILD PARAMETERS('replace stoplist new_stop');

Rebuilding a Partitioned Index

The following example creates a partitioned text table, populates it, and creates a partitioned index. It then adds a new partition to the table and rebuilds the index with ALTER INDEX as follows:

PROMPT create partitioned table and populate it

create table part_tab (a int, b varchar2(40)) partition by range(a)
(partition p_tab1 values less than (10),
 partition p_tab2 values less than (20),
 partition p_tab3 values less than (30));

insert into part_tab values (1,'Actinidia deliciosa');
insert into part_tab values (8,'Distictis buccinatoria');
insert into part_tab values (12,'Actinidia quinata');
insert into part_tab values (18,'Distictis Rivers');
insert into part_tab values (21,'pandorea jasminoides');
insert into part_tab values (28,'pandorea rosea');

commit;

PROMPT create partitioned index
create index part_idx on part_tab(b) indextype is ctxsys.context
local (partition p_idx1, partition p_idx2, partition p_idx3);

PROMPT add a partition and populate it
alter table part_tab add partition p_tab4 values less than (40);
insert into part_tab values (32, 'passiflora citrina');
insert into part_tab values (33, 'passiflora alatocaerulea');
commit;

The following statement rebuilds the index in the newly populated partition. In general, the index partition name for a newly added partition is the same as the table partition name, unless the name has already been used. In this case, Oracle Text generates a new name.

alter index part_idx rebuild partition p_tab4;

The following statement queries the table for the two hits in the newly added partition:

select * from part_tab where contains(b,'passiflora') >0;

The following statement queries the newly added partition directly:

select * from part_tab partition (p_tab4) where contains(b,'passiflora') >;

Replacing Index Metadata: Changing Single-Lexer to Multilexer

The following example demonstrates how an application can migrate from single-language documents (English) to multilanguage documents (English and Spanish) by replacing the index metadata for the lexer.

REM creates a simple table, which stores only English (American) text

create table simple (text varchar2(80));
insert into simple values ('the quick brown fox');
commit;

REM create a simple lexer to lex this English text

begin
  ctx_ddl.create_preference('us_lexer','basic_lexer');
end;
/

REM create a text index on the simple table
create index simple_idx on simple(text)
indextype is ctxsys.context parameters ('lexer us_lexer');

REM we can query easily
select * from simple where contains(text, 'fox')>0;

REM now suppose we want to start accepting Spanish documents.
REM first we have to extend the table with a language column
alter table simple add (lang varchar2(10) default 'us');

REM now let's create a Spanish lexer,
begin
  ctx_ddl.create_preference('e_lexer','basic_lexer');
  ctx_ddl.set_attribute('e_lexer','base_letter','yes');
end;
/
REM Then create a multilexer incorporating our English and Spanish lexers.
REM Note that the DEFAULT lexer is the exact same lexer, with which we have
REM have already indexed all the documents.
begin
  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','spanish','e_lexer');
end;
/
REM next replace our metadata
alter index simple_idx rebuild
parameters ('replace metadata language column lang lexer m_lexer');

REM We are ready for some Spanish data.  Note that we could have inserted
REM this BEFORE the alter index, as long as we did not SYNC.
insert into simple values ('el zorro marrón rápido', 'e');
commit;
exec ctx_ddl.sync_index('simple_idx');
REM now query the Spanish data with base lettering:
select * from simple where contains(text, 'rapido')>0;

Optimizing the Index

To optimize your index, use CTX_DDL.OPTIMIZE_INDEX.

Synchronizing the Index

To synchronize your index, use CTX_DDL.SYNC_INDEX.

Adding a Zone Section

To add to the index the zone section author identified by the tag <author>, enter the following statement:

ALTER INDEX myindex REBUILD PARAMETERS('add zone section author tag author');

Adding a Stop Section

To add a stop section identified by tag <fluff> to the index that uses the AUTO_SECTION_GROUP, enter the following statement:

ALTER INDEX myindex REBUILD PARAMETERS('add stop section fluff');

Adding an Attribute Section

Assume that the following text appears in an XML document:

<book title="Tale of Two Cities">It was the best of times.</book>

Assume also that you want to create a separate section for the title attribute and you want to name the new attribute section booktitle. To do so, enter the following statement:

ALTER INDEX myindex REBUILD PARAMETERS('add attr section booktitle tag
title@book');

Adding an SDATA Section

To add an SDATA section S1 of NUMBER data type and identified by tag T1, to the index, enter the following statement:

ALTER INDEX myindex PARAMETERS('add sdata section S1 tag T1 datatype NUMBER);

Disabling Automatic Background Index Optimization

The following example disables optimize token and optimize full jobs which are automatically running in the background:

ALTER INDEX myindex PARAMETERS ('REPLACE METADATA OPTIMIZE (MANUAL)');

Using Flashback Queries

If a Text query is flashed back to a point before an ALTER INDEX statement was issued on the Text index for which the query is being run, then:

• The query optimizer will not choose the index access path for that given index because the index is treated according to its creation time with ALTER INDEX. Therefore, to the query optimizer, the index is perceived not to exist.

• The functional processing of the Text operator will fail with ORA-01466 or ORA-08176 errors if the ALTER INDEX statement involves re-creation of DR$ index tables.

To work around this issue, use the DBMS_FLASHBACK package. For example:

EXEC dbms_flashback.enable_at_system_change_number(:scn);
SELECT id from documents WHERE CONTAINS(text, 'oracle')>0;
EXEC dbms_flashback.disable;

See Also: Using DBMS_FLASHBACK Package in Oracle Database Development Guide

Notes

Add Section Constraints

Before altering the index section information, Oracle Text checks the new section against the existing sections to ensure that all validity constraints are met. These constraints are the same for adding a section to a section group with the CTX_DDL PL/SQL package and are as follows:

• You cannot add zone, field, or stop sections to a NULL_SECTION_GROUP.

• You cannot add zone, field, or attribute sections to an automatic section group.

• You cannot add attribute sections to anything other than XML section groups.

• You cannot have the same tag for two different sections.

• Section names for zone, field, and attribute sections cannot intersect.

• You cannot exceed 64 fields per section.

• You cannot add stop sections to basic, HTML, XML, or news section groups.

• SENTENCE and PARAGRAPH are reserved section names.

• You cannot have embedded blanks in section and field names.

Related Topics

• Oracle Text Indexing Elements

• CREATE INDEX

• ALTER INDEX

• OPTIMIZE_INDEX