Oracle Hint inline materialize

When we use with, Oracle may convert the results in with into a temporary table, which is just possible because the CBO will judge. Inline is not converted to a temporary table, and materialize is coerced into a temporary table.

Manufacturing data

drop table Test1 Purge;

drop table Test2 purge;
drop table Test3 Purge;
CREATE TABLE Test1 as SELECT * from Dba_objects;
CREATE TABLE Test2 as SELECT * from User_objects;
CREATE TABLE Test3 as SELECT * from Dba_objects;
exec dbms_stats.gather_table_stats (user, ' test1 ');
exec dbms_stats.gather_table_stats (user, ' test2 ');

exec dbms_stats.gather_table_stats (user, ' test3 ');

Sql> select * from V$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-production
CORE 11.2.0.1.0 Production
TNS for Linux:version 11.2.0.1.0-production
Nlsrtl Version 11.2.0.1.0-production

Sql> Set Autotrace traceonly

Sql> with T as (select t1.* from Test1 t1,test2 T2
where t1.object_id=t2.object_id)
SELECT * from T,test3 T3 where t.object_id=t3.object_id;
1931 rows have been selected.
Time used: 00:00:00.20
Execution plan
----------------------------------------------------------
Plan Hash value:1215971386
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |  |   1931 |   382k| 409 (2) | 00:00:06 |
|* 1 |       HASH JOIN |  |   1931 |   382k| 409 (2) | 00:00:06 |
|* 2 |       HASH JOIN |  |   1932 |   196k| 210 (2) | 00:00:03 |
|    3 | TABLE ACCESS full|  TEST2 |  1934 |    9670 | 10 (0) | 00:00:01 |
|    4 | TABLE ACCESS full| TEST1 |  71347 |   6897k| 199 (2) | 00:00:03 |
|   5 | TABLE ACCESS Full | TEST3 |  71349 |   6897k| 199 (2) | 00:00:03 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-access ("T1". object_id "=" T3 "." OBJECT_ID ")
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
1173 Consistent gets
0 physical Reads
0 Redo Size
139087 Bytes sent via sql*net to client
1768 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1931 Rows processed

using hint inline
Sql> with T as (select/*+inline*/t1.* from Test1 T1,test2 T2
where t1.object_id=t2.object_id)
SELECT * from T,test3 T3 where t.object_id=t3.object_id;
1931 rows have been selected.
Time used: 00:00:00.21
Execution plan
----------------------------------------------------------
Plan Hash value:1215971386
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-----------------------------------------------------------------------------
| 0 |       SELECT STATEMENT |  |   1931 |   382k| 409 (2) | 00:00:06 |
|* 1 |       HASH JOIN |  |   1931 |   382k| 409 (2) | 00:00:06 |
|* 2 |       HASH JOIN |  |   1932 |   196k| 210 (2) | 00:00:03 |
|    3 | TABLE ACCESS full|  TEST2 |  1934 |    9670 | 10 (0) | 00:00:01 |
|    4 | TABLE ACCESS full| TEST1 |  71347 |   6897k| 199 (2) | 00:00:03 |
|   5 | TABLE ACCESS Full | TEST3 |  71349 |   6897k| 199 (2) | 00:00:03 |
-----------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
1-access ("T1". object_id "=" T3 "." OBJECT_ID ")
2-access ("T1". object_id "=" T2 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
1 Recursive calls
0 db Block gets
1173 Consistent gets
0 physical Reads
0 Redo Size
139087 Bytes sent via sql*net to client
1768 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1931 Rows processed

Using hint materialize
Sql> with T as (select /*+materialize*/t1.* from test1 t1,test2 T2
where t1.object_id=t2.object_id)
SELECT * from T,test3 T3 where t.object_id=t3.object_id;

1931 rows have been selected.
Time used: 00:00:00.21
Execution plan
----------------------------------------------------------
Plan Hash value:1492452360
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
----------------------------------------------------------------------------------------------------------
| 0 |                             SELECT STATEMENT |  |   1925 |   575k| 416 (2) | 00:00:06 |
|  1 |                             TEMP TABLE Transformation |       |       |            |       | |
|   2 | LOAD as SELECT |Sys_temp_0fd9d660c_9a3a2aea|       |            |       | |
|* 3 |                             HASH JOIN |  |   1932 |   196k| 210 (2) | 00:00:03 |
|     4 | TABLE ACCESS Full |  TEST2 |  1934 |    9670 | 10 (0) | 00:00:01 |
|     5 | TABLE ACCESS Full | TEST1 |  71347 |   6897k| 199 (2) | 00:00:03 |
|* 6 |                             HASH JOIN |  |   1925 |   575k| 207 (2) | 00:00:03 |
|    7 |                             VIEW |  |   1932 |     390k| 7 (0) | 00:00:01 |
|     8 | TABLE ACCESS Full |Sys_temp_0fd9d660c_9a3a2aea|   1932 |     196k| 7 (0) | 00:00:01 |
|    9 | TABLE ACCESS Full | TEST3 |  71349 |   6897k| 199 (2) | 00:00:03 |
----------------------------------------------------------------------------------------------------------
predicate information (identified by Operation ID):
---------------------------------------------------
3-access ("T1". object_id "=" T2 "." OBJECT_ID ")
6-access ("T". " object_id "=" T3 "." OBJECT_ID ")
Statistical information
----------------------------------------------------------
394 Recursive calls
DB Block gets
1243 consistent gets
Physical Reads
Redo Size
139087 Bytes sent via sql*net to client
1768 bytes received via sql*net from client
Sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
1931 Rows processed

Oracle hint inline materialize