Optimizing Joins - Troubleshooting Oracle Performance - page 559

In practice, partial partition-wise joins don't necessarily lead to improved performance. In fact, regular joins
might be faster than partial partition-wise joins. Because using partial partition-wise joins can be detrimental to
performance, you'll seldom see the query optimizer using this optimization technique.

Star Transformation
The star transformation is an optimization technique used with star schemas (also known as dimensional models).
This type of schema is composed of one large central table, the fact table , and of several other tables, the dimension
tables . Its main characteristic is that the fact table references the dimension tables. Figure 14-17 is an example based
on the sample schema SH (the Sample Schemas manual describes this fully).

Figure 14-17. A typical star schema

■ Star transformation is only available in enterprise edition. To take advantage of a similar optimization
technique in Standard edition, you have to rewrite the query yourself. at the end of this section I provide an example of
such a rewrite.

Note

The following is a typical query executed against a star schema (notice that no restrictions are applied to the fact
table, but only on the dimension tables):

SELECT c.cust_state_province, t.fiscal_month_name, sum(s.amount_sold) AS amount_sold
FROM sales s, customers c, times t, products p
WHERE s.cust_id = c.cust_id
AND s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND c.cust_year_of_birth BETWEEN 1970 AND 1979
AND p.prod_subcategory = 'Cameras'
GROUP BY c.cust_state_province, t.fiscal_month_name
ORDER BY c.cust_state_province, sum(s.amount_sold) DESC