There are many good reasons to use ANSI join syntax instead of the old Oracle join syntax. With one exception: If you want to use your Materialized Views for Query Rewrite, avoid to write them with ANSI joins.

I’m a big fan of the ANSI join syntax. Since years I prefer to use it instead of the old-fashioned Oracle syntax with join conditions written in the WHERE condition. From my point of view, the ANSI syntax is easier to read, supports a clear distinction between join conditions and filter conditions, and allows more flexibility with outer joins. But when I prepared the demo scripts for my DOAG 2016 presentations, I was once more reminded of a strange behavior in Oracle: Materialized Views must be written with the old Oracle join syntax, otherwise Query Rewrite will not work properly.

To illustrate this, I create a Materialized View (once with ANSI and once with Oracle join syntax) and execute different queries that should be able to use Query Rewrite.

Setup the Test Environment

For the test case, I use the Sales History (SH) demo schema on an Oracle 12.1.0.2 EE database. The foreign keys between fact table SALES and the dimension tables TIMES and PRODUCTS are defined as reliable constraints (see previous blog post). The following parameters are set to enable advanced Query Rewrite:

ALTER SESSION SET query_rewrite_enable = true;

ALTER SESSION SET query_rewrite_integrity = trusted;

First, I create a Materialized View on the tables SALES, TIMES and PRODUCTS with the following statement:

CREATE MATERIALIZED VIEW mv_product_month_sales

ENABLE QUERY REWRITE

AS

SELECT t.calendar_month_desc

, p.prod_id

, SUM(s.amount_sold)

, COUNT(s.amount_sold)

, COUNT(*)

FROM sales s

JOIN times t ON t.time_id = s.time_id

JOIN products p ON p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc, p.prod_id

Please notice: The Materialized View is created with ANSI join syntax.

Test Queries: The Frustrating Part

Now, I run several test queries, all of them written with ANSI join syntax. The first query looks similar to the definition of the Materialized View, but does not contain all attributes. In this case, Query Rewrite with partial text match should be possible.

SELECT t.calendar_month_desc

, p.prod_id

, SUM(s.amount_sold)

FROM sales s

JOIN times t ON t.time_id = s.time_id

JOIN products p ON p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc, p.prod_id

As you can see in the execution plan, the query is rewritten – perfect!

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 3188 |

| 1 | MAT_VIEW REWRITE ACCESS FULL | MV_PRODUCT_MONTH_SALES | 3188 |

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

The second query contains additional attributes of the PRODUCTS dimension. In this case, a general Query Rewrite with a join back on the dimension table should occur.

SELECT t.calendar_month_desc

, p.prod_name

, p.prod_subcategory

, p.prod_category

, SUM(s.amount_sold) AS amount_sold

FROM sales s

JOIN times t ON t.time_id = s.time_id

JOIN products p ON p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc

, p.prod_name

, p.prod_subcategory

, p.prod_category

Unfortunately, this is not the case. The execution plan shows that the Materialized View is not used for this query:

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

| Id | Operation | Name | Rows |

——————————————————————————————----------------------------------------------------------

| 0 | SELECT STATEMENT | | 3055 |

| 1 | HASH GROUP BY | | 3055 |

|* 2 | HASH JOIN | | 3055 |

| 3 | TABLE ACCESS FULL | PRODUCTS | 72 |

| 4 | VIEW | VW_GBC_9 | 3055 |

| 5 | HASH GROUP BY | | 3055 |

|* 6 | HASH JOIN | | 918K|

| 7 | PART JOIN FILTER CREATE | :BF0000 | 1826 |

| 8 | TABLE ACCESS FULL | TIMES | 1826 |

| 9 | PARTITION RANGE JOIN-FILTER| | 918K|

| 10 | TABLE ACCESS FULL | SALES | 918K|

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

The third query joins only the tables SALES and PRODUCTS, but not TIMES. In this case, a materialized view delta join should be performed. Because of the lossless join to the TIMES dimension, it is possible to do a Query Rewrite, even if this dimension table is not part of the query.

SELECT p.prod_name

, SUM(s.amount_sold) AS amount_sold

FROM sales s

JOIN products p ON p.prod_id = s.prod_id

GROUP BY p.prod_name

The execution plan shows something else: Here again, the Materialized View is not used, the query is executed on the base tables:

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 71 |

| 1 | HASH GROUP BY | | 71 |

|* 2 | HASH JOIN | | 72 |

| 3 | VIEW | VW_GBC_5 | 72 |

| 4 | HASH GROUP BY | | 72 |

| 5 | PARTITION RANGE ALL| | 918K|

| 6 | TABLE ACCESS FULL | SALES | 918K|

| 7 | TABLE ACCESS FULL | PRODUCTS | 72 |

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

At this point, I was very disappointed because I was sure that Query Rewrite is possible for all these queries. I used such statements and more complex ones in different customer projects and training demos, and I never had troubles with Query Rewrite. Even further analysis with dbms_mview.explain_mview and dbms_mview.explain_rewrite did not help to find an explanation. Finally, I compared my test case with a demo script from the O-DWH training and discovered a small, but important difference: In the demo script, the Materialized View was created with Oracle join syntax. Aaaaaah! I know that since years, but always forget about this strange behavior.

Recreate the Materialized View

Let’s drop and recreate the Materialized View, but this time with the Oracle join syntax:

DROP MATERIALIZED VIEW mv_product_month_sales;

CREATE MATERIALIZED VIEW mv_product_month_sales

ENABLE QUERY REWRITE

AS

SELECT t.calendar_month_desc

, p.prod_id

, SUM(s.amount_sold)

, COUNT(s.amount_sold)

, COUNT(*)

FROM sales s

, times t

, products p

WHERE t.time_id = s.time_id

AND p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc, p.prod_id

Test Queries: The Successful Part

Now, I execute the same queries again. And what a surprise: All queries are rewritten as expected!

SELECT t.calendar_month_desc

, p.prod_id

, SUM(s.amount_sold)

FROM sales s

JOIN times t ON t.time_id = s.time_id

JOIN products p ON p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc, p.prod_id

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 3188 |

| 1 | MAT_VIEW REWRITE ACCESS FULL | MV_PRODUCT_MONTH_SALES | 3188 |

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

SELECT t.calendar_month_desc

, p.prod_name

, p.prod_subcategory

, p.prod_category

, SUM(s.amount_sold) AS amount_sold

FROM sales s

JOIN times t ON t.time_id = s.time_id

JOIN products p ON p.prod_id = s.prod_id

GROUP BY t.calendar_month_desc

, p.prod_name

, p.prod_subcategory

, p.prod_category

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

| Id | Operation | Name | Rows |

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

| 0 | SELECT STATEMENT | | 2444 |

| 1 | HASH GROUP BY | | 2444 |

|* 2 | HASH JOIN | | 2444 |

| 3 | TABLE ACCESS FULL | PRODUCTS | 72 |

| 4 | VIEW | VW_GBC_5 | 2444 |

| 5 | HASH GROUP BY | | 2444 |

| 6 | MAT_VIEW REWRITE ACCESS FULL | MV_PRODUCT_MONTH_SALES | 3188 |

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

SELECT p.prod_name

, SUM(s.amount_sold) AS amount_sold

FROM sales s

JOIN products p ON p.prod_id = s.prod_id

GROUP BY p.prod_name

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

| Id | Operation | Name | Rows |

————————————————————————————————————----------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 71 |

| 1 | HASH GROUP BY | | 71 |

|* 2 | HASH JOIN | | 3188 |

| 3 | TABLE ACCESS FULL | PRODUCTS | 72 |

| 4 | MAT_VIEW REWRITE ACCESS FULL | MV_PRODUCT_MONTH_SALES | 3188 |

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

The first query is rewritten in the same way as before: with a partial text match. The second query uses the Materialized View as well and does a join back to the PRODUCTS dimension. For the third query, a materialized view delta join is performed. So, for all test cases, Query Rewrite works as expected. And by the way: The queries are still the same as before, so all of them are written with ANSI join syntax. Only the Materialized View was changed.

Summary and Recommendation

As you can see in these examples, the join syntax used in a Materialized View definition has an impact on the capability of Query Rewrite. If the Materialized View is written with ANSI join syntax, only full and partial text matching is possible, but all advanced Query Rewrite mechanisms do not work. If the Materialized View is written with Oracle join syntax, Query Rewrite works as expected. The join syntax of the queries does not matter, the statemets can be written in ANSI or Oracle join syntax.

The following overview gives a summary of all test cases:

My recommendation: Always use Oracle join syntax in Materialized Views, if you plan to use them for Query Rewrite. In all other cases, I prefer the ANSI join syntax. But when I will create my next Materialized View with ANSI syntax, I hope to remember this blog post. It will help me to avoid losing a lot of time with Query Rewrite troubles.