Which are the two prerequisites for enabling star transformation on queries?
A.
The STAR_TRANSFORMATION_ENABLED parameter should be set to TRUE or
TEMP_DISABLE.
B.
A B-tree index should be built on each of the foreign key columns of the fact table(s),
C.
A bitmap index should be built on each of the primary key columns of the fact table(s).
D.
A bitmap index should be built on each of the foreign key columns of the fact table(s).
E.
A bitmap index must exist on all the columns that are used in the filter predicates of the query.
Explanation:
A: Enabling the transformation
E: Star transformation is essentially about adding subquery predicates corresponding to the
constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates.
The transformation is performed when there are indexes on the fact join columns (s.timeid,
s.custid…). By driving bitmap AND and OR operations (bitmaps can be from bitmap indexes or
generated from regular B-Tree indexes) of the key values supplied by the subqueries, only the
relevant rows from the fact table need to be retrieved. If the filters on the dimension tables filter out
a lot of data, this can be much more efficient than a full table scan on the fact table. After the
relevant rows have been retrieved from the fact table, they may need to be joined back to the
dimension tables, using the original predicates. In some cases, the join back can be eliminated.
Star transformation is controlled by the star_transformation_enabled parameter. The parameter
takes 3 values.
TRUE – 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.
FALSE – The transformation is not tried.
TEMP_DISABLE – This value has similar behavior as TRUE except that temporary table
transformation is not tried.
The default value of the parameter is FALSE. You have to change the parameter value and create
indexes on the joining columns of the fact table to take advantage of this transformation.
Reference: Optimizer Transformations: Star Transformation
I think A,D
A,D
To get the best possible performance for star queries, it is important to follow some basic guidelines:
• A bitmap index should be built on each of the foreign key columns of the fact table or tables.
• The STAR_TRANSFORMATION_ENABLED initialization parameter should be set to TRUE.
This enables an important optimizer feature for star queries. It is set to FALSE by default for backwards compatibility.
(Oracle Database 11g: SQL Tuning Workshop Student Guide D52163GC20 Edition 2.0)
A, E are correct
if fact table’s column 1 is not used in join, we do not need index on it at all
Thanks Jun , can you share any reference for same .
A,E
To get the best possible performance for star queries, it is important to follow some basic guidelines:
• A bitmap index should be built on each of the foreign key columns of the fact table or tables.
—-
This is true but this is still a quideline to make star transformation more useful in different cases. The question is more specific and asks if it’s necessary to have all foreign key columns indexed – no it is not. E is correct.
A prerequisite of the star transformation is that there be a single-column bitmap index on every join column of the fact table. These join columns include all foreign key columns.
http://docs.oracle.com/cd/B28359_01/server.111/b28313/schemas.htm
So, propably A, E.
A,D
To get the best possible performance for star queries, it is important to follow some basic guidelines:
A bitmap index should be built on each of the foreign key columns of the fact table or tables.
The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE. This enables an important optimizer feature for star-queries. It is set to FALSE by default for backward-compatibility.
The STAR_TRANSFORMATION_ENABLED initialization parameter controls star transformations. This parameter takes the following values:
true
The optimizer performs the star transformation by identifying the fact and constraint dimension tables automatically. The optimizer performs the star transformation only if the cost of the transformed plan is lower than the alternatives. Also, the optimizer attempts temporary table transformation automatically whenever materialization improves performance (see “Temporary Table Transformation: Scenario”).
false (default)
The optimizer does not perform star transformations.
TEMP_DISABLE
This value is identical to true except that the optimizer does not attempt temporary table transformation.
Reference:
http://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG8591
http://docs.oracle.com/database/121/TGSQL/tgsql_transform.htm#TGSQL94902