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