DISTRIB

Specifies to the optimizer how to distribute join key data to implement a join.

Specifies to the optimizer how to distribute join key data to implement a join.

Syntax

JOIN /*+DISTRIB(outer-join, inner-join)*/

Arguments

outer-join inner-join
Specifies how to distribute data on the outer and inner joins:

  • L (local): Inner and outer join keys are identically segmented on each node, join locally.

  • R (resegment): Inner and outer join keys are not identically segmented. Resegment join-key data before implementing the join.

  • B (broadcast): Inner and outer join keys are not identically segmented. Broadcast data of this join key to other nodes before implementing the join.

  • F (filter): Join table is unsegmented. Filter data as needed by the other join key before implementing the join.

  • A (any): Let the optimizer choose the distribution method that it considers to be most cost-effective.

Description

The DISTRIB hint specifies to the optimizer how to distribute join key data in order to implement a join. If a specified distribution is not feasible, the optimizer ignores the hint and throws a warning.

The following requirements apply:

  • Queries that include the DISTRIB hint must also include the SYNTACTIC_JOIN hint. Otherwise, the optimizer ignores the DISTRIB hint and throws a warning.

  • Join syntax must conform with ANSI SQL-92 join conventions.

Examples

In the following query, the join is qualified with a DISTRIB hint of /*+DISTRIB(L,R)*/. This hint tells the optimizer to resegment data of join key stores.store_key before joining it to the sales.store_key data:

SELECT /*+SYNTACTIC_JOIN*/ sales.store_key, stores.store_name, sales.product_description, sales.sales_quantity, sales.sale_date
FROM (store.storeSales AS sales JOIN /*+DISTRIB(L,R),JTYPE(H)*/ store.store_dimension AS stores ON (sales.store_key = stores.store_key))
WHERE (sales.sale_date = '2014-12-01'::date) ORDER BY sales.store_key, sales.sale_date;