Database Reference
In-Depth Information
----------------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TEMP TABLE TRANSFORMATION | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6647_1CB85C |
|* 3 | TABLE ACCESS FULL | CUSTOMERS |
| 4 | SORT ORDER BY | |
| 5 | HASH GROUP BY | |
|* 6 | HASH JOIN | |
| 7 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_1CB85C |
|* 8 | HASH JOIN | |
| 9 | TABLE ACCESS FULL | TIMES |
| 10 | VIEW | VW_ST_16AF99B7 |
| 11 | NESTED LOOPS | |
| 12 | BITMAP CONVERSION TO ROWIDS | |
| 13 | BITMAP AND | |
| 14 | BITMAP MERGE | |
| 15 | BITMAP KEY ITERATION | |
| 16 | TABLE ACCESS BY INDEX ROWID| PRODUCTS |
|* 17 | INDEX RANGE SCAN | PRODUCTS_PROD_SUBCAT_IX |
|* 18 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 19 | BITMAP MERGE | |
| 20 | BITMAP KEY ITERATION | |
| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6647_1CB85C |
|* 22 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX |
| 23 | TABLE ACCESS BY USER ROWID | SALES |
----------------------------------------------------------------------------
3 - filter("C"."CUST_YEAR_OF_BIRTH">=1970 AND "C"."CUST_YEAR_OF_BIRTH"<=1979)
6 - access("ITEM_2"="C0")
8 - access("ITEM_1"="T"."TIME_ID")
17 - access("P"."PROD_SUBCATEGORY"='Cameras')
18 - access("S"."PROD_ID"="P"."PROD_ID")
22 - access("S"."CUST_ID"="C0")
The second optimization technique is based on bitmap-join indexes. The idea is to avoid the “join” between
the dimension tables and the corresponding bitmap index on the fact tables. For this purpose, the bitmap-join
indexes must be created on the fact table and index one or several columns of the dimension tables. For example,
the following indexes are necessary to apply the restrictions
c.cust_year_of_birth BETWEEN 1970 AND 1979
and
p.prod_ subcategory = 'Cameras'
, respectively:
CREATE BITMAP INDEX sales_cust_year_of_birth_bix ON sales (c.cust_year_of_birth)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
CREATE BITMAP INDEX sales_prod_subcategory_bix ON sales (p.prod_subcategory)
FROM sales s, products p
WHERE s.prod_id = p.prod_id
