Using the sorted_data Option to Speed Sorts

The sorted_data option can reduce the time needed to create an index by skipping the sort step and by eliminating the need to copy the data rows to new pages in certain cases. The speed increase becomes significant on large tables, and increases to several times faster in tables larger than 1GB.

If sorted_data is specified, but data is not in sorted order, the SAP ASE server displays an error message, and the command fails.

The effects of sorted_data for creating a clustered index depend on whether the table is partitioned and whether certain other options are used in the create index command. Some options require data copying, if used at all, for nonpartitioned tables and sorts plus data copying for partitioned tables, while others require data copying only if you use:

The ignore_dup_row option
The fillfactor option
The on segmentname clause to specify a segment that is different from the segment where the table data is located
The max_rows_per_page clause to specify a value that is different from the value associated with the table

Creating a nonunique, nonclustered index succeeds, unless there are rows with duplicate keys. If there are rows with duplicate keys, the SAP ASE server displays an error message, and the command fails.

This table shows when the sort is required and when the table is copied for partitioned and nonpartitioned tables.

Options	Partitioned Table	Unpartitioned Table
No options specified	Parallel sort necessary only for creating a clustered index on a round-robin-partitioned table; 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.