Oracle with AS (hint inline materialize)

1. Test data:

Sql> CREATE TABLE Test1 as SELECT * from Dba_objects; Table created. Sql> CREATE TABLE Test2 as SELECT * from User_objects; Table created. Sql> CREATE TABLE Test3 as SELECT * from Dba_objects; Table created.

Collect statistics:

Begin  dbms_stats. Gather_table_stats (ownname          =>  ' SCOTT ',                                  tabname           =>  ' TEST1 ',                                  estimate_percent => 100,                                  method_opt       =>   ' For  columns owner size repeat ',        & nbsp;                         no_invalidate    => FALSE,                                  degree            => 4,                                  granularity      =>  ' All ',                                  cascade           =>&nbsP TRUE);   end;   /pl/sql procedure successfully completed. 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;11 rows selected. Execution plan----------------------------------------------------------plan hash value:  2878150729-------------------------------------------------------------------------------------| id   | Operation    | Name  | Rows  | Bytes  | tempspc| cost  (%CPU) | time     |-------------------------------------- -----------------------------------------------|   0 | select statement     |    | 86999 | 16m|    |  1605    (1) | 00:00:20 | | *  1 |  hash join    |    | 86999  | 16M|    |  1605    (1) | 00:00:20 | |    2 |   TABLE ACCESS FULL | TEST2 | 13  | 65 |    |  3    (0) | 00:00:01 | | *  3 |   hash join    |    |  86999 | 16m|  9352k|  1602    (1) | 00:00:20 | |    4 |    table access full| test1 | 86997  |  8325K|    |347    (1) | 00:00:05 | |    5 |    table access full| test3 | 86999  |  8326k|    |347    (1) | 00:00:05 |-------------------- -----------------------------------------------------------------predicate information  (identified  by operation id):---------------------------------------------------   1 -  access ("T1". object_id "=" T2 "." object_id ")    3 - access (" T1 "." object_id "=" T3 "." object_id ") Statistics----------------------------------------------------------  0   recursive calls  0  db block gets        2491  consistent gets       2484  physical  Reads  0  redo size       3736  bytes  sent via sql*net to client523  bytes received via sql*net  from client  2  sql*net roundtrips to/from client  0  sorts  ( Memory)   0  sorts  (disk)  11  rows processed  plus   Materialize hint, force Oracle to generate temporary table 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;11 rows selected. Execution plan----------------------------------------------------------plan hash value:  3288461629----------------------------------------------------------------------------------------------------- -----------| id  | operation   | name        | rows  | bytes | tempspc| cost  (%CPU) | time     |----------------------------------------------------------------------------------------------------------------|   0  | SELECT STATEMENT   |       |    582m|   165g|       |  3963 (+) |  00:00:48 | |    1 |  TEMP TABLE TRANSFORMATION |        |       |       |        |    |       | |    2 |   load as select   | sys_temp_ 0fd9d661a_155646 |       |       |        |    |        || *  3 |    hash join   |       |  86997 |  8750k|       |   351  (1) |  00:00:05 | |    4 |     TABLE ACCESS FULL   |  Test2       |    13 |    65  |       |     3  (0) | 00:00:01  | |    5 |     TABLE ACCESS FULL   |  test1       | 86997 |  8325k|        |   347  (1) | 00:00:05 | | *  6 |   hash join   |        |   582M|   165g|  9352k|  3613 (| 00:00:44 |) |    7 |    table access full   | test3        | 86999 |  8326K|        |   347  (1) | 00:00:05 | |    8 |    VIEW    |        | 86997 |    17M|       |    332  (1) | 00:00:04 | |    9 |     TABLE ACCESS FULL   |  sys_temp_0fd9d661a_155646 | 86997 |  8750k|        |   332  (1) | 00:00:04 |--------------------------------------------------------- -------------------------------------------------------predicate information  (identified by operation id):--------------------------------- ------------------   3 - access ("T1". object_id "=" T2 "." object_id ")    6 - access (" T "." object_id "=" T3 "." object_id ") Statistics---------------------------------------------------------- 55  recursive  calls  8  db block gets       2525   consistent gets       2485  physical  Reads656  redo size       3736  bytes sent  via sql*net to client523  bytes received via sql*net from  client  2  SQL*Net roundtrips to/from client  3   sorts  (memory)   0  sorts  (disk)  11  rows processed 

Oracle with AS (hint inline materialize)