Which statement is true about the usage of the STAR_TRANSFORMATION hint in a query?
A.
The optimizer always uses a plan in which the transformation is used.
B.
The optimizer uses transformation only if the cost is less than a query executing without
transformation.
C.
The optimizer always generates subqueries to transform a query.
D.
The optimizer always uses bitmap indexes on the primary key column for any dimension table
to transform a query.
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.
Reference: Oracle Database SQL Language Reference, STAR_TRANSFORMATION Hint
From the given explanation it seems that correct answer is C.
A si not correct because even if the hint is given, there is no guarantee that the transformation will take place.
D is also incorrect
I hesitate between B and C
I prefere B
https://blogs.oracle.com/optimizer/entry/star_transformation
The Oracle optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.
Also B.
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements006.htm#SQLRF50508
Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only 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.
B – correct
Ref: Database Data Warehousing Guide – Data Warehousing Optimizations and Techniques
The optimizer generates and saves the best plan it can produce without the transformation. If the transformation is enabled, the optimizer then tries to apply it to the query and, if applicable, generates the best plan using the transformed query. Based on a comparison of the cost estimates between the best plans for the two versions of the query, the optimizer then decides whether to use the best plan for the transformed or untransformed version.
A should be correct.
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 query optimization 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
The Oracle optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.
https://blogs.oracle.com/optimizer/entry/star_transformation