Creating Clustered Indexes on Segments

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.

This example creates a clustered index, without specifying the segment name, using a table on the new_space segment :
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.

See create index in the Reference Manual: Commands.