SYNTACTIC_JOIN
Enforces join order and enables other join hints.
Syntax
/*+ SYN[TACTIC]_JOIN */
Description
In order to achieve optimal performance, the optimizer often overrides a query's specified join order. By including the SYNTACTIC_JOIN
hint, you can ensure that the optimizer enforces the query's join order exactly as specified. One requirement applies: the join syntax must conform with ANSI SQL-92 conventions.
The SYNTACTIC_JOIN
hint must immediately follow SELECT
. If the annotated query includes another hint that must also follow SELECT
, such as VERBATIM
, combine the two hints together. For example:
SELECT /*+ syntactic_join,verbatim */ ...
Examples
In the following examples, the optimizer produces different plans for two queries that differ only by including or excluding the SYNTACTIC_JOIN
hint.
Excludes SYNTACTIC_JOIN
EXPLAIN SELECT sales.store_key, stores.store_name, products.product_description, sales.sales_quantity, sales.sale_date FROM (store.store_sales sales JOIN products ON sales.product_key=products.product_key) JOIN store.store_dimension stores ON sales.store_key=stores.store_key WHERE sales.sale_date='2014-12-01' order by sales.store_key, sales.sale_date; Access Path: +-SORT [Cost: 14K, Rows: 100K (NO STATISTICS)] (PATH ID: 1) | Order: sales.store_key ASC, sales.sale_date ASC | Execute on: All Nodes | +---> JOIN HASH [Cost: 11K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) Outer (RESEGMENT)(LOCAL ROUND ROBIN) Inner (RESEGMENT) | | Join Cond: (sales.product_key = products.product_key) | | Materialize at Input: sales.store_key, sales.product_key, sales.sale_date, sales.sales_quantity | | Execute on: All Nodes | | +-- Outer -> JOIN HASH [Cost: 1K, Rows: 100K (NO STATISTICS)] (PATH ID: 3) | | | Join Cond: (sales.store_key = stores.store_key) | | | Execute on: All Nodes | | | +-- Outer -> STORAGE ACCESS for sales [Cost: 1K, Rows: 100K (NO STATISTICS)] (PATH ID: 4) | | | | Projection: store.store_sales_b0 | | | | Materialize: sales.store_key | | | | Filter: (sales.sale_date = '2014-12-01'::date) | | | | Execute on: All Nodes | | | | Runtime Filter: (SIP1(HashJoin): sales.store_key) | | | +-- Inner -> STORAGE ACCESS for stores [Cost: 34, Rows: 250] (PATH ID: 5) | | | | Projection: store.store_dimension_DBD_10_rep_VMartDesign_node0001 | | | | Materialize: stores.store_key, stores.store_name | | | | Execute on: All Nodes | | +-- Inner -> STORAGE ACCESS for products [Cost: 3K, Rows: 60K (NO STATISTICS)] (PATH ID: 6) | | | Projection: public.products_b0 | | | Materialize: products.product_key, products.product_description | | | Execute on: All Nodes
Includes SYNTACTIC_JOIN
EXPLAIN SELECT /*+SYNTACTIC_JOIN*/ sales.store_key, stores.store_name, products.product_description, sales.sales_quantity, sales.sale_date FROM (store.store_sales sales JOIN products ON sales.product_key=products.product_key) JOIN store.store_dimension stores ON sales.store_key=stores.store_key WHERE sales.sale_date='2014-12-01' order by sales.store_key, sales.sale_date; Access Path: +-SORT [Cost: 11K, Rows: 100K (NO STATISTICS)] (PATH ID: 1) | Order: sales.store_key ASC, sales.sale_date ASC | Execute on: All Nodes | +---> JOIN HASH [Cost: 8K, Rows: 100K (NO STATISTICS)] (PATH ID: 2) | | Join Cond: (sales.store_key = stores.store_key) | | Execute on: All Nodes | | +-- Outer -> JOIN HASH [Cost: 7K, Rows: 100K (NO STATISTICS)] (PATH ID: 3) Outer (BROADCAST)(LOCAL ROUND ROBIN) | | | Join Cond: (sales.product_key = products.product_key) | | | Execute on: All Nodes | | | Runtime Filter: (SIP1(HashJoin): sales.store_key) | | | +-- Outer -> STORAGE ACCESS for sales [Cost: 2K, Rows: 100K (NO STATISTICS)] (PATH ID: 4) | | | | Projection: store.store_sales_b0 | | | | Materialize: sales.sale_date, sales.store_key, sales.product_key, sales.sales_quantity | | | | Filter: (sales.sale_date = '2014-12-01'::date) | | | | Execute on: All Nodes | | | +-- Inner -> STORAGE ACCESS for products [Cost: 3K, Rows: 60K (NO STATISTICS)] (PATH ID: 5) | | | | Projection: public.products_b0 | | | | Materialize: products.product_key, products.product_description | | | | Execute on: All Nodes | | +-- Inner -> STORAGE ACCESS for stores [Cost: 34, Rows: 250] (PATH ID: 6) | | | Projection: store.store_dimension_DBD_10_rep_VMartDesign_node0001 | | | Materialize: stores.store_key, stores.store_name | | | Execute on: All Nodes