RAID devices appear to Adaptive Server as a single database device, so, although the devices may be capable of supporting the I/O load of parallel sorts, Adaptive Server assigns only a single consumer for the device, by default.
The with consumers clause to the create index statement provides a way to specify the number of consumer processes that create index can use. By testing the I/O capacity of striped devices, you can determine the number of simultaneous processes your RAID device can support and use this number to suggest a degree of parallelism for parallel sorting. As a baseline, use one consumer for each underlying physical device. This example specifies eight consumers:
create index order_ix on orders (order_id) with consumers = 8
You can also use the with consumers clause with the alter table...add constraint clauses that create the primary key and unique indexes:
alter table orders add constraint prim_key primary key (order_id) with consumers = 8
The with consumers clause can be used for creating indexes—you cannot control the number of consumer processes used in internal sorts for parallel queries. You cannot use this clause when creating a clustered index on a partitioned table. When creating a clustered index on a partitioned table, Adaptive Server must use one consumer process for every partition in the table to ensure that the final, sorted data is distributed evenly over partitions.
Adaptive Server ignores the with consumers clause if the specified number of processes is higher than the number of available worker processes, or if the specified number of processes exceeds the server or session limits for parallelism.
