Database Reference
In-Depth Information
Operation 11, BITMAP MERGE , merges the bitmaps passed to it by its child operation. This
operation is necessary because one index key from a bitmap index might cover only part of
the indexed table.
5.
Operations 16 to 19 process the customers dimension table in the same way as the
products dimension table is processed by operations 11 to 15. In fact, every dimension
that a restriction is applied to is processed in the same way.
6.
Operation 10, BITMAP AND , combines the bitmaps passed from its two child operations
(11 and 16) and keeps only the matching entries.
7.
Operation 9, BITMAP CONVERSION TO ROWIDS , converts the bitmaps passed from its child
operation (10) in rowids of the sales fact table.
8.
9.
Operation 20, because of the nested loops join (operation 8) accesses the fact table with
the rowids generated by operation 9.
10.
Operation 7 is merely informative, telling you that the query block contains an
unmergeable view resulting from a star transformation (notice the VW_ST prefix as a view
name). This operation is available only from version 11.2.0.2 onward.
11.
With the rows returned by operation 8, the hash tables of the two hash joins
(operations 3 and 5) are probed. If matching rows are found, they're passed to operation 2.
Operation 2, HASH GROUP BY , processes the GROUP BY clause and passes the resulting rows
to operation 1.
12.
Finally, operation 1, SORT ORDER BY , processes the ORDER BY clause.
13.
In summary, the following steps are performed to execute a star transformation:
1.
The dimension tables are “joined” to the corresponding bitmap index on the fact table.
This operation is necessary only for the dimension tables that have restrictions applied to
them—in this case, the products and customers tables.
2.
The resulting bitmaps are merged and converted to rowids. Then the fact table is accessed
through the rowids.
3.
The dimension tables are joined to the data selected from the fact table. This operation
is necessary only for the dimensions that have columns referenced outside the WHERE
clause—in this case, for the times and customers tables. This is why the customers table
appears twice in the execution plan.
You can apply two additional optimization techniques to this basic behavior: temporary tables and
bitmap-join indexes.
The purpose of temporary tables is to avoid the double processing of dimension tables. For example, in the
previous execution plan, not only is the customers dimension table accessed twice with a full scan (operations 6 and 18),
but the predicate applied to it is also executed twice. The idea is to access each dimension only once, apply the
predicates, and store the resulting row in a temporary table. This optimization technique is enabled when the
star_transformation_enabled initialization parameter is set to TRUE . The following execution plan, which is
based on the same SQL statement as before, is an example. Notice the creation of the sys_temp_0fd9d6647_1cb85c
temporary table (operations from 1 to 3) and its utilization (operations 7 and 21):
 
Search WWH ::




Custom Search