create index sorts data within the index key. This sort may require one or more in-memory sorts and one or more disk-based sorts. Adaptive Server loads the data create index sorts into the data cache associated with the table upon which it is creating an index. The number of data cache buffers the sort uses is limited by the value of number of sort buffers. If all keys being sorted fit into the value for number of sort buffers, Adaptive Server peforms a single, in-memory sort operation. If the keys being sorted do not fit into the sort buffers, Adaptive Server must write the results of the in-memory sort to disk so Adaptive Server can load the next set of index keys into the sort buffers to be sorted.
In addition to the sort buffers allocated from the data cache, this sort operation also requires about 66 bytes of metadata from the procedure cache. The formula for the amount of procedure cache used, assuming all of the sort buffers are used, is:
In this example,
The number of sort buffers set to 500
create index creates a 15 byte field, yielding 131 rows per page
As a result, all 500 2K buffers are used to hold the data being sorted, and the procedure cache uses 2,111 2K buffers:
(131 X 500 X66) / 2048 = 2,111 2K buffers
In this example,
The number of sort buffers set to 5000
create index creates a 8 byte field, yielding about 246 rows per page
As a result, all 5,000 2K buffers are used to hold the data being sorted, and the procedure cache uses 39, 639 2K buffers:
(246 X 5,000 X 66) / 2048 = 39,639 2K buffers
In this example:
The number of sort buffers set to 1000
The table is small, and you can load it completely in to a 800 2K sort buffer data cache, leaving 200 data cache sort buffers available for overhead
create index creates a 50 byte field, yielding about 39 rows per page
As a result, all 5000 2K buffers are used to hold the data being sorted:
(39 X 1,000 X 66) / 2048 = 1,257 2K buffers
But the data cache has 200 2K buffers left over for overhead, so the procedure cache uses 1057 2K buffers.