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

13 thoughts on “WITH clause and hints MATERIALIZE and INLINE”

Jacek P. on December 30, 2015 at 1:13 pm said:

Thanks Master 🙂 As always good tip from you.
All the best in New Year 🙂

Reply ↓
- admin on December 30, 2015 at 1:18 pm said:
  
  Thanks and Happy New Year 2016 !
  
  Regards
  Tomasz
  
  Reply ↓
Babu Chelika on January 4, 2018 at 8:32 am said:

super

Reply ↓
Vlad on May 19, 2018 at 5:47 pm said:

Excellent explanation!!! I found that my complex view built on top of other views with multiple WITH statements works very quickly when INLINE hint specified in one subquery. Otherwise it was hanging for hours without progress. I decided to read more about this hint, found this article and the author explained everything I wanted to know about this hint. Thanks again.

Reply ↓
Tomek on October 30, 2018 at 3:59 pm said:

Tomasz, Is is possible to give your own name for this materialized temp object (SYS_TEMP_0FDA22B05_93A119B8)?

Reply ↓
- joda3008 on October 30, 2018 at 7:33 pm said:
  
  why do you need this ? add some hints for temp objects to control explain plan read my article about QB_NAME hint
  
  http://dbaora.com/qb_name-control-execution-plans-in-views/
  
  Regards
  Tomasz
  
  Reply ↓
Bharat on December 13, 2018 at 3:10 pm said:

Very well explained with simple steps.. 🙂

Reply ↓
foxbat on August 24, 2020 at 2:41 pm said:

How can I check what is the current value of the ‘_with_subquery’ parameter?

Reply ↓
- Jaro on October 12, 2020 at 5:57 am said:
  
  use: [dbms_utility.get_parameter_value()] – e.g.:
  ”
  with
  function get_parameter_value (
  p_parnam VARCHAR2–(256)
  )
  RETURN VARCHAR IS
  — DECLARE
  parnam VARCHAR2(256);
  intval BINARY_INTEGER;
  strval VARCHAR2(256);
  partyp BINARY_INTEGER;
  parval VARCHAR2(256);
  BEGIN
  parnam := ‘SQL_TRACE’;
  parnam := ‘sql_trace’;
  parnam := p_parnam;
  partyp := dbms_utility.get_parameter_value(parnam, intval, strval);
  dbms_output.put(‘parameter value is: ‘);
  IF partyp = 1 THEN
  dbms_output.put_line(strval);
  parval:= strval;
  ELSE
  dbms_output.put_line(intval);
  parval:= intval;
  END IF;
  IF partyp = 1 THEN
  dbms_output.put(‘parameter value length is: ‘);
  dbms_output.put_line(intval);
  END IF;
  dbms_output.put(‘parameter type is: ‘);
  IF partyp = 1 THEN
  dbms_output.put_line(‘string’);
  ELSE
  dbms_output.put_line(‘integer’);
  END IF;
  return parval;
  exception
  when others then
  if sqlcode = ‘-20000’ then
  — return sqlerrm;
  return ‘invalid or unsupported parameter: ‘||'”‘||parnam||'”‘;
  end if;
  return sqlerrm;
  END;
  — select get_parameter_value (‘db_block_buffers’) from dual
  select get_parameter_value (‘cursor_sharing’) from dual
  ;
  “
  
  Reply ↓
foxbat on September 2, 2020 at 5:55 pm said:

How can I check what is current value of ‘_with_subquery’ parameter?

Reply ↓
Joseph Humbert on July 26, 2021 at 1:31 pm said:

There is a way to replace or materialize?

Reply ↓
- joda3008 on July 28, 2021 at 9:56 am said:
  
  what do you mean ?
  
  Reply ↓
Hanan on February 15, 2022 at 7:15 am said:

Gee Tomasz, your site is great!
Many many thanks.
Cheers!

Reply ↓

DBAORA

oracle expert presents

WITH clause and hints MATERIALIZE and INLINE

13 thoughts on “WITH clause and hints MATERIALIZE and INLINE”

Leave a Reply to Jacek P. Cancel reply