Query Transformation Hints

Again, all hints in this category, save for the generic NO_QUERY_TRANSFORMATION hint, come in couples:


With NO_QUERY_TRANSFORMATION you disable all query transformation that the optimizer can perform. What the hint does not disable, though, are transformations that the optimizer always applies, such as the count transformation, predicate move-around, filter push-down, distinct elimination, and select-list pruning. This is of course no hint for a production environment, and it should only be used for testing purposes.

Generic Transformations

We have already briefly seen the NO_MERGE( view_name_or_alias ) hint. It prohibits the optimizer from merging views in a query. Similarly, you can force Oracle to merge (inline) views with MERGE( view_name ).


MERGE and NO_MERGE have nothing to do with the sort-merge join!

When the view contains a GROUP BY clause or DISTINCT operator (or UNIQUE) operator, the MERGE hint only works if complex view merging is enabled. The delayed evaluation of these operations can either improve or worsen performance, so use these hints wisely and sparingly. For instance, join conditions may reduce the data volume to be grouped or sorted, which may be beneficial to performance. Likewise, it can be advantageous to aggregate data as early as possible to deal with less data in subsequent operations. The optimizer uses the cost to determine whether it is better to merge views or not. Complex view merging also allows uncorrelated IN-subqueries to be merged into the main SQL statement.

USE_CONCAT always enables the OR-expansion, which transforms combined OR-conditions or IN-lists in the WHERE clause into a compound query with the UNION ALL operator. Whether the cost with such an OR-expansion is truly lower than without it is irrelevant: when specified Oracle does as instructed. NO_EXPAND makes the optimizer discard the OR-expansion as a possible query transformation.

Subquery unnesting can be forced without regard for the cost with the UNNEST hint. It combines subqueries in the WHERE, such as in IN-lists, into the FROM clause, which opens the door to more access paths for the optimizer to tinker with. Without subquery unnesting, Oracle treats the main query and its subqueries as separate statements: the subqueries are executed, and their results are used to run the main query. Subquery unnesting is possible if and only if the resulting join statement is guaranteed to returns the same rows as the original statement, for instance thanks to a primary key, and the subquery does not contain any aggregate functions. NO_UNNEST is, as you may have guessed, used to disable subquery unnesting. Oracle unnests subqueries automatically unless hinted, regardless of cost expected.

Materialized views that have been created with the ENABLE QUERY REWRITE clause can be used to provide data to queries that do not explicitly call these materialized view in their FROM clauses. Contrary to regular views, which are nothing but stored queries, materialized views store the result sets of the queries that define them and regularly refresh the data. Materialized views are particularly useful for queries that are run often, as a snapshot of the data is taken and stored, so the data does not have to be calculated from scratch every time a user asks for it. However, some users may not be aware of these materialized views, which is why they are executing their own queries that ask for the same data as contained in the materialized views. With REWRITE you allow people to benefit from the data of these materialized views; the hint has an optional parameter, which is the name of the materialized view. Typically, Oracle does this automatically when it determines that such a rewrite is beneficial. If successful, it shows up in the execution plan as MAT_VIEW REWRITE.

NO_REWRITE overrides the ENABLE QUERY REWRITE clause, if present. This can be helpful if you know that the data in the materialized view is stale compared to the source tables, and your query needs the current state of affairs.

The Star Transformation

In many data warehouses and OLAP databases that power business intelligence solutions, the dimensional rather than the entity-relationship data model is the gold standard. [1] Fact tables contain all information pertinent to a user’s queries, and they can easily be joined to so-called dimension tables with more details on the dimensions listed in the fact table. The schema for such databases resembles what we refer to as a snowflake schema. In such instances, a star transformation can be useful, to which end Oracle has introduced the STAR_TRANSFORMATION hint. When specified, Oracle does not guarantee that it will be used.

A requirement for the star transformation is that there be a single-column bitmap index on all foreign-key columns of the fact table that participate in the join. The star transformation progresses in two steps:

  1. Transform the original query with the join into a query with the fact table in the FROM clause and the dimension tables as subqueries in the WHERE clause to filter rows from the fact table based on the dimensions’ values or ranges. The bitmap indexes are then combined with the bitmap AND-operation to select only the rows that satisfy all dimensional constraints. The advantage is that all the dimension tables are logically joined with the fact table only once rather than once for each dimension table. Which join method is used depends on what the optimizer decides is best, although typically for large data volumes a hash join is chosen.
  2. Adjoin the rows from the dimension tables to the fact table using the best access method available to the optimizer, which is typically a full table scan because dimension tables are often relatively small.

We have said that Oracle does not always perform a star transformation, even though the STAR_TRANSFORMATION hint is specified. This is even true when all prerequisites, such as said bitmap indexes on the fact table, are met. In fact, the optimizer calculates the best plan without the transformation and only then compares it to the best plan with the transformation. Based on the costs of both plans, it picks one, which may not always be the one with the transformation enabled. One such case is when a large fraction of the rows in the fact table need to be fetched, for instance because the constraints on the dimension tables are not selective enough. It is then often advantageous to do a full table scan with multi-block reads.

Most of the time, database developers are told that bind variables are the key to great performance. When your query has bind variables, the star transformation will never be used though.

Another instance when star transformations are never applied is when fact tables are accessed remotely, that is through a database link. Dimension tables may, however, be on different Oracle database instances.

Anti-joins, fact tables that are unmerged or partitioned views, and dimension tables that appear both in the FROM clause and as subqueries in the WHERE clause are a few other party poopers for the star transformation.

The FACT( tab_name_or_alias ) hint can be used to inform the optimizer which table should be considered the fact table. NO_FACT is exactly the opposite.


[1]We have no intention of starting a debate on the data model paradigms of Kimball and Inmon. The interested reader will find plenty of insightful articles on the internet.