Oracle Hint inline materialize

Source: Internet
Author: User
Tags sorts

When we use with, Oracle may convert the results in with into a temporary table. This is only possible, as the CBO will infer.

Inline is not converted to a temporary table. Materialize is forced 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
Run a 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
Run a 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
Run a 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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.