一个奇怪的执行计划（为什么不是HASH JOIN OUTER 关联）

SQL> select t1.MSISDN,
  2              t1.PROV_ID      ,
  3              t1.CITY_ID      ,
  4              t1.USER_TYPE    ,
 ..........
  9              t1.REG_TIME --    ,
 10              --t2.imei,
 11              --t2.update_time
 12  from  BAK_SRC_SUBSCRIBER t1,
 13       src_device_info   t2
 14  where t1.msisdn=t2.msisdn(+)
 15    and t2.status=1 ;

  MSISDN    PROV_ID    CITY_ID  USER_TYPE GROUP_FLAG   VIP_FLAG  CR_OPENED
---------- ---------- ---------- ---------- ---------- ---------- ----------
GPRS_OPENED REG_TIME
----------- ------------
1.3912E+10        200        755          1          0          0          0
          0 05-MAR-09

1.3912E+10        200        755          1          0          0          0
          0 18-MAR-09

1.5915E+10        200        755          1          0          0          0
          0 15-APR-09

.........
9 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1760931589

--------------------------------------------------------------------------------
--------------------------

| Id  | Operation           | Name               | Rows  | Bytes | Cost (%CPU)|
Time     | Pstart| Pstop |

--------------------------------------------------------------------------------
--------------------------

|   0 | SELECT STATEMENT    |                    |    11 |  1364 |    17   (6)|
00:00:01 |       |       |

|*  1 |  HASH JOIN          |                    |    11 |  1364 |    17   (6)|
00:00:01 |       |       |

|   2 |   PARTITION HASH ALL|                    |    11 |   121 |    13   (0)|
00:00:01 |     1 |    12 |

|*  3 |    TABLE ACCESS FULL| SRC_DEVICE_INFO    |    11 |   121 |    13   (0)|
00:00:01 |     1 |    12 |

|   4 |   TABLE ACCESS FULL | BAK_SRC_SUBSCRIBER |    27 |  3051 |     3   (0)|
00:00:01 |       |       |

--------------------------------------------------------------------------------
--------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."MSISDN"="T2"."MSISDN")
   3 - filter("T2"."STATUS"=1)

Note
-----
   - dynamic sampling used for this statement

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/297293/viewspace-605832/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/297293/viewspace-605832/