Reformatting Message

WorktableN Created for REFORMATTING.

When joining two or more tables, Adaptive Server may choose to use a reformatting strategy to join the tables when the tables are large and the tables in the join do not have a useful index.

The reformatting strategy:

Inserts the needed columns from qualifying rows of the smaller of the two tables into a worktable.
Creates a clustered index on the join column(s) of the worktable. The index is built using keys to join the worktable to the other table in the query.
Uses the clustered index in the join to retrieve the qualifying rows from the table.

See Performance and Tuning Guide: Optimizing and Abstract Plans for more information on reformatting.

The following example illustrates the reformatting strategy. It performs a three-way join on the titles, titleauthor, and titles tables. There are no indexes on the join columns in the tables (au_id and title_id), so Adaptive Server uses the reformatting strategy on two of the tables:

select au_lname, title
from authors a, titleauthor ta, titles t
where a.au_id = ta.au_id
and t.title_id = ta.title_id

QUERY PLAN FOR STATEMENT 1 (at line 1).


STEP 1
        The type of query is INSERT.
        The update mode is direct.
        Worktable1 created for REFORMATTING.

        FROM TABLE
            titleauthor
            ta
        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
            Worktable1.

    STEP 2
        The type of query is INSERT.
        The update mode is direct.
        Worktable2 created for REFORMATTING.

        FROM TABLE
            authors
            a
        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.

    STEP 3
        The type of query is SELECT.

        FROM TABLE
            titles
            t
        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.

        FROM TABLE
            Worktable1.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

        FROM TABLE
            Worktable2.
        Nested iteration.
        Using Clustered Index.
        Forward scan.
        Positioning by key.
        Using I/O Size 2 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.

This query was run with set sort_merge off. When sort-merge joins are enabled, this query chooses a sort-merge join instead.