Dropping the Default Index Associated with a Table

ALTER TABLE Statement

To drop the default index associated with a table, use the SQL DDL ALTER TABLE statement with the DROP DEFAULT INDEX option.

Considerations

Do not drop the default index on a table until the CALC key, indexes, and referential constraints in which the table participates have been defined. If no other index exists on the table, an area sweep will be initiated each time one of the above components is defined.
Dropping the default index could change the location mode of a table.
Default indexes can be useful whenever it is anticipated that a table will be accessed without WHERE clauses specifying index or CALC keys and without joins that might use referential relationships with other tables. In short, they are useful whenever it is anticipated that the optimizer would otherwise choose area sweeps to satisfy access requests on the table. This is particularly true when it is a sparse table, since a sweep of the default index will only access data pages that contain rows of the table; whereas, an area sweep will access every page of the area.

Note: For more information about when you would choose to drop the default index, see the CA IDMS Database Design Guide.

Example

In the following example, the default index for the EMPLOYEE table is dropped.

alter table emp.employee
   drop default index;