The use fact_table abstract plan hint allows the query processor to chose a parallel hash join plan for the star join query. Parallel plans enable the query processor to push bloom filter probings (which allow for faster joins between dimension and fact tables) below the EXCHANGE operator, further reducing the number of qualifying rows from fact tables.
Versions of SAP ASE earlier than 16.0 included the bloom filter above the EXCHANGE operator in sp_showplan output.
This example includes a parallel plan under the hint, which includes table F as the hinted central fact table:
QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using Parallel ModeExecuted in parallel by coordinating process and 66 worker processes. STEP 1 The type of query is SELECT. 29 operator(s) under root |ROOT:EMIT Operator (VA = 29) | | |HASH VECTOR AGGREGATE Operator (VA = 28) | | GROUP BY | | Evaluate Grouped COUNT AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Using Worktable10 for internal storage. | | Key Count: 3 | | | | |HASH JOIN PROBE Operator (VA = 27) (Join Type: Inner Join) | | | Using Worktable1 for internal storage. | | | Key Count: 1 | | | | | | |HASH JOIN BUILD Operator (VA = 1) (Join Type: Inner Join) | | | | Using Worktable1 for internal storage. | | | | Key Count: 1 | | | | | | | | |SCAN Operator (VA = 0) | | | | | FROM TABLE | | | | | /B49/DBENCH01P | | | | | DP | | | | | Index : /B49/DBENCH01P~010 | | | | | Forward Scan. | | | | | Positioning by key. | | | | | Keys are: | | | | | SID_0CHNGID ASC | | | | | SID_0RECORDTP ASC | | | | | SID_0REQUID ASC | | | | | Using I/O Size 16 Kbytes for index leaf pages. | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | Using I/O Size 16 Kbytes for data pages. | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | |HASH JOIN PROBE Operator (VA = 26) (Join Type: Inner Join) | | | | Using Worktable2 for internal storage. | | | | Key Count: 1 | | | | | | | | |HASH JOIN BUILD Operator (VA = 3) (Join Type: Inner Join) | | | | | Using Worktable2 for internal storage. | | | | | Key Count: 1 | | | | | | | | | | |SCAN Operator (VA = 2) | | | | | | FROM TABLE | | | | | | /B49/DBENCH01U | | | | | | DU | | | | | | Index : "/B49/DBENCH01U~020" | | | | | | Forward Scan. | | | | | | Positioning at index start. | | | | | | Using I/O Size 16 Kbytes for index leaf pages. | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | Using I/O Size 16 Kbytes for data pages. | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | |HASH JOIN PROBE Operator (VA = 25) (Join Type: Inner Join) | | | | | Using Worktable3 for internal storage. | | | | | Key Count: 2 | | | | | | | | | | |HASH JOIN BUILD Operator (VA = 5) (Join Type: Inner Join) | | | | | | Using Worktable3 for internal storage. | | | | | | Key Count: 2 | | | | | | | | | | | | |SCAN Operator (VA = 4) | | | | | | | FROM TABLE | | | | | | | /B49/DBENCH01T | | | | | | | DT | | | | | | | Index : /B49/DBENCH01T~20 | | | | | | | Forward Scan. | | | | | | | Positioning by key. | | | | | | | Keys are: | | | | | | | SID_0CALMONTH ASC | | | | | | | Using I/O Size 16 Kbytes for index leaf pages. | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | Using I/O Size 16 Kbytes for data pages. | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | |HASH JOIN PROBE Operator (VA = 24) (Join Type: Inner Join) | | | | | | Using Worktable4 for internal storage. | | | | | | Key Count: 1 | | | | | | | | | | | | |HASH JOIN BUILD Operator (VA = 7) (Join Type: Inner Join) | | | | | | | Using Worktable4 for internal storage. | | | | | | | Building pushdown bloom filter (bv7) | | | | | | | Key Count: 1 | | | | | | | | | | | | | | |SCAN Operator (VA = 6) | | | | | | | | FROM TABLE | | | | | | | | /B49/XCUSTOMER | | | | | | | | X1 | | | | | | | | Table Scan. | | | | | | | | Forward Scan. | | | | | | | | Positioning at start of table. | | | | | | | | Using I/O Size 128 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | |HASH JOIN PROBE Operator (VA = 23) (Join Type: Inner Join) | | | | | | | Using Worktable5 for internal storage. | | | | | | | Key Count: 1 | | | | | | | | | | | | | | |HASH JOIN BUILD Operator (VA = 9) (Join Type: Inner Join) | | | | | | | | Using Worktable5 for internal storage. | | | | | | | | Building pushdown bloom filter (bv9) | | | | | | | | Key Count: 1 | | | | | | | | | | | | | | | | |SCAN Operator (VA = 8) | | | | | | | | | FROM TABLE | | | | | | | | | /B49/DBENCH011 | | | | | | | | | D1 | | | | | | | | | Table Scan. | | | | | | | | | Forward Scan. | | | | | | | | | Positioning at start of table. | | | | | | | | | Using pushdown bloom filter (bv7). | | | | | | | | | Using I/O Size 128 Kbytes for data pages. | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | |EXCHANGE Operator (VA = 22) (Merged) | | | | | | | |Executed in parallel by 64 Producer and 1 Consumer processes. | | | | | | | | | | | | | | | | |EXCHANGE:EMIT Operator (VA = 21) | | | | | | | | | | | | | | | | | | |HASH JOIN PROBE Operator (VA = 20) (Join Type: Inner Join) | | | | | | | | | | Using Worktable9 for internal storage. | | | | | | | | | | Key Count: 1 | | | | | | | | | | | | | | | | | | | | |EXCHANGE Operator (VA = 13) (Replicated) | | | | | | | | | | |Executed in parallel by 1 Producer and 64 Consumer processes. | | | | | | | | | | | | | | | | | | | | | | |EXCHANGE:EMIT Operator (VA = 12) | | | | | | | | | | | | | | | | | | | | | | | | |HASH JOIN BUILD Operator (VA = 11) (Join Type: Inner Join) | | | | | | | | | | | | | Using Worktable6 for internal storage. | | | | | | | | | | | | | Building pushdown bloom filter (bv11) | | | | | | | | | | | | | Key Count: 1 | | | | | | | | | | | | | | | | | | | | | | | | | | |SCAN Operator (VA = 10) | | | | | | | | | | | | | | FROM TABLE | | | | | | | | | | | | | | /B49/SSALESORG | | | | | | | | | | | | | | S1 | | | | | | | | | | | | | | Index : "/B49/SSALESORG~0" | | | | | | | | | | | | | | Forward Scan. | | | | | | | | | | | | | | Positioning by key. | | | | | | | | | | | | | | Keys are: | | | | | | | | | | | | | | /B49/S_SALESORG ASC | | | | | | | | | | | | | | Using I/O Size 16 Kbytes for index leaf pages. | | | | | | | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | | | | | | | Using I/O Size 16 Kbytes for data pages. | | | | | | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | | | | | | | |HASH JOIN PROBE Operator (VA = 19) (Join Type: Inner Join) | | | | | | | | | | | Using Worktable8 for internal storage. | | | | | | | | | | | Key Count: 1 | | | | | | | | | | | | | | | | | | | | | | |EXCHANGE Operator (VA = 17) (Replicated) | | | | | | | | | | | |Executed in parallel by 1 Producer and 64 Consumer processes. | | | | | | | | | | | | | | | | | | | | | | | | |EXCHANGE:EMIT Operator (VA = 16) | | | | | | | | | | | | | | | | | | | | | | | | | | |HASH JOIN BUILD Operator (VA = 15) (Join Type: Inner Join) | | | | | | | | | | | | | | Using Worktable7 for internal storage. | | | | | | | | | | | | | | Building pushdown bloom filter (bv15) | | | | | | | | | | | | | | Key Count: 1 | | | | | | | | | | | | | | | | | | | | | | | | | | | | |SCAN Operator (VA = 14) | | | | | | | | | | | | | | | FROM TABLE | | | | | | | | | | | | | | | /B49/DBENCH013 | | | | | | | | | | | | | | | D3 | | | | | | | | | | | | | | | Table Scan. | | | | | | | | | | | | | | | Forward Scan. | | | | | | | | | | | | | | | Positioning at start of table. | | | | | | | | | | | | | | | Using pushdown bloom filter (bv11). | | | | | | | | | | | | | | | Using I/O Size 128 Kbytes for data pages. | | | | | | | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | | | | | | | | | |SCAN Operator (VA = 18) | | | | | | | | | | | | FROM TABLE | | | | | | | | | | | | /B49/FBENCH01 | | | | | | | | | | | | F | | | | | | | | | | | | Index : /B49/FBENCH01~060 | | | | | | | | | | | | Forward Scan. | | | | | | | | | | | | Positioning at index start. | | | | | | | | | | | | Using pushdown bloom filter (bv15, bv9). | | | | | | | | | | | | Executed in parallel with a 64-way partition scan. | | | | | | | | | | | | Using I/O Size 128 Kbytes for index leaf pages. | | | | | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | | | | | Using I/O Size 128 Kbytes for data pages. | | | | | | | | | | | | With MRU Buffer Replacement Strategy for data pages.
QUERY PLAN FOR STATEMENT 1 (at line 1). Optimized using Serial Mode STEP 1 The type of query is SELECT. 8 operator(s) under root |ROOT:EMIT Operator (VA = 8) | | |HASH VECTOR AGGREGATE Operator (VA = 7) | | GROUP BY | | Evaluate Grouped COUNT AGGREGATE. | | Evaluate Grouped SUM OR AVERAGE AGGREGATE. | | Using Worktable4 for internal storage. | | Key Count: 1 | | | | |HASH JOIN Operator (VA = 6) (Join Type: Inner Join) | | | Using Worktable3 for internal storage. | | | Building pushdown bloom filter (bv6) | | | Key Count: 1 | | | | | | |SCAN Operator (VA = 0) | | | | FROM TABLE | | | | Location | | | | L | | | | 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. | | | | | | |HASH JOIN Operator (VA = 5) (Join Type: Inner Join) | | | | Using Worktable2 for internal storage. | | | | Building pushdown bloom filter (bv5) | | | | Key Count: 1 | | | | | | | | |SCAN Operator (VA = 1) | | | | | FROM TABLE | | | | | Customer | | | | | C | | | | | 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. | | | | | | | | |HASH JOIN Operator (VA = 4) (Join Type: Inner Join) | | | | | Using Worktable1 for internal storage. | | | | | Building pushdown bloom filter (bv4) | | | | | Key Count: 1 | | | | | | | | | | |SCAN Operator (VA = 2) | | | | | | FROM TABLE | | | | | | Time | | | | | | T | | | | | | 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 = 3) | | | | | | FROM TABLE | | | | | | Orders | | | | | | O | | | | | | Table Scan. | | | | | | Forward Scan. | | | | | | Positioning at start of table. | | | | | | Using pushdown bloom filter (bv4, bv5, bv6). | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | With LRU Buffer Replacement Strategy for data pages.