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:

Partition scan
Serial table scan

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.