ALTER INDEX

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

Purpose

Use ALTER INDEX to perform the following maintenance tasks for a Text index:

• Rename the index. See RENAME Syntax.

• Rebuild the index using different preferences. See REBUILD Syntax.

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

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

• Optimize the index. See REBUILD Syntax.

• Add stopwords to the index. See REBUILD Syntax.

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

RENAME Syntax

Use the following syntax to rename an index:

ALTER INDEX [schema.]index_name RENAME to new_index_name ;  

schema.index_name

Specify the name of the index to be renamed.

new_index_name

Specify the new name for schema.index. The new_index_name parameter can be no more than 25 characters. If you specify a name longer than 25 characters, Oracle returns an error and the renamed index is no longer valid.

Note: When new_index_name has more than 25 characters and less than 30 characters, 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 command and then recreate and drop index_name.

REBUILD Syntax

The following syntax is used to rebuild the index, 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 [online] [parameters (paramstring)];

[online]

Optionally specify the online parameter for non-blocking 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]

           |    resume [memory memsize]
           |    optimize [fast | full [maxtime (time | unlimited)]
           |    sync [memory memsize]
           |    add stopword word
           |    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.

resume [memory memsize]

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

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

Optimizes the index. Specify either fast or full optimization.

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 (garbage collection.)

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, which ever 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.

sync [memory memsize]

Synchronizes the index. You can optionally specify the amount of runtime memory to use with memsize.

add stopword word

Dynamically adds a stopword word to the index.

The index is not rebuilt by this command.

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

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.

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

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

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

Examples

Resuming Failed Index

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

ALTER INDEX newsindex rebuild parameters('resume memory 2M');

Rebuilding an Index

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

ALTER INDEX newsindex rebuild parameters('replace stoplist new_stop');

Fast Optimization

The following command optimizes newsindex in fast mode:

ALTER INDEX newsindex rebuild parameters('optimize fast');

Full Optimization

To specify an optimization operation to last for three hours (180 minutes), issue the following command:

ALTER INDEX newsindex rebuild parameters('optimize full maxtime 180');

To optimize the entire index without regard to time, issue the following command:

ALTER INDEX newsindex rebuild parameters('optimize full maxtime unlimited');

To optimize the entire index and to allow queries to be issued during the optimization, issue the following command:

ALTER INDEX newsindex rebuild online parameters('optimize full maxtime 
unlimited');

Synchronizing the Index

The following example synchronizes the index with a runtime memory of 2 megabytes:

ALTER INDEX newsindex rebuild PARAMETERS('sync memory 2M');

Adding a Zone Section

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

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

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

ALTER INDEX myindex rebuild parameters('add attr section booktitle tag 
title@book');

Notes

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 memory improves indexing performance since there is less I/O and improves query performance and maintenance since 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.

Viewing Index Errors

You can view index errors with iMT views. View errors on your indexes with CTX_USER_INDEX_ERRORS. View errors on all indexes as CTXSYS with the CTX_INDEX_ERRORS.

For example to view the most recent errors on your indexes, you can issue:

select err_timestamp, err_text from CTX_USER_INDEX_ERRORS order by err_timestamp 
desc;

To clear the view, you can issue:

delete from CTX_USER_INDEX_ERRORS;

Viewing Pending DML

With the CTX_USER_PENDING view, you can view newly updated, deleted, or inserted rows requiring index synchronization. For example, to view pending DML on all your indexes, issue the following statement:

select pnd_index_name, pnd_rowid, to_char(pnd_timestamp, 'dd-mon-yyyy 
hh24:mi:ss') timestamp from ctx_user_pending;

This statement gives output in the form:

PND_INDEX_NAME                 PND_ROWID          TIMESTAMP
------------------------------ ------------------ --------------------
MYINDEX                        AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50

Add Section Constraints

Before altering the index section information, the new section is checked 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

Related Topics

CTX_DDL.CREATE_PREFERENCE in Chapter 7.

CTX_DDL.CREATE_STOPLIST in Chapter 7.

CTX_DDL.CREATE_SECTION_GROUP in Chapter 7.

CREATE INDEX

DROP INDEX