Table partition scan

This example shows a query where the optimizer chooses a table partition scan over a serial table scan. The configuration and table layout are as follows:

Configuration parameter values

Parameter

Setting

max parallel degree

10 worker processes

max scan parallel degree

2 worker processes

Table layout

Table name

Useful indexes

Number of partitions

Number of pages

authors

None

5

Partition 1: 50 pages Partition 2: 70 pages Partition 3: 90 pages Partition 4: 80 pages Partition 5: 10 pages

The example query is:

select * 
    from authors 
    where au_lname < "L"

Using the logic in Table 8-2, the optimizer determines that the following access methods are available for consideration:

The optimizer does not consider a hash-based table scan for the table, since the balance of pages in the partitions is not skewed, and the upper limit to the degree of parallelism for the table, 10, is high enough to allow a partition-based scan.

The optimizer computes the cost of each access method, as follows:

The optimizer chooses to perform a table partition scan at a cost of 90 physical and logical I/Os. Because the table has 5 partitions, the optimizer chooses to use 5 worker processes. The final showplan output for this query is:

QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 5 worker
processes.
    STEP 1
        The type of query is SELECT.
        Executed in parallel by coordinating process and 5
        worker processes.
    FROM TABLE
        authors
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Executed in parallel with a 5-way partition scan.
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    Parallel network buffer merge.