ALTER INDEX

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

Purpose

Use ALTER INDEX to perform the following maintenance tasks for a context, ctxcat, or ctxrule index:

All Indextypes

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

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

• Add stopwords to the index. See REBUILD Syntax.

CONTEXT and CTXRULE Indextypes

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

• Process DML in batch (synchronize). See REBUILD Syntax.

• Optimize the index, fully or by token. See REBUILD Syntax.

• Add sections and stop sections to the index. See REBUILD Syntax.

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. If you specify a name longer than 25 bytes, Oracle returns an error and the renamed index is no longer valid.

Note: When new_index_name is more than 25 bytes and less than 30 bytes, Oracle 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.

part_name

Specify the name of the index partition to rename.

new_part_name

Specify the new name for partition.

REBUILD Syntax

The following syntax is used to rebuild the index, rebuild an index partition, resume a failed operation, perform batch DML, add stopwords to index, add sections and stop sections to index, or optimize the index:

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

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 sync, optimize full/fast, or replace in paramstring. These operations work only on the partname you specify.

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 automatically creates the metadata for the new index partition. The new index partition has the same name as the new table partition. You can change the index partition name with ALTER INDEX RENAME. To populate the new index partition, you must rebuild it with ALTER INDEX REBUILD.

Splitting or Merging Partitions

Splitting or merging a table partition with ALTER TABLE renders the index partition(s) invalid. You must rebuild them with ALTER INDEX REBUILD.

[ONLINE]

Optionally specify the ONLINE parameter for nonblocking operation, which allows the index to be queried during an ALTER INDEX synchronize or optimize operation. You cannot specify ONLINE for replace, resume, or when adding stopwords or stop sections.

PARAMETERS (paramstring)

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

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]
[index set index_set]

|    resume [memory memsize]
|    optimize [token index_token | fast | full [maxtime (time | unlimited)]
|    sync [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 preferences, your own or system-defined.

You can only replace 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 only lexer, wordlist, storage index set, and memory preferences.

See Also: Chapter 2, "Indexing" for more information about creating and setting preferences, including information about system-defined preferences.

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.

optimize [token index_token | fast | full [maxtime (time | unlimited)]

Note: This ALTER INDEX operation will not be supported in future releases. To optimize your index, use CTX_DDL.OPTIMIZE_INDEX.

Optimizes the index. Specify token, fast, or full optimization. You typically optimize after you synchronize the index.

When you optimize in token mode, Oracle optimizes only the index token index_token in token mode. Use this method of optimization to quickly optimize index information for specific words.

When you optimize in fast mode, Oracle works on the entire index, compacting fragmented rows. However, in fast mode, old data is not removed.

When you optimize in full mode, you can optimize the whole index or a portion. This method compacts rows and removes old data (deleted rows).

Note: Optimizing in full mode runs even when there are no deleted document rows. This is useful when you need to optimize time-limited batches with the maxtime parameter.

You use the maxtime parameter to specify in minutes the time Oracle is to spend on the optimization operation. Oracle starts the optimization where it left off and optimizes until complete or until the time limit has been reached, whichever comes first. Specifying a time limit is useful for automating index optimization, where you set Oracle to optimize the index for a specified time on a regular basis.

When you specify maxtime unlimited, the entire index is optimized. This is the default. When you specify 0 for maxtime, Oracle performs minimal optimization.

Note: This ALTER INDEX operation applies only to context and ctxrule indexes. It does not apply to ctxcat indexes.

sync [memory memsize]

Note: This ALTER INDEX operation will not be supported in future releases. To synchronize your index, use CTX_DDL.SYNC_INDEX.

Synchronizes the index. You can optionally specify the amount of runtime memory to use with memsize. You synchronize the index when you have DML operations on your base table.

Note: This ALTER INDEX operation applies only to context and ctxrule indexes. It does not apply to ctxcat indexes.

Memory Considerations

The memory parameter memsize specifies the amount of memory Oracle uses for the ALTER INDEX operation before flushing the index to disk. Specifying a large amount of memory improves indexing performance because there is less I/O and improves query performance and maintenance because there is less fragmentation.

Specifying smaller amounts of memory increases disk I/O and index fragmentation, but might be useful if you want to track indexing progress or when run-time memory is scarce.

add stopword word [language language]

Dynamically adds a stopword word to the index.

When your stoplist is a multi-language stoplist, you must specify language.

The index is not rebuilt by this statement.

add zone section section_name tag tag

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: "Add Section Constraints" .

add field section section_name tag tag [(VISIBLE | INVISIBLE)]

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

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.

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: "Add Section Constraints" .

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 section section_name applies only to documents indexed after this operation. Thus 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 indexes. It does not apply to ctxcat indexes.

See Also: "Add Section Constraints" in this section.

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 is always searchable.

The number of stop sections you can add is unlimited.

The index is not rebuilt by this statement.

Note: This ALTER INDEX operation applies only to context indexes. It does not apply to ctxcat indexes.

Add Section Constraints

Before altering the index section information, Oracle 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 field sections.

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

• SENTENCE and PARAGRAPH are reserved section names.

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 then rebuilds the index with ALTER INDEX:

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 Lady Di');
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 it is already been used. In this case, Oracle 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') >0;

Optimizing the Index

Optimizing your index with ALTER INDEX will not be supported in future releases. To optimize your index, use CTX_DDL.OPTIMIZE_INDEX.

Synchronizing the Index

Synchronizing the index with ALTER INDEX will not be supported in future releases. 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>, issue 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, issue 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>

You want to create a separate section for the title attribute and you want to name the new attribute section booktitle. To do so, issue the following statement:

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

Related Topics

CTX_DDL.SYNC_INDEX in Chapter 6, "CTX_DDL Package"

CTX_DDL.OPTIMIZE_INDEX in Chapter 6, "CTX_DDL Package"

CREATE INDEX