Index Maintenance

This section describes maintaining your index in the event of an error or indexing failure.

Viewing Index Errors

Sometimes an indexing operation might fail or not complete successfully. When the system encounters an error indexing a row, it logs the error in an Oracle Text view.

You can view errors on your indexes with CTX_USER_INDEX_ERRORS. View errors on all indexes as CTXSYS with 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 of errors, you can issue:

DELETE FROM ctx_user_index_errors;

See Also: Oracle Text Reference to learn more about these views.

Dropping an Index

You must drop an existing index before you can re-create it with CREATE INDEX.

You drop an index using the DROP INDEX command in SQL.

For example, to drop an index called newsindex, issue the following SQL command:

DROP INDEX newsindex; 

If Oracle cannot determine the state of the index, for example as a result of an indexing crash, you cannot drop the index as described above. Instead use:

DROP INDEX newsindex FORCE;

See Also: Oracle Text Reference to learn more about this command.

Resuming Failed Index

You can resume a failed index creation operation using the ALTER INDEX command. You typically resume a failed index after you have investigated and corrected the index failure.

Index optimization commits at regular intervals. Therefore if an optimization operation fails, all optimization work has already been saved.

See Also: Oracle Text Reference to learn more about the ALTER INDEX command syntax.

Example: Resuming a 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

You can rebuild a valid index using ALTER INDEX. You might rebuild an index when you want to index with a new preference.

See Also: Oracle Text Reference to learn more about the ALTER INDEX command syntax.

Example: Rebuilding and Index

The following command rebuilds the index, replacing the lexer preference with my_lexer.

ALTER INDEX newsindex REBUILD PARAMETERS('replace lexer my_lexer');

Dropping a Preference

You might drop a custom index preference when you no longer need it for indexing.

You drop index preferences with the procedure CTX_DDL.DROP_PREFERENCE.

Dropping a preference does not affect the index created from the preference.

See Also: Oracle Text Reference to learn more about the syntax for the CTX_DDL.DROP_PREFERENCE procedure.

Example

The following code drops the preference my_lexer.

begin
ctx_ddl.drop_preference('my_lexer');
end;