Database Reference
In-Depth Information
With these two indexes in place, the following execution plan results. Note that the method used to produce the
rowids (lines 8 to 12) is much more straightforward than the one used in the previous examples. Actually, instead of
accessing the dimension tables and joining them to the bitmap indexes on the fact table, it's enough to access the
bitmap-join indexes. This is possible because the value of the associated dimension row is already present in the
bitmap-join index of the fact table:
-------------------------------------------------------------------------
| 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 | TABLE ACCESS BY INDEX ROWID | SALES |
| 8 | BITMAP CONVERSION TO ROWIDS| |
| 9 | BITMAP AND | |
|* 10 | BITMAP INDEX SINGLE VALUE| SALES_PROD_SUBCATEGORY_BIX |
| 11 | BITMAP MERGE | |
|* 12 | BITMAP INDEX RANGE SCAN | SALES_CUST_YEAR_OF_BIRTH_BIX |
-------------------------------------------------------------------------
3 - access("S"."TIME_ID"="T"."TIME_ID")
5 - access("S"."CUST_ID"="C"."CUST_ID")
6 - filter("C"."CUST_YEAR_OF_BIRTH">=1970 AND "C"."CUST_YEAR_OF_BIRTH"<=1979)
10 - access("S"."SYS_NC00009$"='Cameras')
12 - access("S"."SYS_NC00008$">=1970 AND "S"."SYS_NC00008$"<=1979)
The star transformation is a cost-based query transformation. Therefore, when enabled, the query optimizer
decides not only whether it makes sense to use a star transformation, but also whether temporary tables and/or
bitmap-join indexes are useful for efficient SQL statement execution. The utilization of this feature can also be
controlled with the
star_transformation
and
no_star_transformation
hints.
If you're working in Standard Edition, neither the star transformation nor bitmap indexes are available. In that
case, to have decent performance you might want to rewrite the query yourself. Even though, as the following example
shows, the rewritten query is much less readable, the execution plan is quite similar:
SELECT c.cust_state_province, t.fiscal_month_name, sum(s.amount_sold) AS amount_sold
FROM (SELECT *
FROM sales
WHERE rowid IN (SELECT c.rid
FROM (SELECT s.rowid AS rid
FROM customers c, sales s
WHERE c.cust_id = s.cust_id
AND c.cust_year_of_birth BETWEEN 1970 AND 1979) c,
(SELECT s.rowid AS rid
FROM products p, sales s
WHERE p.prod_id = s.prod_id
AND p.prod_subcategory = 'Cameras') p
WHERE c.rid = p.rid)) s,
