11G OCM Configure a schema to support a star transformation query

References

Oracle® Database Data Warehousing Guide 11g Release 1 (11.1)

Oracle® Database Reference 11g Release 1 (11.1)

Oracle® Database SQL Language Reference 11g Release 1 (11.1)

Overview

The objective from the 11G OCM Upgrade Exam is:

Configure a schema to support a star transformation query

Reading Notes

Tuning Star Queries

Tuning Star Queries says that:

To get the best possible performance for star queries, it is important to follow some basic guidelines:

    • A bitmap index should be built on each of the foreign key columns of the fact table or tables.
    • The initialization parameter STAR_TRANSFORMATION_ENABLED should be set to TRUE. This enables an important optimizer feature for star-queries. It is set to FALSE by default for backward-compatibility.

When a data warehouse satisfies these conditions, the majority of the star queries running in the data warehouse will use a query execution strategy known as the star transformation. The star transformation provides very efficient query performance for star queries.

Star Transformation with a Bitmap Index

Star Transformation with a Bitmap Index says that:

A prerequisite of the star transformation is that there be a single-column bitmap index on every join column of the fact table. These join columns include all foreign key columns.

Test Case

The test I am using is the SQL from Replay a captured workload which has an SQL ID of 'dgdfqhj9ua2kv'.

The test query is as follows:

SELECT products.prod_category, products.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quantity_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOIN countries USING( country_id ) INNER JOIN channels USING( channel_id ) INNER JOIN times USING( time_id ) WHERE products.prod_valid = 'A' AND channels.channel_class = 'Direct' AND customers.cust_valid = 'A' AND countries.country_name = 'Australia' GROUP BY products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY products.prod_category, products.prod_subcategory, times.calendar_year ;

This query is about a yearly summary of product categories and sub-categories sold directly to active customers in Australia.

This query was put into an SQL Tuning Set called SYS.OCM11G during the procedure outlined in Use SQL Tuning Advisor.

Procedure

Check Initialization Parameter

The setting of the STAR_TRANSFORMATION_ENABLED parameter is:

SHOW PARAMETER STAR_TRANSFORMATION_ENABLED

The result is:

NAME

star_transformation_enabled

TYPE

string

VALUE

TRUE

Change Initialization Parameter

The setting of the STAR_TRANSFORMATION_ENABLED parameter is changed as follows:

ALTER SYSTEM SET STAR_TRANSFORMATION_ENABLED=TRUE SCOPE=BOTH;

Check for Missing Indices

The following query checks for missing bitmap indices on the join columns:

SELECT column_name FROM dba_tab_columns tab WHERE owner = 'SH' AND table_name = 'SALES' AND column_name IN ( 'PROD_ID', 'CUST_ID', 'CHANNEL_ID', 'TIME_ID' ) AND column_name NOT IN ( SELECT col.column_name FROM dba_ind_columns col INNER JOIN dba_indexes ind ON ( col.index_owner = ind.owner AND col.index_name = ind.index_name ) WHERE ind.table_owner = tab.owner AND ind.table_name = tab.table_name AND ind.index_type = 'BITMAP' ) /

Explain Plan

The EXPLAIN PLAN for the test query shows that the Star Transformation is being used:

EXPLAIN PLAN FOR SELECT products.prod_category, products.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quantity_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOIN countries USING( country_id ) INNER JOIN channels USING( channel_id ) INNER JOIN times USING( time_id ) WHERE products.prod_valid = 'A' AND channels.channel_class = 'Direct' AND customers.cust_valid = 'A' AND countries.country_name = 'Australia' GROUP BY products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY products.prod_category, products.prod_subcategory, times.calendar_year ; SELECT * FROM TABLE( DBMS_XPLAN.DISPLAY() );

The explain plan is:

PLAN_TABLE_OUTPUT

Plan hash value: 2491394033

Predicate Information (identified by operation id):

2 - access("SALES"."TIME_ID"="TIMES"."TIME_ID")

4 - access("SALES"."PROD_ID"="PRODUCTS"."PROD_ID")

5 - filter("PRODUCTS"."PROD_VALID"='A')

13 - filter("CHANNELS"."CHANNEL_CLASS"='Direct')

14 - access("SALES"."CHANNEL_ID"="CHANNELS"."CHANNEL_ID")

18 - access("CUSTOMERS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")

19 - filter("COUNTRIES"."COUNTRY_NAME"='Australia')

20 - filter("CUSTOMERS"."CUST_VALID"='A')

21 - access("SALES"."CUST_ID"="CUSTOMERS"."CUST_ID")

Note

- star transformation used for this statement

- SQL plan baseline "SYS_SQL_PLAN_5ab507bc2492fe52" used for this statement

46 rows selected.