SQLFILTER operator

The SQLFILTER operator is a nary operator that executes subqueries. Its leftmost child represents the outer query, and the other children represent query plan fragments associated with one or more subqueries.

The leftmost child generates correlation values that are substituted into the other child plans.

The SQLFILTER operator displays this message:

SQFILTER Operator has <N> children.

This example illustrates the use of SQLFILTER:

select pub_name from publishers
where pub_id =
(select distinct titles.pub_id from titles
   where publishers.pub_id = titles.pub_id
   and price > $1000)

QUERY PLAN FOR STATEMENT 1 (at line 1).
4 operator(s) under root

STEP 1
The type of query is SELECT.

4 operator(s) under root

ROOT:EMIT Operator (VA = 4)

  |SQFILTER Operator (VA = 3) has 2 children.
  |
  |  |SCAN Operator (VA = 0)
  |  |  FROM TABLE
  |  |  publishers
  |  |  Table Scan.
  |  |  Forward Scan.
  |  |  Positioning at start of table.
  |  |  Using I/O Size 8 Kbytes for data pages.
  |  |  With LRU Buffer Replacement Strategy for data pages.
  |
  | Run subquery 1 (at nesting level 1)
  |
  |  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 3)
  |
  |  Correlated Subquery
  |  Subquery under an EXPRESSION predicate.
  |
  |  |SCALAR AGGREGATE Operator (VA = 2)
  |  |  Evaluate Ungrouped ONCE-UNIQUE AGGREGATE
  |  |
  |  |  |SCAN Operator (VA = 1)
  |  |  |  FROM TABLE
  |  |  |  titles
  |  |  |  Table Scan.
  |  |  |  Forward Scan.
  |  |  |  Postitioning at start of table.
  |  |  |  Using I/O Size 8 Kbytes for data pages.
  |  |  |  With LRU Buffer Replacement Strategy for data pages.
  |
  | END OF QUERY PLAN FOR SUBQUERY 1

The SQLFILTER operator has two children in this example. The leftmost child is the query’s outer block. It is a simple scan of the publishers table. The right child is used to evaluate the query’s subquery. SQLFILTER fetch rows from the outer block. For every row from the outer block, SQLFILTER invokes the right child to evaluate the subquery. If the subquery evaluates to TRUE, a row is returned to the SQLFILTER’s parent operator.