Creating an index on sorted data

If you need to re-create a clustered index or create one on data that was bulk copied into the server in index key order, use the sorted_data option to create index to shorten index creation time.

Since the data rows must be arranged in key order for clustered indexes, creating a clustered index without sorted_data requires that you rewrite the data rows to a complete new set of data pages. Adaptive Server can skip sorting and/or copying the table’s data rows in some cases. Factors include table partitioning and on clauses used in the create index statement.

When creating an index on a nonpartitioned table, sorted_data and the use of any of the following clauses requires that you copy the data, but does not require a sort:

When these options and sorted_data are included in a create index on a partitioned table, the sort step is performed and the data is copied, distributing the data pages evenly on the table’s partitions.

Table 17-1: Using options for creating a clustered index

Options

Partitioned table

Unpartitioned table

No options specified

Parallel sort; copies data, distributing evenly on partitions; creates index tree.

Either parallel or nonparallel sort; copies data, creates index tree.

with sorted_data only or with sorted_data on same_segment

Creates index tree only. Does not perform the sort or copy data. Does not run in parallel.

Creates index tree only. Does not perform the sort or copy data. Does not run in parallel.

with sorted_data and ignore_dup_row or fillfactor or on other_segment or max_rows_per_page

Parallel sort; copies data, distributing evenly on partitions; creates index tree.

Copies data and creates the index tree. Does not perform the sort. Does not run in parallel.

In the simplest case, using sorted_data and no other options on a nonpartitioned table, the order of the table rows is checked and the index tree is built during this single scan.

If the data rows must be copied, but no sort needs to be performed, a single table scan checks the order of rows, builds the index tree, and copies the data pages to the new location in a single table scan.

For large tables that require numerous passes to build the index, saving the sort time reduces I/O and CPU utilization considerably.

Whenever creating a clustered index copies the data rows, the space available must be approximately 120 percent of the table size to copy the data and store the index pages.