Star-join directives

Star-join directives can specify how the query optimizer joins two or more tables, among which one or more dimension tables have foreign-key dependencies on one or more fact tables.

The following directives can influence the join plan for tables that logically participate in a star schema or in a snowflake schema:

• FACT

  The optimizer considers a query plan in which the specified table is a fact table in a star-join execution plan.

• AVOID_FACT

  The optimizer does not consider a star-join execution plan that treats the specified table (or any of the tables in the list of tables) as a fact table.

• STAR_JOIN

  The optimizer favors a star-join execution plan, if available.

• AVOID_STAR_JOIN

  The optimizer chooses a query execution plan that is not a star-join plan.

These star-join directives have no effect unless the parallel database query feature (PDQ) is enabled.