FACT and NO_FACT Hint

Introduction

The Oracle FACT hint is used to enable optimizations for queries involving star schemas. The FACT hint explicitly tells the Oracle optimizer that the table specified in the hint should be treated as a fact table during star transformation.  This is useful when the optimizer doesn't automatically recognize a table as a fact table, or when you want to ensure that star transformation is used.  Essentially, it guides the optimizer to use a query plan that is optimized for star schema queries.

If you are not familiar with Star Schemas, you can review the blog I posted a while back.

Using the FACT hint

When the optimizer encounters the FACT hint, it will prioritize the following key components of the execution plan

• • Building bitmaps from the dimension tables.

  • Using those bitmaps to filter the fact table.

This can significantly improve query performance, especially for complex queries that involve multiple dimension tables.  Its important to note that this hint works in conjunction with the STAR_ TRANSFORMATION hint

Example

The following query is an example of how the FACT hint works, with an example query plan

SELECT /*+ FACT(sales_fact) */

    SUM(sale_fact.sales_amount)

FROM

    sales_fact,

    customers_dim,

    products_dim

WHERE

    sales_fact.customer_id = customers_dim.customer_id

    AND sales_fact.product_id = products_dim.product_id

    AND customers_dim.customer_region = 'West'

    AND products_dim.product_category = 'Electronics';

Example Plan

-----------------------------------------------------------------------------------

| Id | Operation                     | Name            | Rows | Bytes | Cost (%CPU)|

------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT              |                 |      |       |     (100)  |

|  1 | SORT AGGREGATE                |                 |    1 |    10 |     (99)   |

|  2 | BITMAP CONVERSION TO ROWIDS   |                 |  100 |  1000 |     (98)   |

|  3 | TABLE ACCESS BY ROWID         | SALES_FACT      |  100 |  1000 |     (97)   |

|  4 | BITMAP AND                    |                 |      |       |     (96)   |

|  5 | BITMAP INDEX RANGE SCAN       | CUSTOMERS_IDX   |   50 |       |     (50)   |

|  6 | BITMAP INDEX RANGE SCAN       | PRODUCTS_IDX    |   50 |       |     (50)   |

|  7 | TABLE ACCESS FULL             | CUSTOMERS_DIM   |      |       |     (10)   |

|  8 | TABLE ACCESS FULL             | PRODUCTS_DIM    |      |       |     (10)   |

------------------------------------------------------------------------------------

Explanation of the Plan:

• Lines 5 and 6 show bitmap index scans on the dimension tables.

• Line 4 shows a BITMAP AND operation, combining the results.

• Line 2 shows the bitmap being converted to row IDs.

• Line 3 shows the fact table being accessed using those row IDs.

Its important to note that this is an example, explain plans in the real world are often much more complex.  Also, using the hint does not guarantee the optimizer will chooses these paths.  The optimizer will still look at the overall cost to decide which plan is more optimal.  And of course, test, test, test!!

NO_FACT Hint

The NO_FACT hint tells the optimizer not to  treat the table specified as a fact table   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 6th February 2025