Modifying Secondary Indexes
Secondary indexes are destroyed by default when you modify the base table storage structure. They are destroyed automatically because secondary indexes use the tidp column to reference the row of the base table to which they are pointing. When you modify a table, all the tids of the rows in the base table change, rendering the secondary index useless. For more information, see
Tids on page 30.
Persistence Option
You can use the Persistence option when creating or modifying a secondary index to specify that the index be recreated whenever the base table is modified. By default, indexes are created with no persistence.
In SQL, you can accomplish this task with the CREATE INDEX and MODIFY statements, and the [NO]PERSISTENCE clause. For more information, see the SQL Reference Guide.
In VDBA, this option is found in the Structure of Index and the Create Indexes dialogs.
Example: Enable the Persistence Option
For example, assuming the secondary index empidx was created without enabling the Persistence option, you can modify it to enable this feature, as follows:
modify empidx to btree with persistence;
In VDBA:
1. Open the Structure of Index dialog for the empidx index.
2. Select B-tree from the Structure drop-down list.
3. Enable the Persistence check box.
Changing the Index Storage Structure
The default storage structure for secondary indexes is ISAM; you can choose a different structure when creating an index.
To do this in VDBA, use the Create Indexes dialog.
You can also modify the index to another storage structure after it has been created.
To do this in VDBA, use the Structure of Index dialog.
If a secondary index is modified to B-tree, it cannot contain any data pages. Instead, the leaf pages in the secondary index point directly to data pages in the main table.
Overflow can occur in hash and ISAM secondary indexes, as well as base tables, and must be monitored. One way to eliminate overflow is to use B-tree as the default index structure. If overflow is not a problem, hash or ISAM can be preferable because the indexes are smaller, require less locking, and reuse deleted space.
Secondary indexes are smaller and can be modified more quickly than the base table. When they are used, overflow occurs less frequently because only key values are stored, rather than the entire row.
Because it is quicker to build secondary indexes than to modify the base table, it is easier to experiment with different choices of secondary indexes and different storage structures for them. Remember, however, that it can take longer to update a table with secondary indexes than one without them.
A high degree of duplication in a secondary key can lead to overflow in the secondary index. Repetitive keys are not recommended. Performance benefits can be derived by the inclusion of another column in the secondary index that makes the entire key less repetitive. The less repetitive key reduces the likelihood of overflow chains, resulting in better performance when updates made to the base table require updates to the secondary index. Because overflow chains are reduced, locking and searching overhead is lessened.
If the secondary index to be stored is ISAM or B-tree and the key is not unique, the tidp column is automatically included in the key specified when the index is modified. This achieves key uniqueness without any loss of functionality when the key is used for matches.
Example: Create a B-tree Index for a Table
The following example creates a B-tree index for the emp table:
create index colidx on subsid.emp (column1) with structure = btree;
In VDBA:
1. Open the Create Indexes dialog for the table. For more information, see the online help. Also see the chapter “Choosing Storage Structures and Secondary Indexes.”
2. Enter an appropriate name in the Index Name edit control.
3. Select B-tree from the Structure drop-down list.
4. Select an appropriate key column in the Base Table Columns list box, and click the double-right arrow (>>) to add the column to the Index Columns list box.
Example: Modify an Existing Index to B-tree
This example modifies an existing index to use the B-tree storage structure (assuming it was created using another storage structure):
modify colidx to btree on column1;
In VDBA:
1. Open the Structure of Index dialog for the index.
2. Select B-tree from the Structure drop-down list.
3. Enable the appropriate columns in the Columns list.