The New Home of Oracle-wiki.net
All things oracle...
A website for IT Professionals involved with Oracle database.
STAR_TRANSFORMATION and NO_STAR_TRANSFORMATION Hint
Introduction
The Oracle hint STAR_TRANSFORMATION is used to enable the star transformation optimization in queries involving star schemas. Star schemas are most common in data warehouses. This optimization improves query performance by transforming a star join into a more efficient form.
Before we look at hint examples we need to understand star schemas and how they work.
What is a Star Schema
In data warehousing and business intelligence, the star schema is a common database design. Its name comes from its visual representation: a central fact table connected to surrounding dimension tables, forming a star-like structure. This organization simplifies data analysis.
Fact Table
In a star schema, the fact table sits at the center. Each row in the fact table represents a transaction or event It contains the quantitative data or "facts" that you want to analyze, such as sales revenue, quantities sold, or profit. To provide context for these facts, the fact table is surrounded by dimension tables. The fact table contains foreign keys that reference the primary keys of these dimension tables.
Dimension Tables
Each dimension table stores descriptive or categorical information (e.g., date, product, customer) and has a primary key. These primary keys are referenced as foreign keys in the fact table, creating the link between the fact table and dimension tables. The dimension tables provide context for the facts and allow for detailed analysis, such as filtering or grouping data by different dimensions (e.g., by product or by time period).
Star Schema vs. Traditional Relational Schema
In a traditional relational schema, relationships are typically modeled through foreign key references, where one central table (e.g., a customer table) has foreign keys that reference other related tables. In contrast, a star schema features a fact table in the center, surrounded by dimension tables, each with its own primary key. The fact table links to these dimension tables via foreign keys.
One consequence of a star schema design, is that fact tables can become quite wide, as each new dimension adds a foreign key to the fact table. In traditional relational schemas, on the other hand, tables do not typically grow as wide because they are usually normalized, with child tables referencing a single primary key in a parent table.
Key Characteristics of the Star Schema:
Simplicity
The design is simple and intuitive, making it easy for business users to understand and query.
Denormalization
Dimension tables are usually denormalized, meaning data redundancy is allowed to improve query performance.
Performance
The star schema is optimized for query performance, as it minimizes the number of joins needed between tables.
Using the STAR_TRANSFORMATION hint
In order to understand how the star_transformation hint works, let's look at how the optimizer processes a join without the hint. Assume we have the following schema for a retail sales database
Fact Table Sales
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
date_key NUMBER NOT NULL,
customer_key NUMBER NOT NULL,
product_key NUMBER NOT NULL,
store_key NUMBER NOT NULL,
sales_amount NUMBER(10,2) NOT NULL,
CONSTRAINT fk_sales_date FOREIGN KEY (date_key) REFERENCES time(date_key),
CONSTRAINT fk_sales_customer FOREIGN KEY (customer_key) REFERENCES customer(customer_key),
CONSTRAINT fk_sales_product FOREIGN KEY (product_key) REFERENCES product(product_key),
CONSTRAINT fk_sales_store FOREIGN KEY (store_key) REFERENCES store(store_key)
) TABLESPACE users;
/
CREATE BITMAP INDEX sales_date_bix ON sales(date_key)
/
CREATE BITMAP INDEX sales_product_bix ON sales(product_key)
/
CREATE BITMAP INDEX sales_store_bix ON sales(store_key)
/
Dimension Tables time, customer, product, store
CREATE TABLE time (
date_key NUMBER PRIMARY KEY,
date_value DATE NOT NULL,
year NUMBER NOT NULL,
quarter VARCHAR2(10) NOT NULL,
month VARCHAR2(20) NOT NULL,
day NUMBER NOT NULL
) TABLESPACE users;
CREATE TABLE customer (
customer_key NUMBER PRIMARY KEY,
customer_name VARCHAR2(255) NOT NULL,
customer_type VARCHAR2(100) NOT NULL
) TABLESPACE users;
CREATE TABLE product (
product_key NUMBER PRIMARY KEY,
product_name VARCHAR2(255) NOT NULL,
category VARCHAR2(100) NOT NULL,
price NUMBER(10,2) NOT NULL
) TABLESPACE users;
CREATE TABLE store (
store_key NUMBER PRIMARY KEY,
store_name VARCHAR2(255) NOT NULL,
city VARCHAR2(255) NOT NULL
) TABLESPACE users;
Star Join Without the STAR_TRANSFORMATION Hint
In a traditional star join, the database joins a large fact table with multiple dimension tables using foreign keys. The optimizer typically does a full scan of the fact table and performs hash joins or nested loop joins. This query retrieves total sales for a specific product category, in a given time period, for a certain store location.
SELECT t.year, p.category, s.city, SUM(f.sales_amount) AS total_sales
FROM sales f
JOIN time t ON f.date_key = t.date_key
JOIN product p ON f.product_key = p.product_key
JOIN store s ON f.store_key = s.store_key
WHERE t.year = 2024
AND p.category = 'Electronics'
AND s.city = 'New York'
GROUP BY t.year, p.category, s.city
/
Example Plan
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 100K (100) | |
| 1 | HASH GROUP BY | | 500 | 20K | 10K (20) | |
| 2 | HASH JOIN | | 100M | 4G | 100K (30) | |
| 3 | HASH JOIN | | 100M | 4G | 80K (25) | |
| 4 | HASH JOIN | | 100M | 4G | 60K (20) | |
| 5 | FULL TABLE SCAN | SALES | 100M | 4G | 50K (15) | |
| 6 | TABLE ACCESS | TIME | 100 | 4K | 100 (5) | |
| 7 | TABLE ACCESS | PRODUCT | 500 | 20K | 200 (5) | |
| 8 | TABLE ACCESS | STORE | 1K | 40K | 300 (5) | |
---------------------------------------------------------------------------------
Without the hint, the optimizer is likely to perform the following
The optimizer scans the entire sales (fact) table which has millions of rows, before filtering.
Even if indexes exist on date_key, product_key, and store_key, the query still performs multiple full table scans and hash joins.
This can be very slow for large datasets.
Star Join with STAR_TRANSFORMATION Hint
When using the STAR_TRANSFORMATION hint, the optimizer will try to use bitmap indexes on foreign keys in the fact table and filtering on the dimension tables. This query retrieves total sales for a specific product category, in a given time period, for a certain store location, but this time with the STAR_TRANSFORMATION hint.
SELECT /*+ STAR_TRANSFORMATION */ t.year, p.category, s.city, SUM(f.sales_amount) AS total_sales
FROM sales f
JOIN time t ON f.date_key = t.date_key
JOIN product p ON f.product_key = p.product_key
JOIN store s ON f.store_key = s.store_key
WHERE t.year = 2024
AND p.category = 'Electronics'
AND s.city = 'New York'
GROUP BY t.year, p.category, s.city
/
Example Plan
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5K (100) | |
| 1 | HASH GROUP BY | | 500 | 20K | 500 (10) | |
| 2 | BITMAP CONVERSION TO ROWID| | 100K | 4G | 5K (5) | |
| 3 | BITMAP OR | | 100K | 4G | 4K (4) | |
| 4 | BITMAP INDEX SINGLE VALUE| SALES_DATE_BIX | 10K | | 1K (2) | |
| 5 | BITMAP INDEX SINGLE VALUE| SALES_PRODUCT_BIX | 10K | | 1K (2) | |
| 6 | BITMAP INDEX SINGLE VALUE| SALES_STORE_BIX | 10K | | 1K (2) | |
| 7 | TABLE ACCESS BY INDEX ROWID| SALES | 100K | 4G | 5K (5) | |
| 8 | TABLE ACCESS | TIME | 100 | 4K | 50 (1) | |
| 9 | TABLE ACCESS | PRODUCT | 500 | 20K | 50 (1) | |
| 10 | TABLE ACCESS | STORE | 1K | 40K | 50 (1) | |
-------------------------------------------------------------------------------------------------
With the hint, the optimizer is likely to perform the following
The optimizer first filters time, product, and store for the given conditions.
Instead of scanning the sales table, the optimizer retrieves only the matching sales rows using bitmap indexes on date_key, product_key, and store_key.
The bitmaps are combined to efficiently locate rows in sales, avoiding a full table scan.
Important Notes
If the initialization parameter STAR_TRANSFORMATION_ENABLED is set to true, then the optimizer will try to transform the query.
If the initialization parameter STAR_TRANSFORMATION_ENABLED is set to false or temp_disable, then the optimizer will not consider a star transformation, unless the hint is specified.
The official documentation makes this important point "Even if the hint is specified, there is no guarantee that the transformation will take place. The optimizer generates the subqueries only if it seems reasonable to do so. If no subqueries are generated, then there is no transformed query, and the best plan for the un-transformed query is used, regardless of the hint. "
NO_STAR_TRANSFORMATION Hint
The NO_STAR_TRANSFORMATION hint tells the optimizer not to perform star query transformation. This is useful, if the initialization parameter STAR_TRANSFORMATION_ENABLED is causing a transformation to take place that is not efficient.
Further Reading
You can find more information on using the hint in the documentation
Information on STAR_TRANSFORMATION_ENABLED
Published on
Published 14th February 2025