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 | TYPE | VALUE |
|---|---|---|
| star_transformation_enabled | string | 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
Id Operation Name Rows Bytes Cost (%CPU) Time Pstart Pstop 0 SELECT STATEMENT 263 20251 948 (1) 00:00:12 1 SORT GROUP BY 263 20251 948 (1) 00:00:12 * 2 HASH JOIN 9675 727K 947 (1) 00:00:12 3 TABLE ACCESS FULL TIMES 1826 21912 18 (0) 00:00:01 * 4 HASH JOIN 9675 614K 928 (1) 00:00:12 * 5 TABLE ACCESS FULL PRODUCTS 72 2664 3 (0) 00:00:01 6 PARTITION RANGE ALL 9675 264K 924 (1) 00:00:12 1 28 7 TABLE ACCESS BY LOCAL INDEX ROWID SALES 9675 264K 924 (1) 00:00:12 1 28 8 BITMAP CONVERSION TO ROWIDS 9 BITMAP AND 10 BITMAP MERGE 11 BITMAP KEY ITERATION 12 BUFFER SORT * 13 TABLE ACCESS FULL CHANNELS 2 22 3 (0) 00:00:01 * 14 BITMAP INDEX RANGE SCAN SALES_CHANNEL_BIX 1 28 15 BITMAP MERGE 16 BITMAP KEY ITERATION 17 BUFFER SORT * 18 HASH JOIN 1461 39447 411 (1) 00:00:05 * 19 TABLE ACCESS FULL COUNTRIES 1 15 3 (0) 00:00:01 * 20 TABLE ACCESS FULL CUSTOMERS 27750 325K 407 (1) 00:00:05 * 21 BITMAP INDEX RANGE SCAN SALES_CUST_BIX 1 28
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.