The sqfilter 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 sqfilter operator displays this message:
SQFILTER Operator has <N> children.
This example illustrates the use of sqfilter.
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 The type of query is SELECT. ROOT:EMIT Operator |SQFILTER Operator has 2 children. | | |SCAN Operator | | 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 | | Evaluate Ungrouped ONCE-UNIQUE AGGREGATE | | | | |SCAN Operator | | | 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 sqfilter operator has 2 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. The sqfilter will fetch rows from the outer block. For every row from the outer block, it will invoke the right child to evaluate the subquery. If the subquery evaluates to TRUE, then a row will be returned to the sqfilter’s parent operator.
