For a given table in a query, the optimizer first evaluates the available indexes and partitions to determine which access methods it can use to scan the table’s data. For any query that involves a join, Adaptive Server considers a range-based merge join, and considers using a parallel merge join if parallel query processing is enabled. The use of a range-based scan does not depend on table partitioning, and range-based scans can be performed using clustered and nonclustered indexes. They are considered, and are very likely to be used, on tables that have no useful index on the join key.
Table 25-2 shows the other parallel access methods that the optimizer may evaluate for different table and index combinations. Hash-based table scans are considered only for the outer table in a query, unless the query uses the parallel optimizer hint.
No useful index |
Useful clustered index |
Useful index (nonclustered or clustered on data-only- locked table) |
|
|---|---|---|---|
Partitioned Table |
Partition scan Hash-based table scan (if table is a heap) Serial table scan |
Clustered index partition scan Serial index scan |
Nonclustered index hash-based scan Serial index scan |
Unpartitioned Table |
Hash-based table scan (if table is a heap) Serial table scan |
Serial index scan |
Nonclustered index hash-based scan Serial index scan |
The optimizer may further eliminate parallel access methods from consideration, based on the number of worker processes that are available to the query. This process of elimination occurs when the optimizer computes the degree of parallelism for the query as a whole.
For an example, see “Partitioned heap table”.
