Database Reference
In-Depth Information
When the
star_transformation_enabled
initialization parameter is set to
temp_disable
, the following
execution plan is used for the sample SQL statement shown previously. This example, like the following ones, is based
on the
star_transformation.sql
script:
-------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT ORDER BY | |
| 2 | HASH GROUP BY | |
|* 3 | HASH JOIN | |
| 4 | TABLE ACCESS FULL | TIMES |
|* 5 | HASH JOIN | |
|* 6 | TABLE ACCESS FULL | CUSTOMERS |
| 7 | VIEW | VW_ST_FE4FBDB9 |
| 8 | NESTED LOOPS | |
| 9 | BITMAP CONVERSION TO ROWIDS | |
| 10 | BITMAP AND | |
| 11 | BITMAP MERGE | |
| 12 | BITMAP KEY ITERATION | |
| 13 | TABLE ACCESS BY INDEX ROWID| PRODUCTS |
|* 14 | INDEX RANGE SCAN | PRODUCTS_PROD_SUBCAT_IX |
|* 15 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 16 | BITMAP MERGE | |
| 17 | BITMAP KEY ITERATION | |
|* 18 | TABLE ACCESS FULL | CUSTOMERS |
|* 19 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX |
| 20 | TABLE ACCESS BY USER ROWID | SALES |
-------------------------------------------------------------------------
3 - access("ITEM_1"="T"."TIME_ID")
5 - access("ITEM_2"="C"."CUST_ID")
6 - filter("C"."CUST_YEAR_OF_BIRTH">=1970 AND "C"."CUST_YEAR_OF_BIRTH"<=1979)
14 - access("P"."PROD_SUBCATEGORY"='Cameras')
15 - access("S"."PROD_ID"="P"."PROD_ID")
18 - filter("C"."CUST_YEAR_OF_BIRTH">=1970 AND "C"."CUST_YEAR_OF_BIRTH"<=1979)
19 - access("S"."CUST_ID"="C"."CUST_ID")
Because this execution plan contains some peculiar operations, let's take a detailed look at its operation:
1.
The execution starts with operation 4, the full scan of the
times
dimension table. With the
data returned from it, a hash table is built by operation 3, the hash join.
2.
Operation 6 does a full scan of the
customers
dimension table and applies the
c.cust_year_of_birth BETWEEN 1970 AND 1979
restriction. With the data returned
from it, a hash table is built by operation 5, the hash join.
3.
Operations 13 and 14 access the
products
dimension table and apply the
p.prod_subcategory='Cameras'
restriction.
4.
Operation 12,
BITMAP KEY ITERATION
, is a related-combine operation. For each row
returned by its first child (operation 13), the second child (operation 15) is executed once.
