Star-Join Directives

Use the star-join directives to specify the manner in which the optimizer should join tables that have a star schema.

Table Reference


Element	Description	Restrictions	Syntax
alias	Temporary alternative table name declared in the FROM clause	If an alias is declared, it must be used (rather than table or synonym)	Identifier
comments	Optional text that documents the directive	Must be outside the parentheses but inside the comment symbols	Character string
synonym, table	Name or synonym of a table to which the directive applies	Synonym and the table to which it points must exist	Identifier

In AVOID_FACT directives that specify more than one table, use a comma or blank space to separate consecutive elements within the parentheses.

The following table describes each of the star-join directives and indicates how it affects the query plan of the optimizer.


Keywords	Effect	Optimizer Action
AVOID_FACT	At least one table must be specified. Do not use the table (or any table in the list of tables) as a fact table in star-join optimization.	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.
AVOID_STAR_JOIN	The optimizer does not consider a star-join execution plan.	The optimizer chooses a query execution plan that is not a star-join plan.
FACT	Exactly one table must be specified. Only consider the specified table as a fact table in the star-join execution plan.	These optimizer considers a query plan in which the specified table is a fact table in a star-join execution plan.
STAR_JOIN	Favor a star-join plan, if one is possible.	The optimizer favors a star-join execution plan, if available.

The star-join directives require that the parallel database query feature (PDQ) be enabled. Star join query optimization is disabled when PDQ is off.

The star-join directives require that all tables in the query have at least low level statistics. If table statistics are not available for any table in the query, star-join query optimization is disabled.

The SET OPTIMIZATION ENVIRONMENT STAR_JOIN DISABLED statement of SQL disables star-join optimization in the current session. (For additional information about optimization environment settings, see ENVIRONMENT Options.)

Specifying the FACT directive alone does not automatically favor a star-join execution plan. You can direct the optimizer to prefer a star-join execution plan with a specific fact table by specifying a combination of a STAR_JOIN directive and a FACT directive.

In cluster environments, the star-join optimizer directives are valid on these types of secondary servers:

Shared disk secondary servers (SDS)
Remote standalone secondary servers (RSS)
High-availability data replication secondary servers (HDR).

Restrictions on star-join directives

The following restrictions apply to queries that attempt to join tables that have star-schema dependencies:

The parallel database query (PDQ) feature must be enabled for star-join directives to be valid.
All tables in the query must have at least low level statistics.
Star-join directives do not support joins of more than one fact table.
Star-join directives are not valid while the transaction isolation level is Committed Read Last Committed or Cursor Stability. (All other transaction isolation levels are supported.)