Databases Reference
In-Depth Information
See the following "star query," which notes a query against a star schema that uses star
transformation. This can be verified by running an explain plan on your query.
SQL> show parameter star_transformation
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled string TRUE
SELECT pr.prod_category, c.country_id,
t.calendar_year, sum(s.quantity_sold), SUM(s.amount_sold)
FROM sales s, times t, customers c, products pr
WHERE s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND pr.prod_id = s.prod_id
AND t.calendar_year = '2011'
GROUP BY pr.prod_category, c.country_id, t.calendar_year;
-----------------------------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | HASH GROUP BY | |
| 2 | HASH JOIN | |
| 3 | HASH JOIN | |
| 4 | HASH JOIN | |
| 5 | PARTITION RANGE ALL | |
| 6 | TABLE ACCESS BY LOCAL INDEX ROWID| SALES |
| 7 | BITMAP CONVERSION TO ROWIDS | |
| 8 | BITMAP AND | |
| 9 | BITMAP MERGE | |
| 10 | BITMAP KEY ITERATION | |
| 11 | BUFFER SORT | |
| 12 | TABLE ACCESS FULL | CUSTOMERS |
| 13 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX |
| 14 | BITMAP MERGE | |
| 15 | BITMAP KEY ITERATION | |
| 16 | BUFFER SORT | |
| 17 | VIEW | index$_join$_016 |
| 18 | HASH JOIN | |
| 19 | INDEX FAST FULL SCAN | PRODUCTS_PK |
| 20 | INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX |
| 21 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX |
| 22 | TABLE ACCESS FULL | TIMES |
| 23 | TABLE ACCESS FULL | CUSTOMERS |
| 24 | VIEW | index$_join$_004 |
| 25 | HASH JOIN | |
| 26 | INDEX FAST FULL SCAN | PRODUCTS_PK |
| 27 | INDEX FAST FULL SCAN | PRODUCTS_PROD_CAT_IX |
-----------------------------------------------------------------------
Note
