Merge join messages

Merge join (outer table).

Merge join (inner table).

Merge join messages indicate the use of a merge join and the table’s position (inner or outer) with respect to the other table in the merge join. Merge join messages appear immediately after the table name in the

FROM TABLE

output. This query performs a mixture of merge and nested-loop joins:

select pub_name, au_lname, price
from titles t, authors a, titleauthor ta,
            publishers p
where t.title_id = ta.title_id
    and a.au_id = ta.au_id
    and p.pub_id = t.pub_id
    and type = ’business’
    and price < $25

Messages for merge joins are printed in bold type in the showplan output:

QUERY PLAN FOR STATEMENT 1 (at line 1).
Executed in parallel by coordinating process and 3 worker processes.


    STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Executed in parallel by coordinating process and 3 worker processes.

        FROM TABLE
            titles
            t
        Merge join (outer table).
        Parallel data merge using 3 worker processes.
        Using Clustered Index.
        Index : title_id_ix
        Forward scan.
        Positioning by key.
        Keys are:
            title_id  ASC
        Using I/O Size 16 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            titleauthor
            ta
        Merge join (inner table).
        Index : ta_ix
        Forward scan.
        Positioning by key.
        Index contains all needed columns. Base table will not be read.
        Keys are:
            title_id  ASC
        Using I/O Size 16 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.

        FROM TABLE
            authors
            a
        Nested iteration.
        Index : au_id_ix
        Forward scan.
        Positioning by key.
        Keys are:
            au_id  ASC
        Using I/O Size 2 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable1.
        Worktable1 created for sort merge join.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Executed by coordinating process.

        FROM TABLE
            publishers
            p
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            Worktable2.
        Worktable2 created for sort merge join.

    STEP 3
        The type of query is SELECT.
        Executed by coordinating process.

        FROM TABLE
            Worktable1.
        Merge join (outer table).
        Serial data merge.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable2.
        Merge join (inner table).
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

Total estimated I/O cost for statement 1 (at line 1): 4423.

The sort for Worktable1 is done in Serial

The sort for Worktable2 is done in Serial

This query performed the following joins:

A full-merge join on titles and titleauthor, with titles as the outer table
A nested-loop join with the authors table
A sort-merge join with the publishers table