Oracle hint inline materialize, inlinematerialize

Oracle hint inline materialize, inlinematerialize

When we use with, oracle may convert the results in with to a temporary table, which is only possible because CBO will judge. Inline is not converted to a temporary table, and materialize is forcibly converted to 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;
Row 1931 has been selected.
Used time: 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 ")
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1173 consistent gets
0 physical reads
0 redo size
139087 bytes sent via SQL * Net to client
1768 bytes encoded ed via SQL * Net from client
130 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1931 rows processed

Use 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;
Row 1931 has been selected.
Used time: 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 ")
Statistics
----------------------------------------------------------
1 recursive cballs
0 db block gets
1173 consistent gets
0 physical reads
0 redo size
139087 bytes sent via SQL * Net to client
1768 bytes encoded ed via SQL * Net from client
130 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1931 rows processed

Use 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;

Row 1931 has been selected.
Used time: 00: 00: 00.21
Execution Plan
----------------------------------------------------------
Plan hash value: 1492452360
Bytes ----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------------------------
| 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 |
Bytes ----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
3-access ("T1". "OBJECT_ID" = "T2". "OBJECT_ID ")
6-access ("T". "OBJECT_ID" = "T3". "OBJECT_ID ")
Statistics
----------------------------------------------------------
394 recursive cballs
25 db block gets
1243 consistent gets
18 physical reads
600 redo size
139087 bytes sent via SQL * Net to client
1768 bytes encoded ed via SQL * Net from client
130 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1931 rows processed