Star transformation in snowflaked schema with compound keys

January 20, 2009

This article builds on Mark Rittman’s post on explain plans for star transformation.

Every now and again I have come across claims that the CBO only uses star transformation with single part foreign keys on the fact table:

In order for a RDBMS query optimizer to execute a query using a Star Transformation, a single part foreign key with a bitmap index is required.

Generally, no explanation or proof is given for this claim.

Inspired by Mark’s excellent post I wanted to get to the bottom of this. And while I am at this I’ll also have a look at star transformation in snowflaked dimensional models. We will be using Oracle on Windows XP.

Let’s start by setting up our snowflaked star schema. We will be using the SH sample schema as a basis for this.

This will give us the following (very simple) snowflaked model:

So let’s actually run a query against our snowflake

So what conclusions can we draw from the above explain plan. Well, first of all we see that Oracle has used star transformation for this query. This demonstrates that star transformation is used by the CBO in a snowflaked dimensional model. The next question then is, how exactly did this happen

As a first step, on lines 2-7 (Id 2-7), Oracle loads a global temporary table (GTT). It expects to load 29 rows into this table from the Bitmap ANDed predicates on the customer table. It uses that GTT in the star transformation itself (lines 11-27). So rather than joining directly to the customer dimension Oracle uses the GTT as part of the star transformation. On lines 10 and 28 our GTT is hash joined to the results of the star transformation. On lines 9 and 29 our snowflaked countries_star dimension is joined to our result set and this is then finally aggregated in line 10 and returned in line 0. Interestingly, the customers_star dimension does not directly take part in a join at all.

Let’s move on to the next item in our list: Does the CBO use star transformation when it finds a compound key in both fact and dimension table?

In order to demonstrate this we will first create a compound key in our products_star dimension and also set this up as a foreign key in the sales_star fact table. We will use the prod_name in products_star as the second item in the compound key. We will also create the prod_name column in the sales_star table.

Let’s first drop the products_star.prod_id primary key:

Next we drop the Bitmap index on sales_star.prod_id and recreate it as a compund Bitmap index

We gather stats on the two tables

And rerun our query.

Et voilá, q.e.d.: star transformation used in dimensional model with compound key.

So, what does this mean now? First of all it means that the CBO can use star transformation with compound keys. Claims to the contrary are simply false. This also means that surrogate keys are not a pre-requisite for star transformation to be used in a dimensional model. So another reason to get rid of them (in most situations).

Enjoyed this post? Have a look at the other posts on our blog.
Contact us for Snowflake professional services.
We created the content in partnership with Snowflake.