Free Trial

Safari Books Online is a digital library providing on-demand subscription access to thousands of learning resources.

  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL
Help

Other Oracle Database 11g Features > Star Transformation

Star Transformation

A star transformation is another type of query rewrite performed by the Oracle optimizer. The star transformation is performed as a way to significantly improve the performance of queries directed against a standard star schema, which includes a fact table and a large number of dimensions.

To understand how a star transformation works, consider the following query, typical in a data warehouse scenario:

Select SUM(quantity_sold)
  From Sales s, Customers c, Products p, Times t
Where s.cust_id = c.cust_id
And     s.prod_id = p.prod_id
And     s.time_id = t.time_id
And     c.cust_city ='BOSTON'
And     p.product ='UMBRELLA'
And     t.month    ='MAY'
And     t.year        = 2008;

Normally, the optimizer would start out by looking to perform a four-way join in the most optimal way, between the SALES, CUSTOMERS, PRODUCTS, and TIMES tables. Although the dimension tables are much smaller than the SALES fact table, performing a multiple-way join can still be costly, especially since the point of the join is simply to use the Cartesian product of the dimensional join to identify the rows in the fact table for the SUM operation.


  

You are currently reading a PREVIEW of this book.

                                                                                                                    

Get instant access to over $1 million worth of books and videos.

  

Start a Free Trial


  
  • Safari Books Online
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint