Selecting parallel access methods

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.

Table 25-2: Determining applicable partition or hash-based access methods

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”.