Monday, December 10, 2018

WITH and the MATERIALIZE hint in the cloud or not


Recently in class a student mentioned that she’d be told that for the WITH clause you don’t need to use the MATERIALIZE hint anymore.  Maybe.   It turns out that if you REALLY want the subquery factor to be materialized, you do need to use it. 

Keep in mind that the optimizer sees your SQL statement as just that, a statement.  It can and will rearrange and rewrite your SQL into something it likes better.  And this means it can merge your subquery factors defined in the WITH clause into the statement.

I’ll use a simple statement to illustrate this point.   Clearly this select could (and really should) be written without a WITH clause at all.  This is just a simple example to illustrate the point.  Once you have a better understanding of how it works you can decide what is best for your particular situation.

Edit: Tests were done on an Oracle Database 12c Enterprise Edition Release 12.2.0.1.0, on a windows laptop.

SQL> -- no hints
SQL> WITH inline_view AS
  2  (
  3  SELECT /*+ qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders
  4    FROM ord2
  5   GROUP BY CUST_NO
  6  )
  7  SELECT /*+ qb_name(main) */ *
  8    FROM inline_view where total_orders > 1400000;

        CUST_NO    TOTAL_ORDERS
--------------- ---------------
           9379      1409495.95

If we run this the optimizer will merge the subquery factor and the main query together into one statement.  Which really makes sense here, the subquery factor isn’t really doing anything special. Here is the plan, notice the query block MAIN disappears completely and the only reference to the CUST_TOTALS block is the table alias:

SQL_ID  bnzfdp1yvk7vc, child number 0
-------------------------------------
WITH inline_view AS ( SELECT /*+ qb_name(cust_totals)*/ CUST_NO,
SUM(TOTAL_ORDER_PRICE) AS total_orders   FROM ord2  GROUP BY CUST_NO )
SELECT /*+ qb_name(main) */ *   FROM inline_view where total_orders >
1400000

Plan hash value: 2809195938

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |       |       |    69 (100)|          |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   HASH GROUP BY     |      |    50 |   550 |    69   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| ORD2 | 12890 |   138K|    68   (0)| 00:00:01 |
----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$8092F496
   3 - SEL$8092F496 / ORD2@CUST_TOTALS

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(SUM("TOTAL_ORDER_PRICE")>1400000)

Now to use the MATERIALIZE hint and the subquery factor defined in the WITH clause stays as a separate entity. 

SQL> -- materialize hint
SQL> WITH inline_view AS
  2  (
  3  SELECT /*+ materialize qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders
  4    FROM ord2
  5   GROUP BY CUST_NO
  6  )
  7  SELECT /*+ qb_name(main) */ *
  8    FROM inline_view where total_orders > 1400000;

        CUST_NO    TOTAL_ORDERS
--------------- ---------------
           9379      1409495.95

In the plan this time we can see the both query blocks and a new one which is the materialized data from the subquery factor. 

SQL_ID  65bmr36y814d3, child number 0
-------------------------------------
WITH inline_view AS ( SELECT /*+ materialize qb_name(cust_totals)*/
CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders   FROM ord2  GROUP BY
CUST_NO ) SELECT /*+ qb_name(main) */ *   FROM inline_view where
total_orders > 1400000

Plan hash value: 1306428065

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |       |       |    72 (100)|          |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D6653_4B0C1F7 |       |       |            |          |
|   3 |    HASH GROUP BY                         |                            |  1000 | 11000 |    69   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL                    | ORD2                       | 12890 |   138K|    68   (0)| 00:00:01 |
|*  5 |   VIEW                                   |                            |  1000 | 26000 |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL                     | SYS_TEMP_0FD9D6653_4B0C1F7 |  1000 | 11000 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN
   2 - CUST_TOTALS
   4 - CUST_TOTALS  / ORD2@CUST_TOTALS
   5 - SEL$03D1C9D1 / INLINE_VIEW@MAIN
   6 - SEL$03D1C9D1 / T1@SEL$03D1C9D1

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("TOTAL_ORDERS">1400000)

This makes it clear that if the optimizer can merge the subquery factor in to the main query it will.  The materialize hint will keep the subquery factor as a separate entity.   Bottom line is that if you want a subquery factor kept as a separate entity, and you notice that in the plan it’s being merged in, then use the materialize hint.

Which brings up another point.  Some folks will use the NO_MERGE hint to keep the subquery factor from being merged in.  This works, sort of.  Here is the same query using the NO_MERGE hint.

SQL> -- no merge hint
SQL> WITH inline_view AS
  2  (
  3  SELECT /*+ no_merge qb_name(cust_totals)*/ CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders
  4    FROM ord2
  5   GROUP BY CUST_NO
  6  )
  7  SELECT /*+ qb_name(main) */ *
  8    FROM inline_view where total_orders > 1400000;

        CUST_NO    TOTAL_ORDERS
--------------- ---------------
           9379      1409495.95

And now here is the plan.  Notice that the subquery factor isn’t completely merged in, but it does become basically an inline view instead.  This of course might be just fine and works the way you want, however it’s not a materialized version of the subquery factor, but an inline view. 

SQL_ID  6mf7u56n8bad6, child number 0
-------------------------------------
WITH inline_view AS ( SELECT /*+ no_merge qb_name(cust_totals)*/
CUST_NO, SUM(TOTAL_ORDER_PRICE) AS total_orders   FROM ord2  GROUP BY
CUST_NO ) SELECT /*+ qb_name(main) */ *   FROM inline_view where
total_orders > 1400000

Plan hash value: 2169976290

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |    69 (100)|          |
|   1 |  VIEW                |      |     1 |    26 |    69   (2)| 00:00:01 |
|*  2 |   FILTER             |      |       |       |            |          |
|   3 |    HASH GROUP BY     |      |     1 |    11 |    69   (2)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| ORD2 | 12890 |   138K|    68   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - CUST_TOTALS / INLINE_VIEW@MAIN
   2 - CUST_TOTALS
   4 - CUST_TOTALS / ORD2@CUST_TOTALS

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(SUM("TOTAL_ORDER_PRICE")>1400000)

1 comment:

  1. I was asked via Twitter what would happen if the no_query_transformation hint was used. The optimizer produces the same plan as the no_merge hint query.

    ReplyDelete