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:
ignore_dup_row
fillfactor
The on segment_name clause, specifying a different segment from the segment where the table data is located
The max_rows_per_page clause, specifying a value that is different from the value associated with the table
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.
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.
