The bottom, or leaf level, of a clustered index contains the data. Therefore, a table and its clustered index are on the same segment. If you create a table on one segment and its clustered index on a different segment, the table migrates to the segment where you created the clustered index. This provides a quick and easy way to move a table to other devices in your database.
See create index in the Reference Manual: Commands.
This example creates a clustered index, without specifying the segment name, using a table on the new_space segment (see “A segment tutorial” for the steps to create this table):
create clustered index mytabl_cix on mytabl(c1)
sp_helpsegment new_space
segment name status ------- ------------- ------ 3 new_space 0 device size free_pages ---------------- ------ ---------- newdevice 3.0MB 1523 total_size total_pages free_pages used_pages reserved_pages ------------- ----------- ----------- ----------- -------------- 3.0MB 1536 1530 6 0
If you have placed a table on a segment, and you must create a clustered index, use the on segment_name clause, or the table migrates to the default segment.