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)
The objective from the 11G OCM Upgrade Exam is:
Configure a schema to support a star transformation query
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:
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.
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.
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.