Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?

Question

Options

• AThe optimizer always uses a plan in which the transformation is used.
• BThe optimizer uses transformation only if the cost is less than a query executing without transformation.
• CThe optimizer always generates subqueries to transform a query.
• DThe optimizer always uses bitmap indexes on the primary key column for any dimension table to transform

• A
  71% (15)
• B
  5% (1)
• C
  10% (2)
• D
  14% (3)

Explanation

The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a cost-based decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. Even if the hint is given, there is no guarantee that the transformation will take place. The optimizer only generates the subqueries if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the untransformed query is used, regardless of the hint.

No community discussion yet for this question.