Home » Oracle » 1z0-117 » Which are the two prerequisites for enabling star transformation on queries?
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.
Correct Answer: AE
Explanation/Reference:
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
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
