You can change a subquery attachment only for correlated subqueries that cannot be flattened. Changing the subquery attachment reduces the number of times a subquery gets evaluated.
For example, in the three-table join shown below, only the skeletal plan output shows that the subquery is attached after the join of the three outer tables is performed. This is highlighted in the showplan output.
1> select count(*) 2> from lineitem, part PO, customer 3> where l_partkey = p_partkey and l_custkey = c_custkey 4> and p_cost = (select min(PI.p_cost) from part PI where PO.p_partkey = PI.p_partkey) 5> go The Abstract Plan (AP) of the final query execution plan: ( scalar_agg ( nested ( m_join ( sort ( m_join ( sort ( t_scan customer ) ) ( sort (t_scan lineitem ) ) ) ) ( i_scan part_indx (table (PO part ) ) ) ( subq ( scalar_agg ( t_scan (table (PI part ) ) ) ) ) ) ) (prop customer ( parallel 1 ) ( prefetch 2 ) ( lru ) ) ( prop (table (PO part)) ( parallel 1 ) (prefetch 2 ) ( lru ) ) (prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) ) ( prop (table(PI part)) ( parallel 1 ) (prefetch 2 ) ( lru ) )
You can modify the abstract plan to change the query processor’s behavior, and pass the query plan to the query processor using the PLAN clause. The syntax is:
SELECT/INSERT/DELETE/UPDATE ...PLAN '( ... )
The new plan is:
QUERY PLAN FOR STATEMENT 1 (at line 1). 12 operator(s) under root The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped COUNT AGGREGATE. | | |SQFILTER Operator has 2 children. | | |MERGE JOIN Operator (Join Type: Inner Join) | | | | | | |SORT Operator | | | | Using Worktable4 for internal storage. | | | | | | | | |MERGE JOIN Operator (Join Type: Inner Join) | | | | | | | | | | |SORT Operator | | | | | | Using Worktable1 for internal storage. | | | | | | | | | | | | |SCAN Operator | | | | | | | FROM TABLE | | | | | | | customer | | | | | | | Table Scan. | | | | | | SORT Operator | | | | | | Using Worktable2 for internal storage. | | | | | | | | | | | | |SCAN Operator | | | | | | | FROM TABLE | | | | | | | lineitem | | | | | | | Table Scan. | | | |SCAN Operator | | | | FROM TABLE | | | | part | | | | Run subquery 1 (at nesting level 1). | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 4). | | | | Correlated Subquery. | | Subquery under an EXPRESSION predicate. | | | | |SCALAR AGGREGATE Operator | | | Evaluate Ungrouped MINIMUM AGGREGATE. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | part | | | | END OF QUERY PLAN FOR SUBQUERY 1.
You can view the operator tree with trace flag 526 or set statistics plancost. Trace flag 526 displays the operator tree without the cost (in the example below). However, knowing the cost is useful for determining whether the abstract plan you are forcing is efficient, so Sybase recommends that you use set statistics plancost on . In this example, note the location of the aggregate on PI.
==================== Lava Operator Tree ==================== Emit (VA = 12) / ScalarAgg Count (VA = 11) / SQFilter (VA = 10) / \ MergeJoin ScalarAgg Inner Join Min (VA = 7) (VA = 9) / \ / Sort TableScan TableScan (VA = 5) part(PO) part(PI) (VA = 6) (VA = 8) / MergeJoin Inner Join (VA = 4) / \ Sort Sort (VA = 1) (VA = 3) / / TableScan TableScan customer lineitem (VA = 0) (VA = 2)
This query plan may not be optimal. The subquery is dependent
on the outer table part (PO),
and can be attached anywhere after this table has been scanned.
This example assumes that the correct join order must be part (PO) as the outer-most table, followed by lineitem and then customer as the innermost table. If you need to attach the subquery to the scan of table PO, start with the abstract plan produced in the previous example and then modify the query as necessary:
select count(*) from lineitem, part PO, customer where l_partkey = p_partkey and l_custkey = c_custkey and p_cost = (select min(PI.p_cost) from part PI where PO.p_partkey = PI.p_partkey) plan "(scalar_agg (m_join (sort (m_join (nested (scan (table (PO part))) (subq (scalar_agg (scan (table (PI part))))) ) (sort (scan lineitem) ) ) ) (sort (scan customer) ) ) ) (prop customer ( parallel 1 ) ( prefetch 2 ) ( lru ) ) (prop (table (PO part)) ( parallel 1 ) (prefetch 2 ) ( lru ) ) (prop lineitem ( parallel 1 ) ( prefetch 2 ) ( lru ) ) (prop (table(PI part)) ( parallel 1 ) (prefetch 2 ) ( lru ) ) go ==================== Lava Operator Tree ==================== Emit (VA = 12) / ScalarAgg Count (VA = 11) / MergeJoin Inner Join (VA = 10) / \ Sort Sort (VA = 7) (VA = 9) / / MergeJoin TableScan Inner Join customer (VA = 6) (VA = 8) / \ SQFilter Sort (VA = 3) (VA = 5) / \ / TableScan ScalarAgg TableScan part(PO) Min lineitem (VA = 0) (VA = 2) (VA = 4) / TableScan part (PI) (VA = 1) ============================================================ The type of query is SELECT. ROOT:EMIT Operator |SCALAR AGGREGATE Operator | Evaluate Ungrouped COUNT AGGREGATE. | |MERGE JOIN Operator (Join Type: Inner Join) | | Using Worktable5 for internal storage. | | Key Count: 1 | | Key Ordering: ASC | | | | |SORT Operator | | | Using Worktable3 for internal storage. | | | | | | |MERGE JOIN Operator (Join Type: Inner Join) | | | | Using Worktable2 for internal storage. | | | | Key Count: 1 | | | | Key Ordering: ASC | | | | | | | | |SQFILTER Operator has 2 children. | | | | | | | | | | |SCAN Operator | | | | | | FROM TABLE | | | | | | part | | | | | | PO | | | | | | Table Scan. | | | | | | Forward Scan. | | | | | | | | | | Run subquery 1 (at nesting level 1). | | | | | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 4). | | | | | | | | | | Correlated Subquery. | | | | | Subquery under an EXPRESSION predicate. | | | | | | | | | | |SCALAR AGGREGATE Operator | | | | | | Evaluate Ungrouped MINIMUM AGGREGATE. | | | | | | | | | | | |SCAN Operator | | | | | | FROM TABLE | | | | | | part | | | | | | PI | | | | | | Table Scan. | | | | | | Forward Scan. | | | | | END OF QUERY PLAN FOR SUBQUERY 1. | | | | | | | | |SORT Operator | | | | | Using Worktable1 for internal storage. | | | | | | | | | | |SCAN Operator | | | | | | FROM TABLE | | | | | | lineitem | | | | | | Table Scan. | | | | | | Forward Scan. | | |SORT Operator | | | Using Worktable4 for internal storage. | | | | | | |SCAN Operator | | | | FROM TABLE | | | | customer | | | | Table Scan. | | | | Forward Scan.
This example shifts ScalarAgg and TableScan compared to its placement in the subquery in the previous example.
