5.5.4 Notes on defining range indexes (unfinished status of range indexes)
You define a range index by entering the CREATE INDEX statement after creating the base table but before storing data, in the same manner as when you define a B-tree index.
If a range index is defined for a base table for which row storage segments have been allocated, the defined range index is placed in unfinished status. A range index that is in unfinished status is not valid and cannot be used.
To release a range index from unfinished status, you need to rebuild it. Therefore, determine carefully the columns for which range indexes will be defined before you store any data in the base table.
An error occurs if any of the following operations is performed on a range index that is in unfinished status.
- ■ Operations that will cause an error
-
-
Executing a SELECT statement that uses a range index that is in unfinished status
-
Executing the INSERT or UPDATE statement on a table for which a range index in unfinished status is defined
-
Executing the adbimport command without the -d option specification on a base table for which a range index in unfinished status is defined
-
Executing the adbmergechunk command on a base table for which a range index in unfinished status is defined
-
Executing the adbunarchivechunk command on an archivable multi-chunk table for which a range index in unfinished status is defined
-
For details about how to release a range index from unfinished status, see 15.11.1 Steps to take when unfinished status is applied to a range 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 range 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 range index is placed in unfinished status.
-