STORE operator

The STORE operator is used to create a worktable, fill it, and possibly create an index on it. As part of the execution of a query plan, the worktable is used by other operators in the plan. A SEQUENCER operator guarantees that the plan fragment corresponding to the worktable and potential index creation is executed before other plan fragments that use the worktable. This is important when a plan is executed in parallel, because execution processes operate asynchronously.

Reformatting strategies use the STORE operator to create a worktable with a clustered index on it.

If the STORE operator is used for a reformatting operation, it prints this message:

Worktable <X> created, in <L> locking mode for reformatting.

The locking mode <L> has to be one of “allpages,” “datapages,” or “datarows.”

The STORE operator also prints this message:

Creating clustered index.

If the STORE operator is not used for a reformatting operation, it prints this message:

Worktable <X> created, in <L> locking mode. 

The following example applies to the STORE operator, as well as to the SEQUENCER operator.

select * from bigun a, bigun 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
  |  |  |  |  | bigun
  |  |  |  |  | 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 (VA = 3)
  |  |  |  | Worktable1.
  |  |
  |  |  |NESTED LOOP JOIN (Join Type: Inner Join)(VA = 7)
  |  |  |
  |  |  |  |SCAN Operator (VA = 2)
  |  |  |  | FROM TABLE
  |  |  |  | bigun
  |  |  |  | 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 key.
  |  |  |  | Using I/O Size 2 Kbytes for data pages.
  |  |  |  | With LRU Buffer Replacement Strategy for data pages. 

In the example plan shown above, the STORE operator is used in a reformatting strategy. It is located directly below the SEQUENCER operator in the leftmost child of the SEQUENCER operator.

The STORE operator creates Worktable1, which is filled by the INSERT operator below it. The STORE operator then creates a clustered index on Worktable1. The index is built on the join key b.c4.