Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

what are lacks of using /*+ MATERIALIZE */ hint?

marcoDec 19 2011 — edited Dec 19 2011
Hi all,

I'm in process of testing heavy sql statement and found amazing feature for decreasing time of performing statement. 
with t1 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual),
       t2 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual) 
select * from t1, t2 where t1.t = t2.t
It's hard to imagine some heavy sample statement to demonstrate here, but in few words this hint in nested sql statements allows in my real case to decrease performing time from 40 min to 1,5 min!
I'm just interesting what are lacks of using this hint?

Upd:
I found that materialize hint is undocumented, so it can be deprecated or just stop working in next version of Oracle. I can use rownum in subquery with the same effect (subquery becomes materialized).
Could you please confirm my guess?

Edited by: marco on Dec 19, 2011 3:24 AM

Comments

32685
When you use the materialize hint, it instructs the optimiser to create a global temporary table and insert the data generated by the query in the with clause into it. Wherever the query is referenced in the rest of the query, it will select the results from the global temporary table instead of rerunning the statement. 
XXXX> with t1 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual),
  2         t2 as (select /*+ MATERIALIZE */ 1 as t, 2 as y from dual)
  3  select * from t1, t2 where t1.t = t2.t
  4  /

1 row selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 4216725922

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     1 |    12 |     9  (12)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           |                             |       |       |            |          |
|   3 |    FAST DUAL               |                             |     1 |       |     2   (0)| 00:00:01 |
|   4 |   LOAD AS SELECT           |                             |       |       |            |          |
|   5 |    FAST DUAL               |                             |     1 |       |     2   (0)| 00:00:01 |
|*  6 |   HASH JOIN                |                             |     1 |    12 |     5  (20)| 00:00:01 |
|   7 |    VIEW                    |                             |     1 |     6 |     2   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6608_9CC969DE |     1 |    26 |     2   (0)| 00:00:01 |
|   9 |    VIEW                    |                             |     1 |     6 |     2   (0)| 00:00:01 |
|  10 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6609_9CC969DE |     1 |    26 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   6 - access("T1"."T"="T2"."T")


Statistics
----------------------------------------------------------
        190  recursive calls
         20  db block gets
         36  consistent gets
          2  physical reads
       2864  redo size
        280  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
Line 1, 2 and 4 show the creation and load of a global temporary table, and line 8 and 10 show that the optimiser is selecting from that table ( SYS_TEMP_0FD9D6609_9CC969DE).

without the materialize hint the two queries have just been merged...
XXXX> with t1 as (select /*+  */ 1 as t, 2 as y from dual),
  2         t2 as (select /*+  */ 1 as t, 2 as y from dual)
  3  select * from t1, t2 where t1.t = t2.t
  4  /

1 row selected.

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 2479754086

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS    |      |     1 |     4   (0)| 00:00:01 |
|   2 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
|   3 |   FAST DUAL      |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        297  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
So what might be happening in your case is that the materialize hint is preventing your queries from being merged. When the queries are merged, it could be that a sub optimial plan is being chosen - and there could be many different reasons for that. The best way to find out what's happening is to examine the two execution plans and narrow down where the difference is coming from.
BluShadow
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:587908400346860711
marco
thanks to all.
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 16 2012
Added on Dec 19 2011
3 comments
111,729 views