SEQUENCER operator

The SEQUENCER operator is a nary operator used to sequentially execute each the child plans below it. The SEQUENCER operator is used in reformatting plans, and certain aggregate processing plans.

The SEQUENCER operator executes each of its child subplans, except for the rightmost one. Once all the left child subplans are executed, the rightmost subplan is executed.

The SEQUENCER operator displays this message:

SEQUENCER operator has N children.

select * from tab1 a, tab2 b where a.c4 = b.c4 and a.c2 < 10

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the Abstract Plan in the PLAN clause.

  STEP 1
    The type of query is SELECT.

    7 operator(s) under root

    |ROOT:EMIT Operator (VA = 7)
    |
    |  |SEQUENCER Operator (VA = 6) has 2 children.
    |  |
    |  |  |STORE Operator (VA = 5)
    |  |  | Worktable1 created, in allpages locking mode, for REFORMATTING.
    |  |  | Creating clustered index.
    |  |  |
    |  |  |  |INSERT Operator (VA = 4)
    |  |  |  | The update mode is direct.
    |  |  |  |
    |  |  |  |  |SCAN Operator (VA = 0)
    |  |  |  |  | FROM TABLE
    |  |  |  |  | tab2
    |  |  |  |  | b
    |  |  |  |  | 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.
    |  |
    |  |  |NESTED LOOP JOIN Operator (Join Type: Inner Join) (VA = 3)
    |  |  |
    |  |  |  |SCAN Operator (VA = 2)
    |  |  |  | FROM TABLE
    |  |  |  | tab1
    |  |  |  | a
    |  |  |  | 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.
    |  |  |
    |  |  |  |SCAN Operator (VA = 1)
    |  |  |  | FROM TABLE
    |  |  |  | Worktable1.
    |  |  |  | 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.

In this example, the SEQUENCER operator implements a reformatting strategy. The leftmost branch of the SEQUENCER operator creates a clustered index on Worktable1. This branch is executed and closed before the SEQUENCER operator proceeds to the next child operator. The SEQUENCER operator arrives at the rightmost child, opens, and begins to drain it, returning rows back to its parent operator. The design intent of the SEQUENCER operator is for operators in the rightmost branch to use the worktables created in the preceding outer branches of the SEQUENCER operator. In this example, Worktable1 is used in a nested-loop join strategy. The scan of Worktable1 is positioned by a key on its clustered index for each row that comes from the outer scan of tab1.