1. 測試數(shù)據(jù)：

   

   成都創(chuàng)新互聯(lián)服務(wù)項目包括石城網(wǎng)站建設(shè)、石城網(wǎng)站制作、石城網(wǎng)頁制作以及石城網(wǎng)絡(luò)營銷策劃等。多年來，我們專注于互聯(lián)網(wǎng)行業(yè)，利用自身積累的技術(shù)優(yōu)勢、行業(yè)經(jīng)驗、深度合作伙伴關(guān)系等，向廣大中小型企業(yè)、政府機構(gòu)等提供互聯(lián)網(wǎng)行業(yè)的解決方案，石城網(wǎng)站推廣取得了明顯的社會效益與經(jīng)濟效益。目前，我們服務(wù)的客戶以成都為中心已經(jīng)輻射到石城省份的部分城市，未來相信會繼續(xù)擴大服務(wù)區(qū)域并繼續(xù)獲得客戶的支持與信任！

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.

收集統(tǒng)計信息：

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