Join Elimination: Difference in Oracle 11g and 12c

Join elimination is a query transformation of the Oracle optimizer to remove joins that are not required in a query. A very practical features for star schema queries as well as ETL processes in data warehouses, but it is important to know what preconditions must be satisfied. And good to know: The behavior is different in Oracle 12c than in previous database versions.

The following query on a star schema joins a fact table SALES with the three dimension tables PRODUCTS, CUSTOMERS and TIMES:

SELECT p.prod_category_desc
     , SUM(s.amount_sold)
  FROM sales s
  JOIN products p ON (s.prod_id = p.prod_id)
  JOIN customers c ON (s.cust_id = c.cust_id)
  JOIN times t ON (s.time_id = t.time_id)
 WHERE t.calendar_month_desc = '2015-06'
GROUP BY p.prod_category_desc

When we have a look at the execution plan, we can see that the dimension table CUSTOMERS does not appear. Why? The optimizer eliminated the join on this dimension table because there is no need to read any customer information. This feature is called join elimination.

---------------------------------------------------------------
| Id  | Operation                            | Name           |
---------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |
|   1 |  HASH GROUP BY                       |                |
|*  2 |   HASH JOIN                          |                |
|   3 |    TABLE ACCESS FULL                 | PRODUCTS       |
|   4 |    NESTED LOOPS                      |                |
|   5 |     NESTED LOOPS                     |                |
|*  6 |      TABLE ACCESS FULL               | TIMES          |
|   7 |      PARTITION RANGE ITERATOR        |                |
|   8 |       BITMAP CONVERSION TO ROWIDS    |                |
|*  9 |        BITMAP INDEX SINGLE VALUE     | SALES_TIME_BIX |
|  10 |     TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |
---------------------------------------------------------------

Preconditions for Join Elimination

To enable join elimination, the relationships between the tables must be implemented with foreign key constraints. If the constraints are missing, most of the query transformations are not possible at all. Therefore it is highly recommended to use constraints. “Stand-alone” tables without relationships are not a recommended way to implement a physical data model.

In data warehouses, a common approach is to define the foreign key constraints with DISABLE NOVALIDATE. In this case, the constraints are visible in the database and can be used for documentation and reverse engineering of the data model, but also for query transformations like join elimination and query rewrite. But the data loaded by the ETL processes is not checked against the constraints. Therefore, data consistency has to be guaranteed by the ETL processes.

When a constraint is not validated, it should be defined as reliable in a data warehouse. This is required if query rewrite on materialized view is used, but it is a precondition for join elimination, too. The execution plan above only works on the SH demo schema, if the foreign key constraints on the SALES table are set to RELY:

ALTER TABLE sales MODIFY constraint sales_channel_fk RELY;
ALTER TABLE sales MODIFY constraint sales_time_fk RELY;
ALTER TABLE sales MODIFY constraint sales_product_fk RELY;
ALTER TABLE sales MODIFY constraint sales_customer_fk RELY;
ALTER TABLE sales MODIFY constraint sales_promo_fk RELY;

With the RELY option, join elimination works when the foreign key constraints are set to NOVALIDATE, even when they are DISABLED. At least in Oracle 11g.

Different Behavior in Oracle 12c

The previous example was executed on an Oracle 11.2.0.4 database. In Oracle 12.1.0.2, the execution plan looks slightly different: An additional join on CUSTOMER (in this case an index-only scan on the primary key index CUSTOMERS_PK) is performed. Or in other words: the join elimination does not work.

----------------------------------------------------------------
| Id  | Operation                             | Name           | 
----------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                | 
|   1 |  HASH GROUP BY                        |                | 
|   2 |   NESTED LOOPS                        |                | 
|*  3 |    HASH JOIN                          |                | 
|   4 |     TABLE ACCESS FULL                 | PRODUCTS       | 
|   5 |     NESTED LOOPS                      |                | 
|   6 |      NESTED LOOPS                     |                | 
|*  7 |       TABLE ACCESS FULL               | TIMES          | 
|   8 |       PARTITION RANGE ITERATOR        |                | 
|   9 |        BITMAP CONVERSION TO ROWIDS    |                | 
|* 10 |         BITMAP INDEX SINGLE VALUE     | SALES_TIME_BIX | 
|  11 |      TABLE ACCESS BY LOCAL INDEX ROWID| SALES          | 
|* 12 |    INDEX UNIQUE SCAN                  | CUSTOMERS_PK   | 
----------------------------------------------------------------

In Oracle 12c, join elimination in combination with NOVALIDATE works only, if the parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED. See the corresponding note in Oracle Database Reference 12c Release 1:
If a foreign key constraint is in NOVALIDATE state, join elimination is not done when QUERY_REWRITE_INTEGRITY=enforced. This means that queries with joins over a foreign key constraint that is in RELY NOVALIDATE state can potentially take longer to parse and execute as the optimizer does not trust the RELY.

The parameter QUERY_REWRITE_INTEGRITY is mainly used for query rewrite in combination with materialized views. But since Oracle 12c, it is also relevant to enable join elimination. This makes sense, but the behavior is not the same as in Oracle 11g anymore.

Test Case

The following test case can be used to see the different behavior in Oracle 11g and 12c:

CREATE TABLE t1 (pk NUMBER NOT NULL
                ,CONSTRAINT t1_pk PRIMARY KEY (pk) RELY);
CREATE TABLE t2 (fk NUMBER NOT NULL
                ,CONSTRAINT t2_fk FOREIGN KEY (fk)
                 REFERENCES t1 RELY DISABLE NOVALIDATE);

SET AUTOTRACE TRACEONLY EXPLAIN

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=ENFORCED;

SELECT t2.fk FROM t1 JOIN t2 ON t1.pk = t2.fk;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

SELECT t2.fk FROM t1 JOIN t2 ON t1.pk = t2.fk;

SET AUTOTRACE OFF
DROP TABLE t2 PURGE;
DROP TABLE t1 PURGE;

In Oracle 11.2.0.4, join elimination works for both queries (ENFORCED and TRUSTED), i.e. it is independent of the parameter QUERY_REWRITE_INTEGRITY.

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T2   |
----------------------------------

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T2   |
----------------------------------

In Oracle 12.1.0.2, join elimination works only if the parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED.

----------------------------------
| Id  | Operation         | Name |
----------------------------------
|   0 | SELECT STATEMENT  |      |
|   1 |  TABLE ACCESS FULL| T2   |
----------------------------------

------------------------------------
| Id  | Operation          | Name  |
------------------------------------
|   0 | SELECT STATEMENT   |       |
|   1 |  NESTED LOOPS      |       |
|   2 |   TABLE ACCESS FULL| T2    |
|*  3 |   INDEX UNIQUE SCAN| T1_PK |
------------------------------------

Summary

Foreign key constraints in data warehouses are often defined with NOVALIDATE. To enable query transformations such as join elimination and query rewrite, the constraints have to be set to reliable with the RELY keyword. Additionally, you have to take care that the parameter QUERY_REWRITE_INTEGRITY is set to TRUSTED on an Oracle 12c environment, otherwise join elimination will not work anymore. This setting is recommended anyway because of query rewrite on materialized views, but if you leave the parameter on the default value ENFORCED, this could be a possible reason for bad execution plans after a migration from 11g to 12c.

In OLTP system where all constraints are usually validated, there is no difference between Oracle 11g and 12c.