Table 8-1 summarizes the potential use of parallel access methods in Adaptive Server query processing. In all cases, the query must access at least 20 data pages in the table before the optimizer considers parallel access methods.
Parallel method |
Major cost factors |
Requirements for consideration |
Competing serial methods |
|---|---|---|---|
Partition-based scan |
Number of pages in the largest partition |
Partitioned table with balanced data |
Serial table scan, serial index scan |
Hash-based table scan |
Number of pages in table |
Any outer table in a join query and that is a heap |
Serial table scan, serial index scan |
Clustered index partition scan |
If total number of pages to be scanned <= 2 * number of pages in average-sized partition, then: Total number of pages to be scanned / 2 If total number of pages to be scanned > 2 * number of pages in average-sized partition, then: Average number of pages in a partition |
Partitioned table with a useful clustered index; allpages locking only |
Serial index scan |
Hash-based index scan |
Number of index pages above leaf level to scan + number of leaf-level index pages to scan + (number of data pages referenced in leaf-level index pages / number of worker processes) |
Any table with a useful nonclustered index or a data-only-locked table with a clustered index |
Serial index scan |
Range-based scan |
Number of pages to be accessed in both tables/number of worker processes, plus any sort costs |
Any table in a join eligible for merge join consideration |
Serial merge, nested-loop join |
