Star transformation in snowflaked schema with compound keys

This article builds on Mark Rittman’s recent 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 11.1.0.7 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.

SQL&gt;  create table sales_star
  2      as
  3      select * from sh.sales;

Table created.

SQL&gt;  create table customers_star
  2      as
  3      select * from sh.customers;

Table created.

SQL&gt;  create table products_star
  2      as
  3      select * from sh.products;

Table created.

SQL&gt;  create table countries_star
  2      as
  3      select * from sh.countries;

Table created.

SQL&gt;  alter table customers_star add constraint cust_star_pk primary key (cust_id);

Table altered.

SQL&gt;  alter table products_star add constraint prod_star_pk primary key (prod_id);

Table altered.

SQL&gt; alter table countries_star add constraint countries_star_pk primary key (country_id);

Table altered.

SQL&gt;  create bitmap index sales_star_cust_bix on sales_star(cust_id);

Index created.

SQL&gt;  create bitmap index sales_star_prod_bix on sales_star(prod_id);

Index created.

SQL&gt;   alter table countries_star add constraint countries_star_pk primary key (country_id);

Table altered.

SQL&gt;  create bitmap index customers_star_gender_bix on customers_star(cust_gender);

Index created.

SQL&gt;  create bitmap index customers_star_city_bix on customers_star(cust_city);

Index created.

SQL&gt;  create bitmap index products_star_subcategory_bix on products_star(prod_subcategory_desc);

Index created.

SQL&gt;  create bitmap index customers_star_country_bix on customers_star(country_id);

Index created.

SQL&gt;  analyze table sales_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL&gt;  analyze table customers_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL&gt;  analyze table products_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL&gt;  analyze table countries_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> create table sales_star

2 as

3 select * from sh.sales;

Table created.

SQL> create table customers_star

2 as

3 select * from sh.customers;

Table created.

SQL> create table products_star

2 as

3 select * from sh.products;

Table created.

SQL> create table countries_star

2 as

3 select * from sh.countries;

Table created.

SQL> alter table customers_star add constraint cust_star_pk primary key (cust_id);

Table altered.

SQL> alter table products_star add constraint prod_star_pk primary key (prod_id);

Table altered.

SQL> alter table countries_star add constraint countries_star_pk primary key (country_id);

Table altered.

SQL> create bitmap index sales_star_cust_bix on sales_star(cust_id);

Index created.

SQL> create bitmap index sales_star_prod_bix on sales_star(prod_id);

Index created.

SQL> alter table countries_star add constraint countries_star_pk primary key (country_id);

Table altered.

SQL> create bitmap index customers_star_gender_bix on customers_star(cust_gender);

Index created.

SQL> create bitmap index customers_star_city_bix on customers_star(cust_city);

Index created.

SQL> create bitmap index products_star_subcategory_bix on products_star(prod_subcategory_desc);

Index created.

SQL> create bitmap index customers_star_country_bix on customers_star(country_id);

Index created.

SQL> analyze table sales_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table customers_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table products_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

SQL> analyze table countries_star compute statistics for table for all indexes for all indexed columns;

Table analyzed.

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

So let’s actually run a query against our snowflake

SQL&gt; select
  2      sum(quantity_sold),
  3      p.prod_subcategory_desc,
  4      c.cust_gender
  5  from
  6     sales_star s
  7     join products_star p   ON (s.prod_id = p.prod_id)
  8     join  customers_star c ON (s.cust_id = c.cust_id)
  9     join countries_star d   ON (c.country_id = d.country_id)
 10  where
 11     p.prod_subcategory_desc = 'Memory' and
 12     c.cust_city = 'Oxford' and
 13     c.cust_gender = 'F'
 14  group by
 15     p.prod_subcategory_desc, c.cust_gender;


Execution Plan
----------------------------------------------------------
Plan hash value: 1638875787

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |     1 |    58 |    28   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION             |                               |       |       |            |          |
|   2 |   LOAD AS SELECT                       | SYS_TEMP_0FD9D6609_53663F     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID         | CUSTOMERS_STAR                |    29 |   783 |     9   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS        |                               |       |       |            |          |
|   5 |      BITMAP AND                        |                               |       |       |            |          |
|*  6 |       BITMAP INDEX SINGLE VALUE        | CUSTOMERS_STAR_CITY_BIX       |       |       |            |          |
|*  7 |       BITMAP INDEX SINGLE VALUE        | CUSTOMERS_STAR_GENDER_BIX     |       |       |            |          |
|   8 |   HASH GROUP BY                        |                               |     1 |    58 |    19   (6)| 00:00:01 |
|*  9 |    HASH JOIN                           |                               |     1 |    58 |    19   (6)| 00:00:01 |
|* 10 |     HASH JOIN                          |                               |     1 |    54 |    18   (6)| 00:00:01 |
|* 11 |      HASH JOIN                         |                               |     1 |    36 |    15   (0)| 00:00:01 |
|  12 |       TABLE ACCESS BY INDEX ROWID      | PRODUCTS_STAR                 |     2 |    32 |     2   (0)| 00:00:01 |
|  13 |        BITMAP CONVERSION TO ROWIDS     |                               |       |       |            |          |
|* 14 |         BITMAP INDEX SINGLE VALUE      | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|  15 |       TABLE ACCESS BY INDEX ROWID      | SALES_STAR                    |    13 |   260 |    13   (0)| 00:00:01 |
|  16 |        BITMAP CONVERSION TO ROWIDS     |                               |       |       |            |          |
|  17 |         BITMAP AND                     |                               |       |       |            |          |
|  18 |          BITMAP MERGE                  |                               |       |       |            |          |
|  19 |           BITMAP KEY ITERATION         |                               |       |       |            |          |
|  20 |            TABLE ACCESS FULL           | SYS_TEMP_0FD9D6609_53663F     |     1 |    13 |     2   (0)| 00:00:01 |
|* 21 |            BITMAP INDEX RANGE SCAN     | SALES_STAR_CUST_BIX           |       |       |            |          |
|  22 |          BITMAP MERGE                  |                               |       |       |            |          |
|  23 |           BITMAP KEY ITERATION         |                               |       |       |            |          |
|  24 |            TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR                 |     2 |    32 |     2   (0)| 00:00:01 |
|  25 |             BITMAP CONVERSION TO ROWIDS|                               |       |       |            |          |
|* 26 |              BITMAP INDEX SINGLE VALUE | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|* 27 |            BITMAP INDEX RANGE SCAN     | SALES_STAR_PROD_BIX           |       |       |            |          |
|  28 |      TABLE ACCESS FULL                 | SYS_TEMP_0FD9D6609_53663F     |    29 |   522 |     2   (0)| 00:00:01 |
|  29 |     INDEX FULL SCAN                    | COUNTRIES_STAR_PK             |    23 |    92 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Note
-----
   - star transformation used for this statement

SQL> select

2 sum(quantity_sold),

3 p.prod_subcategory_desc,

4 c.cust_gender

5 from

6 sales_star s

7 join products_star p ON (s.prod_id = p.prod_id)

8 join customers_star c ON (s.cust_id = c.cust_id)

9 join countries_star d ON (c.country_id = d.country_id)

10 where

11 p.prod_subcategory_desc = 'Memory' and

12 c.cust_city = 'Oxford' and

13 c.cust_gender = 'F'

14 group by

15 p.prod_subcategory_desc, c.cust_gender;

Execution Plan

----------------------------------------------------------

Plan hash value: 1638875787

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 58 | 28 (4)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS_STAR | 29 | 783 | 9 (0)| 00:00:01 |

| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 5 | BITMAP AND | | | | | |

| 8 | HASH GROUP BY | | 1 | 58 | 19 (6)| 00:00:01 |

|* 9 | HASH JOIN | | 1 | 58 | 19 (6)| 00:00:01 |

|* 10 | HASH JOIN | | 1 | 54 | 18 (6)| 00:00:01 |

|* 11 | HASH JOIN | | 1 | 36 | 15 (0)| 00:00:01 |

| 12 | TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR | 2 | 32 | 2 (0)| 00:00:01 |

| 13 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 15 | TABLE ACCESS BY INDEX ROWID | SALES_STAR | 13 | 260 | 13 (0)| 00:00:01 |

| 16 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 17 | BITMAP AND | | | | | |

| 18 | BITMAP MERGE | | | | | |

| 19 | BITMAP KEY ITERATION | | | | | |

| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_53663F | 1 | 13 | 2 (0)| 00:00:01 |

| 22 | BITMAP MERGE | | | | | |

| 23 | BITMAP KEY ITERATION | | | | | |

| 24 | TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR | 2 | 32 | 2 (0)| 00:00:01 |

| 25 | BITMAP CONVERSION TO ROWIDS| | | | | |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_53663F | 29 | 522 | 2 (0)| 00:00:01 |

| 29 | INDEX FULL SCAN | COUNTRIES_STAR_PK | 23 | 92 | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------------------

Note

-----

- star transformation used for this statement

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:

SQL&gt; alter table products_star drop constraint prod_star_pk;

Table altered.

Now, we create the compound primary key

SQL&gt; alter table products_star add constraint prod_star_pk primary key (prod_id,prod_name);

Table altered.

Next we add the prod_name column to the sales_star fact table and populate this column with the prod_name from the products_star.prod_name column:

SQL&gt; ALTER TABLE sales_star ADD prod_name VARCHAR2(50);

Table altered.

SQL&gt; MERGE INTO sales_star a USING (
  2      SELECT
  3          prod_id,
  4          prod_name
  5      FROM
  6          products_star
  7  ) b ON (a.prod_id = b.prod_id)
  8  WHEN MATCHED THEN UPDATE SET
  9      a.prod_name = b.prod_name;

904924 rows merged.

SQL&gt; COMMIT;

Commit complete.

SQL> alter table products_star drop constraint prod_star_pk;

Table altered.

Now, we create the compound primary key

SQL> alter table products_star add constraint prod_star_pk primary key (prod_id,prod_name);

Table altered.

Next we add the prod_name column to the sales_star fact table and populate this column with the prod_name from the products_star.prod_name column:

SQL> ALTER TABLE sales_star ADD prod_name VARCHAR2(50);

Table altered.

SQL> MERGE INTO sales_star a USING (

2 SELECT

3 prod_id,

4 prod_name

5 FROM

6 products_star

7 ) b ON (a.prod_id = b.prod_id)

8 WHEN MATCHED THEN UPDATE SET

9 a.prod_name = b.prod_name;

904924 rows merged.

SQL> COMMIT;

Commit complete.

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

SQL&gt; drop index sales_star_prod_bix;

Index dropped.

SQL&gt; create  bitmap index sales_star_prod_bix on sales_star(prod_id,prod_name);

Index created.

SQL> drop index sales_star_prod_bix;

Index dropped.

SQL> create bitmap index sales_star_prod_bix on sales_star(prod_id,prod_name);

Index created.

We gather stats on the two tables

SQL&gt; exec dbms_stats.gather_table_stats ( ownname =&gt; USER, tabname =&gt; 'products_star', degree =&gt; DBMS_STATS.AUTO_DEGREE, estimate_percent =&gt; dbms_stats.AUTO_SAMPLE_SIZE, cascade =&gt; TRUE  ) ;

PL/SQL procedure successfully completed.

SQL&gt; exec dbms_stats.gather_table_stats ( ownname =&gt; USER, tabname =&gt; 'sales_star', degree =&gt; DBMS_STATS.AUTO_DEGREE, estimate_percent =&gt; dbms_stats.AUTO_SAMPLE_SIZE, cascade =&gt; TRUE  ) ;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ( ownname => USER, tabname => 'products_star', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, cascade => TRUE ) ;

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats ( ownname => USER, tabname => 'sales_star', degree => DBMS_STATS.AUTO_DEGREE, estimate_percent => dbms_stats.AUTO_SAMPLE_SIZE, cascade => TRUE ) ;

PL/SQL procedure successfully completed.

And rerun our query.

SQL&gt; select
  2      sum(quantity_sold),
  3      p.prod_subcategory_desc,
  4      c.cust_gender
  5  from
  6     sales_star s
  7     join products_star p   ON (s.prod_id = p.prod_id)
  8     join  customers_star c ON (s.cust_id = c.cust_id)
  9     join countries_star d   ON (c.country_id = d.country_id)
 10  where
 11     p.prod_subcategory_desc = 'Memory' and
 12     c.cust_city = 'Oxford' and
 13     c.cust_gender = 'F'
 14  group by
 15     p.prod_subcategory_desc, c.cust_gender;


Execution Plan
----------------------------------------------------------
Plan hash value: 252248325

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                               |     1 |    43 |    37   (3)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION             |                               |       |       |            |          |
|   2 |   LOAD AS SELECT                       | SYS_TEMP_0FD9D660E_54B1A1     |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID         | CUSTOMERS_STAR                |    45 |   810 |    12   (0)| 00:00:01 |
|   4 |     BITMAP CONVERSION TO ROWIDS        |                               |       |       |            |          |
|   5 |      BITMAP AND                        |                               |       |       |            |          |
|*  6 |       BITMAP INDEX SINGLE VALUE        | CUSTOMERS_STAR_CITY_BIX       |       |       |            |          |
|*  7 |       BITMAP INDEX SINGLE VALUE        | CUSTOMERS_STAR_GENDER_BIX     |       |       |            |          |
|   8 |   HASH GROUP BY                        |                               |     1 |    43 |    25   (8)| 00:00:01 |
|*  9 |    HASH JOIN                           |                               |     1 |    43 |    25   (8)| 00:00:01 |
|* 10 |     HASH JOIN                          |                               |     1 |    39 |    23   (5)| 00:00:01 |
|* 11 |      HASH JOIN                         |                               |     1 |    30 |    21   (5)| 00:00:01 |
|  12 |       TABLE ACCESS BY INDEX ROWID      | PRODUCTS_STAR                 |     2 |    36 |     2   (0)| 00:00:01 |
|  13 |        BITMAP CONVERSION TO ROWIDS     |                               |       |       |            |          |
|* 14 |         BITMAP INDEX SINGLE VALUE      | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|  15 |       TABLE ACCESS BY INDEX ROWID      | SALES_STAR                    |    20 |   240 |    19   (0)| 00:00:01 |
|  16 |        BITMAP CONVERSION TO ROWIDS     |                               |       |       |            |          |
|  17 |         BITMAP AND                     |                               |       |       |            |          |
|  18 |          BITMAP MERGE                  |                               |       |       |            |          |
|  19 |           BITMAP KEY ITERATION         |                               |       |       |            |          |
|  20 |            TABLE ACCESS FULL           | SYS_TEMP_0FD9D660E_54B1A1     |     1 |    13 |     2   (0)| 00:00:01 |
|* 21 |            BITMAP INDEX RANGE SCAN     | SALES_STAR_CUST_BIX           |       |       |            |          |
|  22 |          BITMAP MERGE                  |                               |       |       |            |          |
|  23 |           BITMAP KEY ITERATION         |                               |       |       |            |          |
|  24 |            TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR                 |     2 |    36 |     2   (0)| 00:00:01 |
|  25 |             BITMAP CONVERSION TO ROWIDS|                               |       |       |            |          |
|* 26 |              BITMAP INDEX SINGLE VALUE | PRODUCTS_STAR_SUBCATEGORY_BIX |       |       |            |          |
|* 27 |            BITMAP INDEX RANGE SCAN     | SALES_STAR_PROD_BIX           |       |       |            |          |
|  28 |      TABLE ACCESS FULL                 | SYS_TEMP_0FD9D660E_54B1A1     |    45 |   405 |     2   (0)| 00:00:01 |
|  29 |     INDEX FULL SCAN                    | COUNTRIES_STAR_PK             |    23 |    92 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("C"."CUST_CITY"='Oxford')
   7 - access("C"."CUST_GENDER"='F')
   9 - access("C1"="D"."COUNTRY_ID")
  10 - access("S"."CUST_ID"="C0")
  11 - access("S"."PROD_ID"="P"."PROD_ID")
  14 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')
  21 - access("S"."CUST_ID"="C0")
  26 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')
  27 - access("S"."PROD_ID"="P"."PROD_ID")

Note
-----
   - star transformation used for this statement


Statistics
----------------------------------------------------------
          2  recursive calls
          8  db block gets
        211  consistent gets
          1  physical reads
        600  redo size
        562  bytes sent via SQL*Net to client
        381  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select

2 sum(quantity_sold),

3 p.prod_subcategory_desc,

4 c.cust_gender

5 from

6 sales_star s

7 join products_star p ON (s.prod_id = p.prod_id)

8 join customers_star c ON (s.cust_id = c.cust_id)

9 join countries_star d ON (c.country_id = d.country_id)

10 where

11 p.prod_subcategory_desc = 'Memory' and

12 c.cust_city = 'Oxford' and

13 c.cust_gender = 'F'

14 group by

15 p.prod_subcategory_desc, c.cust_gender;

Execution Plan

----------------------------------------------------------

Plan hash value: 252248325

------------------------------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 43 | 37 (3)| 00:00:01 |

| 1 | TEMP TABLE TRANSFORMATION | | | | | |

| 3 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS_STAR | 45 | 810 | 12 (0)| 00:00:01 |

| 4 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 5 | BITMAP AND | | | | | |

| 8 | HASH GROUP BY | | 1 | 43 | 25 (8)| 00:00:01 |

|* 9 | HASH JOIN | | 1 | 43 | 25 (8)| 00:00:01 |

|* 10 | HASH JOIN | | 1 | 39 | 23 (5)| 00:00:01 |

|* 11 | HASH JOIN | | 1 | 30 | 21 (5)| 00:00:01 |

| 12 | TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR | 2 | 36 | 2 (0)| 00:00:01 |

| 13 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 15 | TABLE ACCESS BY INDEX ROWID | SALES_STAR | 20 | 240 | 19 (0)| 00:00:01 |

| 16 | BITMAP CONVERSION TO ROWIDS | | | | | |

| 17 | BITMAP AND | | | | | |

| 18 | BITMAP MERGE | | | | | |

| 19 | BITMAP KEY ITERATION | | | | | |

| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_54B1A1 | 1 | 13 | 2 (0)| 00:00:01 |

| 22 | BITMAP MERGE | | | | | |

| 23 | BITMAP KEY ITERATION | | | | | |

| 24 | TABLE ACCESS BY INDEX ROWID | PRODUCTS_STAR | 2 | 36 | 2 (0)| 00:00:01 |

| 25 | BITMAP CONVERSION TO ROWIDS| | | | | |

| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660E_54B1A1 | 45 | 405 | 2 (0)| 00:00:01 |

| 29 | INDEX FULL SCAN | COUNTRIES_STAR_PK | 23 | 92 | 1 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

6 - access("C"."CUST_CITY"='Oxford')

7 - access("C"."CUST_GENDER"='F')

9 - access("C1"="D"."COUNTRY_ID")

10 - access("S"."CUST_ID"="C0")

11 - access("S"."PROD_ID"="P"."PROD_ID")

14 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')

21 - access("S"."CUST_ID"="C0")

26 - access("P"."PROD_SUBCATEGORY_DESC"='Memory')

27 - access("S"."PROD_ID"="P"."PROD_ID")

Note

-----

- star transformation used for this statement

Statistics

----------------------------------------------------------

2 recursive calls

8 db block gets

211 consistent gets

1 physical reads

600 redo size

562 bytes sent via SQL*Net to client

381 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

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.

Cookie	Duration	Description
__cfruid	session	Cloudflare sets this cookie to identify trusted web traffic.
cookielawinfo-checkbox-marketing	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Marketing".
cookielawinfo-checkbox-necessary	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-preferences	1 month	This cookie is set by the GDPR Cookie Consent plugin to check if the user has given consent to use cookies under the "Preferences" category.
cookielawinfo-checkbox-statistics	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Statistics".
cookielawinfo-checkbox-unclassified	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Unclassified".
CookieLawInfoConsent	1 month	Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie.
csrftoken	1 year	This cookie is associated with Django web development platform for python. Used to help protect the website against Cross-Site Request Forgery attacks

Cookie	Duration	Description
AnalyticsSyncHistory	1 month	Linkedin set this cookie to store information about the time a sync took place with the lms_analytics cookie.
bcookie	2 years	LinkedIn sets this cookie from LinkedIn share buttons and ad tags to recognize browser ID.
bscookie	2 years	LinkedIn sets this cookie to store performed actions on the website.
lang	session	LinkedIn sets this cookie to remember a user's language setting.
li_gc	2 years	Linkedin set this cookie for storing visitor's consent regarding using cookies for non-essential purposes.
lidc	1 day	LinkedIn sets the lidc cookie to facilitate data center selection.
mgref	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
mgrefby	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
UserMatchHistory	1 month	LinkedIn sets this cookie for LinkedIn Ads ID syncing.

Cookie	Duration	Description
G	1 year	Cookie used to facilitate the translation into the preferred language of the visitor.
SERVERID	session	This cookie is set by Slideshare's HAProxy load balancer to assign the visitor to a specific server.

Cookie	Duration	Description
_ga	2 years	The _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors.
_ga_7H38LVR4Z5	2 years	This cookie is installed by Google Analytics.
_gat_gtag_UA_44804396_1	1 minute	Set by Google to distinguish users.
_gat_UA-44804396-1	1 minute	A variation of the _gat cookie set by Google Analytics and Google Tag Manager to allow website owners to track visitor behaviour and measure site performance. The pattern element in the name contains the unique identity number of the account or website it relates to.
_gcl_au	3 months	Provided by Google Tag Manager to experiment advertisement efficiency of websites using their services.
_gid	1 day	Installed by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously.
CONSENT	2 years	YouTube sets this cookie via embedded youtube-videos and registers anonymous statistical data.
SIDCC	6 Months	The "SIDCC" cookie is used as security measure to protect users data from unauthorised access
test_cookie	15 minutes	The test_cookie is set by doubleclick.net and is used to determine if the user's browser supports cookies.
VISITOR_INFO1_LIVE	5 months 27 days	A cookie set by YouTube to measure bandwidth that determines whether the user gets the new or old player interface.
YSC	session	YSC cookie is set by Youtube and is used to track the views of embedded videos on Youtube pages.
yt-remote-connected-devices	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt-remote-device-id	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt.innertube::nextId	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.
yt.innertube::requests	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.

Cookie	Duration	Description
AN	1 month
AS	session
ebEventToTrack	1 month
eblang	1 year
SNID	2 years	This cookie is set by the Google. This cookie is used by the map which helps visitors to identify and reach the facility.
SP	session
SS	session