WITH clause and hints MATERIALIZE and INLINE

WITH clause is commonly used to simplify SQL statements. This article shows impact of dedicated hints MATERIALIZE and INLINE for processing data with WITH clause.

Let’s prepare some dummy data

DROP TABLE test_tbl;

CREATE TABLE test_tbl
(
  id1        NUMBER,
  id2        NUMBER
);

INSERT INTO test_tbl
SELECT LEVEL, LEVEL FROM dual
CONNECT BY LEVEL < 5;

INSERT INTO test_tbl
SELECT LEVEL, LEVEL FROM dual
CONNECT BY LEVEL < 5;

SELECT * FROM test_tbl;

       ID1        ID2
---------- ----------
         1          1
         2          2
         3          3
         4          4
         1          1
         2          2
         3          3
         4          4

Instead of writing complex query that can be hard to read it’s better to use WITH clause

SELECT * 
  FROM 
    test_tbl A,
    ( SELECT id1, sum(id2) 
        FROM test_tbl
      GROUP BY id1 ) b
WHERE A.id1 = b.id1

so above sql looks much simpler for us humans to read

WITH 
  with_b AS
  (
    SELECT id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b
WHERE A.id1 = b.id1;

If you look on explain plan for above query there is nothing special here simple HASH JOIN

explain plan for 
WITH 
  with_b AS
  (
    SELECT id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b
WHERE A.id1 = b.id1;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 2545341406
 
-------------------------------------------------
| Id  | Operation                    | Name     |
-------------------------------------------------
|   0 | SELECT STATEMENT             |          |
|   1 |  HASH JOIN                   |          |
|   2 |   TABLE ACCESS STORAGE FULL  | TEST_TBL |
|   3 |   VIEW                       |          |
|   4 |    HASH GROUP BY             |          |
|   5 |     TABLE ACCESS STORAGE FULL| TEST_TBL |
-------------------------------------------------

However dedicated hint MATERIALIZE can change it

MATERIALIZE hint

This hint is very nice extension for WITH clause. It forces Oracle to transform data generated by particular WITH into temporary table.

explain plan for 
WITH 
  with_b AS
  (
    SELECT /*+ MATERIALIZE */ id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b
WHERE A.id1 = b.id1;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 4078199279
 
--------------------------------------------------------------------
| Id  | Operation                    | Name                        |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                             |
|   1 |  TEMP TABLE TRANSFORMATION   |                             |
|   2 |   LOAD AS SELECT             | SYS_TEMP_0FDA22B04_93A119B8 |
|   3 |    HASH GROUP BY             |                             |
|   4 |     TABLE ACCESS STORAGE FULL| TEST_TBL                    |
|   5 |   HASH JOIN                  |                             |
|   6 |    TABLE ACCESS STORAGE FULL | TEST_TBL                    |
|   7 |    VIEW                      |                             |
|   8 |     TABLE ACCESS STORAGE FULL| SYS_TEMP_0FDA22B04_93A119B8 |
---------------------------------------------------------------------

In our case it won’t help but It may be useful when WITH is reused many times in sql. In most cases Oracle optimizer can notice that a WITH is used many times so the transformation is done automatically without adding MATERIALIZE hint.

explain plan for 
WITH 
  with_b AS
  (
    SELECT id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b, with_b c
WHERE A.id1 = b.id1
  AND A.id1 = c.id1;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 3866508735
 
---------------------------------------------------------------------
| Id  | Operation                     | Name                        |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FDA22B05_93A119B8 |
|   3 |    HASH GROUP BY              |                             |
|   4 |     TABLE ACCESS STORAGE FULL | TEST_TBL                    |
|   5 |   HASH JOIN                   |                             |
|   6 |    HASH JOIN                  |                             |
|   7 |     TABLE ACCESS STORAGE FULL | TEST_TBL                    |
|   8 |     VIEW                      |                             |
|   9 |      TABLE ACCESS STORAGE FULL| SYS_TEMP_0FDA22B05_93A119B8 |
|  10 |    VIEW                       |                             |
|  11 |     TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA22B05_93A119B8 |
---------------------------------------------------------------------

INLINE hint

This hint disables automatic transformations to temp tables forced by Oracle optimizer and  each WITH is processed in line.

explain plan for 
WITH 
  with_b AS
  (
    SELECT id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b, with_b c
WHERE A.id1 = b.id1
  AND A.id1 = c.id1;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 3866508735
 
---------------------------------------------------------------------
| Id  | Operation                     | Name                        |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                             |
|   1 |  TEMP TABLE TRANSFORMATION    |                             |
|   2 |   LOAD AS SELECT              | SYS_TEMP_0FDA22B05_93A119B8 |
|   3 |    HASH GROUP BY              |                             |
|   4 |     TABLE ACCESS STORAGE FULL | TEST_TBL                    |
|   5 |   HASH JOIN                   |                             |
|   6 |    HASH JOIN                  |                             |
|   7 |     TABLE ACCESS STORAGE FULL | TEST_TBL                    |
|   8 |     VIEW                      |                             |
|   9 |      TABLE ACCESS STORAGE FULL| SYS_TEMP_0FDA22B05_93A119B8 |
|  10 |    VIEW                       |                             |
|  11 |     TABLE ACCESS STORAGE FULL | SYS_TEMP_0FDA22B05_93A119B8 |
---------------------------------------------------------------------

explain plan for 
WITH 
  with_b AS
  (
    SELECT /*+ INLINE */ id1, sum(id2) 
      FROM test_tbl
   GROUP BY id1
  )
SELECT * 
  FROM 
    test_tbl a, with_b b, with_b c
WHERE A.id1 = b.id1
  AND A.id1 = c.id1;

SELECT * FROM TABLE(dbms_xplan.display(format=>'BASIC'));

Plan hash value: 3996169239
 
--------------------------------------------------
| Id  | Operation                     | Name     |
--------------------------------------------------
|   0 | SELECT STATEMENT              |          |
|   1 |  HASH JOIN                    |          |
|   2 |   HASH JOIN                   |          |
|   3 |    TABLE ACCESS STORAGE FULL  | TEST_TBL |
|   4 |    VIEW                       |          |
|   5 |     HASH GROUP BY             |          |
|   6 |      TABLE ACCESS STORAGE FULL| TEST_TBL |
|   7 |   VIEW                        |          |
|   8 |    HASH GROUP BY              |          |
|   9 |     TABLE ACCESS STORAGE FULL | TEST_TBL |
--------------------------------------------------

INLINE hint may be useful for OLTP systems.

Session force

Another option to control materialization is to use hidden parameter “_with_subquery

ALTER SESSION SET "_with_subquery" = materialize;

Possible values are

  • optimizer: Let the optimizer choose – the default mode
  • materialize: Always materialize
  • inline: Always inline

Have a fun 🙂

Tomasz

2 thoughts on “WITH clause and hints MATERIALIZE and INLINE

Leave a Reply to Jacek P. Cancel reply

Your email address will not be published. Required fields are marked *