Oracle with as （hint inline materialize）

lkoih

• 测试数据：
  
  

1 2 3 4 5 6

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.

收集统计信息：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100

BEGIN   DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',                                 tabname          => 'TEST1',                                 estimate_percent => 100,                                 method_opt       => 'for  columns owner size repeat',                                 no_invalidate    => FALSE,                                 degree           => 4,                                 granularity      => 'ALL',                                 cascade          => 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 client     523  bytes received via SQL*Net from client       2  SQL*Net roundtrips to/from client       0  sorts (memory)       0  sorts (disk)      11  rows processed        加 materialize hint,强制oracle生成临时表 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    (40)| 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 (44)| 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 reads     656  redo size        3736  bytes sent via SQL*Net to client     523  bytes received via SQL*Net from client       2  SQL*Net roundtrips to/from client       3  sorts (memory)       0  sorts (disk)      11  rows processed