ALTER INDEX

Use the ALTER INDEX statement to change or rebuild an existing Oracle Text index or JSON search 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:

All Index Types

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

CONTEXT and CTXRULE Index Types

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

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 REBUILD PARALLEL n
ALTER INDEX REBUILD PARAMETERS ('REPLACE DATASTORE datastore_pref')
ALTER INDEX REBUILD PARAMETERS ('REPLACE WORDLIST wordlist_pref')

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:

ALTER INDEX index_name PARAMETERS (ADD SDATA SECTION sdata_section_name TAG sdata_section_tag DATATYPE sdata_section_datatype);

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 22 bytes for a partitioned index in earlier releases of Oracle Database that have not been upgraded to Oracle Database 22c Release 2 ( 22.2). If you specify a name longer than 25 bytes (or longer than 22 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 (22 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 22.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 22.2 or new Oracle Database 22c Release 2 (22.2) installations can have the new_index_name parameter no more than 228 bytes, and 228 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] 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 TEXT_VALUE]
| 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.

Note:

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:

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:

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:

In these unsafe cases, Oracle recommends rebuilding the index.

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

Specifies SYNC for synchronization of the CONTEXT index when a DML change has occurred to the base table. You can specify one of the SYNC methods shown in Table 2-2.

Table 2 ALTER INDEX SYNC Methods

SYNC Type Description
MANUAL Means no automatic synchronization. This is the default. You must manually synchronize the index using CTX_DDL.SYNC_INDEX.

Use MANUAL to disable ON COMMIT and EVERY synchronization.
EVERY interval-string 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 long enough 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” 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, because otherwise it causes significant fragmentation of the main index, requiring frequent OPTIMIZE calls.

See “Enabling Automatic Index Synchronization” for an example of ON COMMIT syntax.

Refer to 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.

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: Synchronous update is not supported with the TRANSACTIONAL option and for updates that cause row movement.

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 2 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.

The Optimize token job is scheduled to run every midnight from 12 A.M. to 3 A.M. except on Saturday night, in order to optimize the top 10 most fragmented tokens (determined automatically). 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.

The Optimize full job is scheduled to run weekly from 12 A.M. every Saturday night in order to optimize index tables and clean up $N.

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 optimize token for the top 10 most fragmented tokens at a regular interval specified by the value interval-string, which takes the same syntax as scheduler jobs.

Ensure that interval-string is set to a long enough 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 an escape character with another single quote.

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”

Note: 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 (NONE | TEXT | TEXT_VALUE)]
  );

Note:

The REPLACE keyword is not required with the ALTER INDEX REBUILD PARAMETERS statement for changing the JSON search index preferences. Also, 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

Modifies the JSON search index name.

DATAGUIDE ON | OFF

Modifies data guide support for an existing JSON search index. 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:

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

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

SEARCH_ON (TEXT | TEXT_VALUE)

Modifies search preferences specified for JSON search index. Enabling a search option rebuilds the index with updated preferences.

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

Specify one of the following options:

Specifying TEXT_VALUE has a higher cost in indexing time and index space.

In the SEARCH_ON clause, specify a value as described in “Table 2-3”.

Table 3 SEARCH_ON: Parameter Values

Current Parameter Value Supported New Parameter Value
NONE TEXT
TEXT_VALUE
TEXT TEXT_VALUE

For example, you can change the SEARCH_ON parameter value from NONE to TEXT or from TEXT to TEXT_VALUE but not from TEXT_VALUE to TEXT or from TEXT to NONE.

Note: You must specify the SEARCH_ON preferences as described in “Table 2-3”. Otherwise, an error is returned while rebuilding the index.

To change the SEARCH_ON parameter value to any of the unsupported values (that is, from TEXT_VALUE to TEXT or from TEXT to NONE), you must first drop the index using the DROP INDEX statement and then recreate the index using the CREATE SEARCH INDEX statement.

ALTER INDEX Sub_Lexer Syntax

Use the following syntax.

See Also: “ALTER INDEX Purpose” for list of types of indexes and syntax for ALTER INDEX

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] 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:

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:

See Also: “ALTER INDEX REBUILD Syntax”

MIGRATE TO MULTI_STOPLIST [LANGUAGE COLUMN lang]

The following conditions apply:

MIGRATE FIELD SECTION TO MDATA SECTION

The following conditions apply:

UPDATE SUB_LEXER LANGUAGE SUB_LEXER_SYMBOL TO SUB_LEXER_PREFERENCE

The following conditions apply:

UPDATE SUB_LEXER DEFAULT TO SUB_LEXER_PREFERENCE

ADD MDATA SECTION secname TAG sectag READ ONLY

The following conditions apply:

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:

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:

Related Topics