RID JOIN operator

The RID JOIN operator is a binary operator that joins two data streams, based on row IDs generated for the same source table. Each data row in a SQL table is associated with a unique row ID (RID). Think of a rid-join as a special case of a self-join query. The left child fills a worktable with the set of uniquely qualifying RIDs. The RIDs are the result of applying a distinct filter to the RIDs returned from two or more disparate index cases of the same source table.

The RID JOIN operator is used to implement the general or strategy. The general-or strategy is often used when a query’s predicate contains a collection of disjunctions that can be qualified by different indexes on the same table. In this case, each index is scanned based on the predicates that can be qualified by that index. For each index row that qualifies, a RID is returned.

The returned RIDs are processed for uniqueness so that the same row is not returned twice, which might happen if two or more of the disjunctions qualify the same row.

The RID JOIN operator inserts the unique RIDs into a worktable. The worktable of unique RIDs is passed to the scan operator in the rid-join’s right branch. The access methods can iteratively fetch the next RID to be processed directly from the worktable, and look up the associated row. This row is then returned to the RID JOIN parent operator.

The RID JOIN operator displays this message:

 Using Worktable <N> for internal storage. 

This worktable is used to store the unique RIDs generated from the left child.

The following example demonstrates the showplan output for the RID JOIN operator.

select * from tab1 a where a.c1 = 10 or a.c3 = 10

QUERY PLAN FOR STATEMENT 1 (at line 2).

  STEP 1
The type of query is SELECT.

6 operator(s) under root.

  |ROOT:EMIT Operator (VA = 6)
  |
  |  |RID JOIN Operator (VA = 5)
  |  | Using Worktable2 for internal storage.
  |  |
  |  |  |HASH UNION Operator (VA = 6) has 2 children.
  |  |  | Key Count: 1
  |  |  |
  |  |  |  |SCAN Operator (VA = 0)
  |  |  |  | FROM TABLE
  |  |  |  | tab1
  |  |  |  | a
  |  |  |  | Index:tab1idx
  |  |  |  | Forward Scan.
  |  |  |  | Positioning by key.
  |  |  |  | Index contains all needed columns. Base table will not be read.
  |  |  |  | Keys are:
  |  |  |  | c1 ASC
  |  |  |  | Using I/O Size 2 Kbytes for index leaf pages.
  |  |  |  | With LRU Buffer Replacement Strategy for index leaf pages.
  |  |  |
  |  |  |  |SCAN Operator (VA = 4)
  |  |  |  | FROM TABLE
  |  |  |  | tab1
  |  |  |  | a
  |  |  |  | Index:tab1idx2
  |  |  |  | Forward Scan.
  |  |  |  | Positioning by key.
  |  |  |  | Index contains all needed columns. Base table will not be read.
  |  |  |  | Keys are:
  |  |  |  |  c3 ASC
  |  |  |  | Using I/O Size 2 Kbytes for index leaf pages.
  |  |  |  | With LRU Buffer Replacement Strategy for index leaf pages.
  |  |
  |  |  |RESTRICT Operator (VA = 3)
  |  |  |
  |  |  |  |SCAN Operator (VA = 2)
  |  |  |  | FROM TABLE
  |  |  |  | tab1
  |  |  |  | a
  |  |  |  | Using Dynamic Index.
  |  |  |  | Forward Scan.
  |  |  |  | Positioning by Row IDentifier (RID).
  |  |  |  | Using I/O Size 2 Kbytes for data pages.
  |  |  |  | With LRU Buffer Replacement Strategy for data pages.

In this example, the index tab1idx is scanned to get all RIDs from tab1 that have a c1 value of 10. Adaptive Server scans tab1idx2 to get all RIDs from tab1 that have a c3 value of 10.

The HASH UNION operator is used to eliminate duplicate RIDs. There are duplicate RIDs for any tab1 rows where both c1 and c3 rows have a value of 10.

The RID JOIN operator inserts all of the returned rows into Worktable2. Worktable2 is passed to the scan of tab1 after it has been completely filled. The access methods fetch the first RID, look up the associated row, and return it to the RID JOIN operator. On subsequent calls to the tab1’s scan operator, the access methods fetch the next RID to be processed and return its associated row.