|
|
|
Sorting occurs commonly during many index, copy, and modify operations. (Sorting also occurs in the processing of the equivalent SQL statements.)
When the size of the sort requires disk space, temporary work locations are used.
A default work location area is defined during installation.
The disk space required for sorting depends on how much sorting needs to be done. If the table to be sorted is badly out of sorted order, more space can be used than if it is in nearly sorted order.
For a nearly sorted table, the amount of work location space is equal to the uncompressed size of the table.
For a table that is badly out of order, the maximum work location space is two times the uncompressed size of the table, and the space required per location can be estimated by the formula:
(2 * uncompressed_table_size) / number_of_work_locations
|
|
|
If any work location runs out of disk space during a sort, the sort fails and the associated transaction is aborted.
To correct this situation, you can add additional work locations or provide more space on the device that filled (by removing or relocating unneeded files). Alternatively, the device that filled can be dropped through the set work locations statement.
For information about work locations and the set work locations statement, see Work Locations. For a discussion on deleting unneeded files, see the chapter "Maintaining Databases."
|
|
|
Sort files can be left in work locations after certain types of failures.
In VDBA, use the "verify database" procedure to remove these orphaned files. For more information, see Verifying a Database in VDBA online help.
To accomplish this task at the command line, use the verifydb command. For more information, see the Command Reference Guide.
|
|
|
The use of multiple work locations does not generally affect overall sort performance.
In UNIX, the performance of very large sorts can be affected by the amount of available operating system cache memory. While most aspects of server performance are largely unaffected by the OS cache size, sorts employ the OS cache, as well as the Ingres DBMS Server DMF cache. Sorting time can sometimes be improved by configuring additional OS memory.