X

Insights into Statistics, Query Optimization and the Oracle Optimizer

Optimizer Transformations: Star Transformation

Star transformation was introduced in Oracle 8i to process star queries efficiently. These queries are commonly used in data warehouse applications that follow the Star Schema data model. The Star Schema is so called because the data model diagram resembles a star. The center of the star consists of one or more fact tables and the points of the star are the dimension tables.

star_model.PNG

The basic idea of this transformation is to steer clear of using a full table scan access method on large tables, referred to as fact tables in the Star Schema. In a typical star query, the fact table is joined to several much smaller dimension tables. The fact table typically contains one key (referred to as foreign key) for every dimension table as well as a number of measure columns such as sales amount. The corresponding key in the dimension table is referred to as the primary key. The join is based on a foreign key of the fact table with the corresponding primary key of the dimension table. The query also contains filter predicates on other columns of the dimension tables that typically are very restrictive. The combination of these filters help to dramatically reduce the data set processed from the fact table.  The goal of star transformation is to access only this reduced set of data from the fact table.

Consider the following star query Q1. The query is to find the total sales amount in all cities in California for quarters Q1 and Q2 of year 1999 through the Internet.

Q1:

SELECT c.cust_city, 
       t.calendar_quarter_desc,
       SUM(s.amount_sold) sales_amount
FROM  sales     s, 
      times     t, 
      customers c, 
      channels  ch
WHERE s.time_id = t.time_id
AND   s.cust_id = c.cust_id
AND   s.channel_id = ch.channel_id
AND   c.cust_state_province = 'CA'
AND   ch.channel_desc = 'Internet'
AND   t.calendar_quarter_desc IN ('1999-01','1999-02')
GROUP BY c.cust_city, t.calendar_quarter_desc;

Sales is the fact table while the other tables are considered as dimension tables. The Sales table contains one row for every sale of a product and thus it may contain billions of sales records. However only a few of them are sold to customers in California through the Internet for the specified quarters. The query is transformed into Q2.

Q2:

SELECT c.cust_city, 
       t.calendar_quarter_desc, 
       SUM(s.amount_sold) sales_amount
FROM sales     s, 
     times     t, 
     customers c
WHERE s.time_id = t.time_id
  AND s.cust_id = c.cust_id
  AND c.cust_state_province = 'CA'
  AND t.calendar_quarter_desc IN ('1999-01','1999-02')
  AND s.time_id IN (SELECT time_id
                            FROM times 
                            WHERE calendar_quarter_desc IN('1999-01','1999-02'))
  AND s.cust_id IN (SELECT cust_id
                           FROM customers
                           WHERE cust_state_province='CA')
  AND s.channel_id IN (SELECT channel_id  
                                 FROM channels 
                                 WHERE channel_desc = 'Internet')
GROUP BY c.cust_city, t.calendar_quarter_desc;

Star transformation is essentially about adding subquery predicates corresponding to the constraint dimensions. These subquery predicates are referred to as bitmap semi-join predicates. The transformation is performed when there are indexes on the fact join columns (s.timeid, s.custid...). By driving bitmap AND and OR operations (bitmaps can be from bitmap indexes or generated from regular B-Tree indexes) of the key values supplied by the subqueries, only the relevant rows from the fact table need to be retrieved.  If the filters on the dimension tables filter out a lot of data, this can be much more efficient than a full table scan on the fact table.  After the relevant rows have been retrieved from the fact table, they may need to be joined back to the dimension tables, using the original predicates. In some cases, the join back can be eliminated. We will discuss this situation later. 

Table 1 shows the query plan for the transformed query. Note that the sales table has a bitmap access path instead of a full table scan. For each key value coming from the subqueries (lines 11, 16, 21), the bitmaps are retrieved from the fact table indexes (lines 12, 17, 22).  Each bit in the bitmap corresponds to a row in fact table. The bit is set if the key value from the subquery is same as the value in the row of fact table.  For example, the bitmap [1][0][1][0][0][0]...(all 0s for remaining rows) indicate that rows 1 and 3 of fact table has matching key value from subquery.  Lets say the above bitmap is for a key value from customers table subquery.

The operations in lines 9, 14, 19 iterates over the keys from the subqueries and get the corresponding bitmaps. Lets say the customers subquery produces one more key value with the  bitmap [0][1][0][0][0][0]...

The bitmaps for each subquery are merged (ORed) (lines 8, 13 and 18). In the above example, it will produce a single bitmap [1][1][1][0][0][0]... for customers subquery after merging the two bitmaps.

The merged bitmaps are ANDed (line 7). Lets say the bitmap from channels is [1][0][0][0][0][0]...  If you AND this bitmap with the bitmap from customers subquery it will produce [1][0][0][0][0]...

The corresponding rowids of the final bitmap are generated (line 6).  The fact table rows are retrieved using the rowids (line 5).  In the above example, it will generate only 1 rowid corresponding to the first row and fetches only a single row instead of scanning the entire fact table.

The representation of bitmaps in the above example is for illustration purpose only. In Oracle, they are represented and stored in a compressed form.

Table 1: The plan of the transformed query...

--------------------------------------------------------------
| Id  | Operation                         | Name             |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                  |  
|   1 |  HASH GROUP BY                    |                  | 
|*  2 |   HASH JOIN                       |                  |
|*  3 |    TABLE ACCESS FULL              | CUSTOMERS        |
|*  4 |    HASH JOIN                      |                  |
|*  5 |     TABLE ACCESS FULL             | TIMES            |
|   6 |     VIEW                          | VW_ST_B1772830   |
|   7 |      NESTED LOOPS                 |                  |
|   8 |       PARTITION RANGE SUBQUERY    |                  |
|   9 |        BITMAP CONVERSION TO ROWIDS|                  |
|  10 |         BITMAP AND                |                  |
|  11 |          BITMAP MERGE             |                  |
|  12 |           BITMAP KEY ITERATION    |                  |
|  13 |            BUFFER SORT            |                  |
|* 14 |             TABLE ACCESS FULL     | CHANNELS         |
|* 15 |            BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX|
|  16 |          BITMAP MERGE             |                  |
|  17 |           BITMAP KEY ITERATION    |                  |
|  18 |            BUFFER SORT            |                  |
|* 19 |             TABLE ACCESS FULL     | TIMES            |
|* 20 |            BITMAP INDEX RANGE SCAN| SALES_TIME_BIX   |
|  21 |          BITMAP MERGE             |                  |
|  22 |           BITMAP KEY ITERATION    |                  |
|  23 |            BUFFER SORT            |                  |
|* 24 |             TABLE ACCESS FULL     | CUSTOMERS        |
|* 25 |            BITMAP INDEX RANGE SCAN| SALES_CUST_BIX   |
|  26 |       TABLE ACCESS BY USER ROWID  | SALES            |
--------------------------------------------------------------

Join back elimination

The subqueries and their bitmap tree only filter the fact table based on the dimension filters, so it may still be necessary to join to the dimension table.  The join back of the dimension table is eliminated when all the predicates on dimension tables are part of the semijoin subquery predicate, the column(s) selected from the subquery are unique and the dimension columns are not in select list, group by etc. In the above example, the table channels is not joined back to the sales table since it is not referenced outside and channel_id is unique.

Temporary table transformation

If the join back is not eliminated, Oracle stores the results of the subquery in a temporary table to avoid re-scanning the dimension table (for bitmap key generation and join back). In addition to this, the results are materialized if the query is run in parallel, so that each slave can select the results from the temporary tables instead of executing the subquery again.

For example, if Oracle materializes the results of the subquery on customers into a temporary table, the transformed query Q3 will be as follows.

Q3:
SELECT t1.c1 cust_city, 
       t.calendar_quarter_desc calendar_quarter_desc, 
       sum(s.amount_sold) sales_amount
FROM sales s, 
     sh.times t, 
     sys_temp_0fd9d6621_e7e24 t1
WHERE s.time_id=t.time_id
  AND s.cust_id=t1.c0 
  AND (t.calendar_quarter_desc='1999-q1' OR t.calendar_quarter_desc='1999-q2')
  AND s.cust_id IN    (SELECT  t1.c0 
                       FROM sys_temp_0fd9d6621_e7e24 t1)
  AND s.channel_id IN (SELECT  ch.channel_id 
                       FROM    channels ch
                       WHERE   ch.channel_desc='internet')
  AND s.time_id IN (SELECT t.time_id
                    FROM  times t
                    WHERE t.calendar_quarter_desc='1999-q1'
                    OR    t.calendar_quarter_desc='1999-q2')
GROUP BY t1.c1,  t.calendar_quarter_desc;
Note that customers is replaced by the temporary table sys_temp_0fd9d6621_e7e24 and references to columns cust_id and cust_city are replaced by the corresponding columns of the temporary table. The temporary table will be created with 2 columns -  (c0 number, c1 varchar2(30)). These columns corresponds to cust_id and cust_city of customers table. The table will be populated using the following query Q4 at the beginning of the execution of the statement Q3.

Q4:
SELECT c.cust_id, c.cust_city 
FROM   customers 
WHERE  c.cust_state_province = 'CA'

Table 2 shows the plan for the transformed query.

Table 2: Plan with temporary table transformation...

----------------------------------------------------------------------
| Id  | Operation                          | Name                    |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                         |
|   1 |  TEMP TABLE TRANSFORMATION         |                         |
|   2 |   LOAD AS SELECT                   |                         |
|*  3 |    TABLE ACCESS FULL               | CUSTOMERS               |
|   4 |   HASH GROUP BY                    |                         |
|*  5 |    HASH JOIN                       |                         |
|   6 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6613_C716F|
|*  7 |     HASH JOIN                      |                         |
|*  8 |      TABLE ACCESS FULL             | TIMES                   |
|   9 |      VIEW                          | VW_ST_A3F94988          |
|  10 |       NESTED LOOPS                 |                         |
|  11 |        PARTITION RANGE SUBQUERY    |                         |
|  12 |         BITMAP CONVERSION TO ROWIDS|                         |
|  13 |          BITMAP AND                |                         |
|  14 |           BITMAP MERGE             |                         |
|  15 |            BITMAP KEY ITERATION    |                         |
|  16 |             BUFFER SORT            |                         |
|* 17 |              TABLE ACCESS FULL     | CHANNELS                |
|* 18 |             BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX       |
|  19 |           BITMAP MERGE             |                         |
|  20 |            BITMAP KEY ITERATION    |                         |
|  21 |             BUFFER SORT            |                         |
|* 22 |              TABLE ACCESS FULL     | TIMES                   |
|* 23 |             BITMAP INDEX RANGE SCAN| SALES_TIME_BIX          |
|  24 |           BITMAP MERGE             |                         |
|  25 |            BITMAP KEY ITERATION    |                         |
|  26 |             BUFFER SORT            |                         |
|  27 |              TABLE ACCESS FULL     | SYS_TEMP_0FD9D6613_C716F|
|* 28 |             BITMAP INDEX RANGE SCAN| SALES_CUST_BIX          |
|  29 |        TABLE ACCESS BY USER ROWID  | SALES                   |
----------------------------------------------------------------------

The lines 1,2 and 3 of the plan materialize the customers subquery into the temporary table. In line 24,  it scans the temporary table (instead of the subquery) to build the bitmap from the fact table. Line 26 is for scanning the temporary table for joining back instead of scanning customers. Note that the filter on customers is not needed to be applied on the temporary table since the filter is already applied while materializing the temporary table.

Enabling the transformation

Star transformation is controlled by the star_transformation_enabled database initialization parameter. The parameter takes 3 values:

TRUE The Oracle Optimizer performs transformation by identifying fact and constraint dimension tables automatically. This is done in a cost-based manner, i.e. the transformation is performed only if the cost of the transformed plan is lower than the non-transformed plan. Also the optimizer will attempt temporary table transformation automatically whenever materialization improves performance.
FALSE The transformation is not tried.
TEMP_DISABLE This value has similar behavior as TRUE except that temporary table transformation is not tried.

 

The default value of the parameter is FALSE. You have to change the parameter value and create indexes on the joining columns of the fact table to take advantage of this transformation.

Summary

Star transformation improves the performance of queries with a very big fact table joined to multiple dimension tables when the dimension tables have very selective predicates. The transformation avoids the full scan of  the fact table. It fetches only relevant rows from the fact table that will eventually join to the constraint dimension rows. The transformation is performed based on cost - only when the cost of the transformed plan is lower than that of the non-transformed plan.  If the dimension filters do not significantly reduce the amount of data to be retrieved from the fact table, then a full table scan is more efficient.

In this post we have tried to illustrate the basic ideas behind star transformation by showing  simple example queries and plans. Oracle can do star transformation in more complex cases. For example, a query with multiple fact tables, snowflakes (dimension is a join of several normalized tables instead of denormalized single table), etc.

Join the discussion

Comments ( 11 )
  • Mahendra Wednesday, May 1, 2013

    Hi,

    I am seeing that only two subquery predicates are being used while Star Transformation.

    For example I have three/four dimensions having filter and one fact involved in Star Transformation. When I look at the plan, I am seeing only two subquery predicates are used while Bitmap Merge!

    Any idea why such behavior?

    Oracle Version:

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

    Thanks,

    Mahendra


  • guest Thursday, May 2, 2013

    Star transformation may not consider some dimensions for generating

    subqueries based on their cost/selectivity. So it is expected.


  • guest Friday, May 3, 2013

    Thanks,

    That's true but more than two sub-queries is giving me huge performance boost as output of BITMAP AND using three sub-query is significantly smaller than BITMAP AND using two sub-query

    Thanks,

    Mahendra


  • Nimish Shah Thursday, March 16, 2017

    We recently upgraded our DW environment to 12C and had kept optimizer version

    to 11. Several reports were generating wrong results with Star_transformation flag set to True. When we set this back to false the results were as expected. We heavily use DTT tales for temporary calculation. I think we may have hit an Oracle bug. Any other thoughts?

    Nimish


  • NigelBayliss Wednesday, March 22, 2017

    Hi Nimish,

    I you have wrong results, please make sure you raise an SR. Wrong results are bugs. If you can provide a test case, then that helps enormously (but I realize that can be difficult). Make sure you have the latest PSUs and check out this MOS note: Things to Consider to Avoid Poor Performance or Wrong Results on 12.1.0.2(Doc ID 2034610.1)

    Regards,

    Nigel.


  • RobK Wednesday, June 6, 2018
    Hello!

    In a perfect world an educational article would show execution plans that are indented and fit on one page.
    How are we supposed to learn from a story that we cannot read?

    The importance of the layout is so much underestimated.

    Cheers,
    R
  • Nigel Bayliss Thursday, June 7, 2018
    Hi R,

    Agreed! We migrated our blog platform some time ago and it created some formatting issues.

    I've been returning to old posts to fix them (admittedly very gradually). I've updated it now to make it look a little better. However, it looks like the plans never had any indentation, so that makes them a little harder to fix.

    I might attempt it by hand, but I want to see if I can get the original test case first. That way I can recreate them and avoid potential errors.

    Regards,
    Nigel
  • Nigel Bayliss Wednesday, June 13, 2018
    Hi - I retrieved indented plans from the docs, so the format should be OK now.
    Regards,
    Nigel
  • RobK Monday, August 6, 2018
    Thanks a lot. Appreciated.
    Re-read the article.

    Rob
  • Baolin Monday, August 13, 2018
    We are on 12cR2 multi-tenant.
    There is a SQL statement which runs well when STAT_TRANSFORMATION_ENABLE is TRUE. For comparison, we change the STAT_TRANSFORMATION_ENABLE to FALSE, then run the same SQL statement, SQL runs, come with ORA-00600 error. Here is the whole error message:
    Error report -
    SQL Error: ORA-00600: internal error code, arguments: [kkpapDIGetNum2], [0], [0], [0], [], [], [], [], [], [], [], []
    00600. 00000 - "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
    *Cause: This is the generic internal error number for Oracle program
    exceptions. It indicates that a process has encountered a low-level,
    unexpected condition. The first argument is the internal message
    number. This argument and the database version number are critical in
    identifying the root cause and the potential impact to your system.

    Can anyone shed some light on this. Thanks....
  • Nigel Bayliss Thursday, August 16, 2018
    This is a generic message so, it does not contain much information. It is possible to see the function that has exceptioned, but this is not usually enough to be sure of the likely cause. You will have a trace file in the diagnostics directory that will include the full call stack. This is much more useful. Please create an SR and upload the trace file. I suspect you might need patch 10097969 but I can't be sure.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services