5.4.3 Notes on defining text indexes (unfinished status of text indexes)
To define text indexes, you enter the CREATE INDEX statement after creating the base table but before storing data.
If a text index is defined for a base table for which row storage segments have been allocated, the defined text index is placed in unfinished status. A text index in unfinished status cannot be used because it is invalid.
To release a text index from unfinished status, you need to rebuild it. Therefore, determine carefully the columns for which text indexes will be defined before storing data in a base table.
An error occurs if any of the following operations is performed on a text index that is in the unfinished status.
- ■ Operations that will cause an error
-
-
Executing a SELECT statement that uses a text index in unfinished status
-
Executing the INSERT, UPDATE, or DELETE statement on a base table for which a text index in unfinished status is defined
-
Executing the adbimport command without the -d option specification on a base table for which a text index in unfinished status is defined
-
Executing the adbmergechunk command on a base table for which a text index in unfinished status is defined
-
Executing the adbunarchivechunk command on an archivable multi-chunk table for which a text index in unfinished status is defined
-
For details about how to release a text index from unfinished status, see 15.10.1 Steps to take when unfinished status is applied to a text index.
- Note
-
For example, row storage segments have not been allocated at the following times. If the CREATE INDEX statement is executed at these times, the text index is created normally.
-
Immediately after defining a base table
-
Immediately after executing the TRUNCATE TABLE statement
If the DELETE statement is used to delete all rows in a table, the row storage segments remain allocated. Therefore, after all table rows are deleted by using the DELETE statement, if the CREATE INDEX statement is executed, the text index is placed in unfinished status.
-