Skip to Main Content


Question and Answer

Chris Saxon

Thanks for the question, ankit.

Asked: June 13, 2016 - 12:43 pm UTC

Last updated: February 06, 2024 - 11:26 am UTC

Version: oracle 11g

Viewed 100K+ times! This question is

You Asked

Hi Tom,

when we use materalized hint in with clause that data is stored in GTT(Global temporary table). In GTT data is release either when commit happens or
when session ends.

I also read in one of your page that suppose 100 users are using the query that contains with clause, query runs 100 times not like materalized views that will run only once.

My question is:
1.) If the query runs every time when the user uses it then how materalized hint helps in faster performance. For every user oracle has to go to db and get the data.

Please answer the question and if my understanding is wrong please correct it.

Thanks in advance.

and Chris said...

The materialize hint forces Oracle Database to process and store the results of the query.

This can be useful if the query is expensive and you refer to it many times. For example here I'll create a function that takes one second to execute:

create or replace function f ( p int )
  return int as
  dbms_lock.sleep (
    1 ) ;
  return p;
end f;

Then I'll have a three row query. This will call the function for each row. I'll join the query to itself using inline views. This will take at least 6s to process (3 rows x 2 queries = 6):

SQL> select * from (
  2     select f(rownum) v
  3     from dual connect by level <= 3
  4  ) r1 join (
  5     select f(rownum) v
  6     from dual connect by level <= 3
  7  ) r2
  8  on    r1.v = r2.v;

Elapsed: 00:00:09.32

Doing the same thing twice like this is a waste of effort. So we can put the query in the with clause. To ensure that Oracle only runs this once, I've added the materialize hint:

SQL> with rws as (
  2    select /*+ materialize */f(rownum) v
  3    from dual connect by level <= 3
  4  )
  5    select * from rws r1
  6    join  rws r2
  7    on    r1.v = r2.v;

Elapsed: 00:00:04.25

Execution Plan
Plan hash value: 2390486683

| Id  | Operation                       | Name                      |
|   0 | SELECT STATEMENT                |                           |
|   1 |  TEMP TABLE TRANSFORMATION      |                           |
|   2 |   LOAD AS SELECT                | SYS_TEMP_0FD9D678C_E91E50 |
|   3 |    COUNT                        |                           |
|*  4 |     CONNECT BY WITHOUT FILTERING|                           |
|   5 |      FAST DUAL                  |                           |
|*  6 |   HASH JOIN                     |                           |
|   7 |    VIEW                         |                           |
|   8 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D678C_E91E50 |
|   9 |    VIEW                         |                           |
|  10 |     TABLE ACCESS FULL           | SYS_TEMP_0FD9D678C_E91E50 |

Notice it only takes 4s vs. 9s for the original query. This is because it only executes the function 3 times instead of 6. So this can help if you have an "expensive" subquery you'll refer to multiple times in one SQL statement.

To prevent this, use the inline hint:

SQL> with rws as (
  2    select /*+ inline */f(rownum) v
  3    from dual connect by level <= 3
  4  )
  5    select * from rws r1
  6    join  rws r2
  7    on    r1.v = r2.v;

Elapsed: 00:00:10.79

Execution Plan
Plan hash value: 155859586

| Id  | Operation                       |
|   0 | SELECT STATEMENT                |
|*  1 |  HASH JOIN                      |
|   2 |   VIEW                          |
|   3 |    COUNT                        |
|   5 |      FAST DUAL                  |
|   6 |   VIEW                          |
|   7 |    COUNT                        |
|   9 |      FAST DUAL                  |

No temporary table and up to 10s execution time.

Note that both these hints are undocumented (though they are referred to in some support notes). Use with caution!


  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment


ankit mongia, June 13, 2016 - 3:55 pm UTC

Hi Tom,

Thanks for the answer, but i still have the doubt.

suppose if 1st user looged in , query will run, oracle will hit the db and get the result which will be stored in memory.

if i remove the with clause and run the query and still oracle will go to db and stores data in the memory.

Now my question is in both the case oracle is going to hit the db, then how materalixed view is faster.
Chris Saxon
June 13, 2016 - 4:14 pm UTC

Are you asking about materialized views (MV) or the materialize hint? These are different things!

For MV, imagine you have this query:

select count(*) from a_really_really_big_table;

This is going to take a while to process. Even if the data is in the buffer cache. But if you create the materialized view:

create materialized view mv as 
  select count(*) from a_really_really_big_table;

This precomputes the result and saves it to disk. Now there's only one row to read. This is going to be a lot quicker than the millions/billions/trillions in a_really_really_big_table.

For the hint, the result is local to each execution of the query. This can help if you have one subquery that you'll reference many times in the same query. But it won't help different users running the same query.

ankit mongia, June 13, 2016 - 4:56 pm UTC

Hi Tom,

I am talking about materalize hint here.

suppose this is my query
with temp as
select /*+ materialize */ * from table a
select temp.* from temp join b

if the user run the above query , the select query inside the with clause will bring data from db and put in GTT.

another way to write this is
select temp.* from temp,b where;

In this also oracle will go to db and fetch data.

What is the advantage with putting data in GTT??

Chris Saxon
June 14, 2016 - 3:56 am UTC

Each user will get their own local copy if they use a materalize hint... it is not shared.

This might be about

a) repeated executions

For example, your query might be:

with t as ( select .... )
select *
from t
union all
select * from t, t1, t2
where x not in ( select * from t )
and exists ( select y from t )

etc etc...

In such cases, it *might* be better to materialize t once.

b) better optimization

even if its just a one-off execution, it might just be the case that materializing that part of the query makes things faster. So rather than rely on the optimizer working that out every time, you can use the hint.

A reader, June 22, 2020 - 5:19 pm UTC

Use materialize hint results with PL/SQL Cursors

A reader, June 27, 2022 - 4:05 am UTC


We have 2 cursors which use WITH and the SQL in the WITH section is the same. No instead of executing the same SQL twice for each cursor was wondering if there is a way in which the query results from the last execution be shared with the next cursor?

Since materialze hint create a temp table can it be leveraged here? Or maybe pull the details of the common SQL into a row type array?


Chris Saxon
June 27, 2022 - 12:47 pm UTC

I'm unclear what you're looking for here - saving the results across two separate executions?

Please ask a new question giving an overview of what you're trying to do.

Thank you!! I did not know.

Nicholas Swandel, July 13, 2023 - 7:43 pm UTC

I cant believe I have not discovered /*+ materialize */.

So useful for me in many places!

Reminds me to go do a review of HINTS occasionally, because one uses the HINTS one knows and forgets about the others.
Chris Saxon
July 14, 2023 - 1:39 pm UTC

Well it is undocumented! As with all undocumented features, there's a risk it'll change/be removed without warning.


Ċ½ilvinas Vidmantas, January 30, 2024 - 12:47 pm UTC

Writing your result set to disk and clearing it after executing SQL query is additional job. So it should be slower than keeping it in memory. Most of the times people use this hint when Oracle chooses not optimal plan and materialize hint makes query to run faster. But if you find a way to hint query with other hints (goal is to get the same execution plan as with materialize but without writing it to disk) it will be faster. For me no_merge hint instead of materialize does the job almost in all cases. When subquery is used more then once, Oracle creates temporary table even without adding materialize hint. At least in my experience it was always true. It is not documented hint and can be removed in subsequent Oracle versions. I used it quite often in my early days but already many years I find better and faster documented hints.
Chris Saxon
February 06, 2024 - 11:26 am UTC

True - using no_merge often gives the same end result with the added bonus of being a documented hint!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library