dblink 取数据的优化

今天开发人员，给了我一段存储过程,部分代码如下：

CREATE TABLE tmp AS SELECT FEE.MSISDN,
                           HW.CITY_ID,
                           HW.ZONENAME,
                           TO_CHAR(FEE.DEAL_DATE, 'yyyymmddhh24miss') AS DEAL_DATE,
                           FEE.PAY_VALUE,
                           FEE.CPSERVICEID,
                           CASE
                             WHEN FEE.SERVICE_TYPE = 5 THEN
                              1
                             ELSE
                              FEE.SERVICE_TYPE
                           END SERVICE_TYPE,
                           FEE.PACKAGEID,
                           FEE.CONSUMECODE
                            FROM STATUSER.DW_USER_BHVR_FEE@TO_CMGPBI       FEE,
                                 STATUSER.HUAWEI_NUMBERDATA_INFO@TO_CMGPBI HW,
                                 STATUSER.DIM_ALL_SERVICE@TO_CMGPBI        ALS
                           WHERE SUBSTR(FEE.MSISDN, 0, 7) = HW.MSISDN
                             AND FEE.DEAL_DATE /*BETWEEN
                                                                 TO_DATE(TO_CHAR(SYSDATE - 1, 'yyyymmdd') ||
                                                                         '000000',
                                                                         'yyyymmddhh24miss') AND
                                                                 TO_DATE(TO_CHAR(SYSDATE - 1, 'yyyymmdd') ||
                                                                         '235959',
                                                                         'yyyymmddhh24miss')*/
                                 BETWEEN
                                 TO_DATE('20100522000000', 'yyyymmddhh24miss') AND
                                 TO_DATE('20100522235959', 'yyyymmddhh24miss')
                             AND FEE.CPSERVICEID = ALS.SERVICE
                             AND FEE.PAY_VALUE > 0
                             AND ALS.SERVICE_TYPE = 1
                             AND ALS.SERVICETYPE = 5

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

CREATE TABLE STATEMENT Optimizer Mode=ALL_ROWS  1     820558                          
  LOAD AS SELECT VGOP.TMP                               
    HASH JOIN  1   183   820557                          
      MERGE JOIN CARTESIAN  1   88   275                          
        REMOTE .DIM_ALL_SERVICE 1   52   2   TO_CMGPBI SERIAL               
        BUFFER SORT  126 K 4 M 273                          
          REMOTE .HUAWEI_NUMBERDATA_INFO 126 K 4 M 273   TO_CMGPBI SERIAL               
      REMOTE .DW_USER_BHVR_FEE 21   1 K 820282   TO_CMGPBI SERIAL               

看执行计划有MERGE JOIN CARTESIAN，并且伴随ORA-1652: unable to extend temp segment的错误，当我把create table tmp 去掉，执行计划就变成：

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT REMOTE Optimizer Mode=ALL_ROWS  1     312                          
  HASH JOIN  1   105   312                          
    NESTED LOOPS  1   90   39                          
      PARTITION RANGE SINGLE  1   82   2                        813 813
        PARTITION LIST ALL  1   82   2                        1 5
          TABLE ACCESS FULL STATUSER.DW_USER_BHVR_FEE 1   82   2   CMGPBI                    4061 4065
      VIEW STATUSER.DIM_ALL_SERVICE 1   8   37   CMGPBI                      
        SORT UNIQUE  2   177   37                          
          UNION ALL PUSHED PREDICATE                                
            NESTED LOOPS  1   95   6                          
              NESTED LOOPS  1   78   3                          
                NESTED LOOPS OUTER  1   71   3                          
                  MAT_VIEW ACCESS BY INDEX ROWID STATUSER.MV_TB_D_SERVICE 1   54   2   CMGPBI                      
                    INDEX UNIQUE SCAN STATUSER.PK_TB_D_SERVICE 1     1   CMGPBI                      
                  MAT_VIEW ACCESS BY INDEX ROWID STATUSER.MV_TB_D_CONSUMECODE 1   17   1   CMGPBI                      
                    INDEX UNIQUE SCAN STATUSER.PK_TB_D_CONSUMECODE 1     0   CMGPBI                      
                INDEX UNIQUE SCAN STATUSER.PK_TB_D_CP 165   1 K 0   CMGPBI                      
              TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 1   17   3   CMGPBI                      
            HASH JOIN  1   82   29                          
              NESTED LOOPS  1   65   25                          
                TABLE ACCESS BY INDEX ROWID STATUSER.TB_BI_SERVICE_MBOX 1   55   2   CMGPBI                      
                  INDEX UNIQUE SCAN STATUSER.PK_TB_BI_SERVICE_MBOX 1     1   CMGPBI                      
                TABLE ACCESS FULL STATUSER.TB_BI_CP_MBOX 1   10   23   CMGPBI                      
              TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 244   4 K 3   CMGPBI                      
    TABLE ACCESS FULL STATUSER.HUAWEI_NUMBERDATA_INFO 126 K 1 M 272   CMGPBI                      

经过分析觉得第一个nest loops 分区表和view之间采用use_hash更快，因为数据量比较大，所以加了/*+ use_hash(fee,als) */  hints。

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT REMOTE Optimizer Mode=ALL_ROWS  1     458                          
  HASH JOIN  1   123   458                          
    HASH JOIN  1   108   185                          
      PARTITION RANGE SINGLE  1   72   2                        813 813
        PARTITION LIST ALL  1   72   2                        1 5
          TABLE ACCESS FULL STATUSER.DW_USER_BHVR_FEE 1   72   2   CMGPBI                    4061 4065
      VIEW STATUSER.DIM_ALL_SERVICE 7 K 250 K 182   CMGPBI                      
        SORT UNIQUE  7 K 570 K 182                          
          UNION-ALL                                
            NESTED LOOPS OUTER  19   1 K 67                          
              NESTED LOOPS  19   1 K 48                          
                HASH JOIN  19   1 K 48                          
                  MAT_VIEW ACCESS FULL STATUSER.MV_TB_D_SERVICE 20   1 K 44   CMGPBI                      
                  TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 244   4 K 3   CMGPBI                      
                INDEX UNIQUE SCAN STATUSER.PK_TB_D_CP 1   7   0   CMGPBI                      
              MAT_VIEW ACCESS BY INDEX ROWID STATUSER.MV_TB_D_CONSUMECODE 1   17   1   CMGPBI                      
                INDEX UNIQUE SCAN STATUSER.PK_TB_D_CONSUMECODE 1     0   CMGPBI                      
            HASH JOIN  7 K 569 K 113                          
              TABLE ACCESS FULL STATUSER.TB_BI_CP_MBOX 847   8 K 23   CMGPBI                      
              HASH JOIN  7 K 499 K 90                          
                TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 244   4 K 3   CMGPBI                      
                TABLE ACCESS FULL STATUSER.TB_BI_SERVICE_MBOX 7 K 381 K 86   CMGPBI                      
    TABLE ACCESS FULL STATUSER.HUAWEI_NUMBERDATA_INFO 126 K 1 M 272   CMGPBI                      

但是前面加了create table tmp as后，执行计划就变成起初的执行计划，经过一段时间考虑，把临时表，建在远端数据库上，执行速度非常的快，总算是解决了个问题：

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

INSERT STATEMENT REMOTE Optimizer Mode=ALL_ROWS  1     458                          
  HASH JOIN  1   123   458                          
    HASH JOIN  1   108   185                          
      PARTITION RANGE SINGLE  1   72   2                        813 813
        PARTITION LIST ALL  1   72   2                        1 5
          TABLE ACCESS FULL STATUSER.DW_USER_BHVR_FEE 1   72   2   CMGPBI                    4061 4065
      VIEW STATUSER.DIM_ALL_SERVICE 7 K 250 K 182   CMGPBI                      
        SORT UNIQUE  7 K 570 K 182                          
          UNION-ALL                                
            NESTED LOOPS OUTER  19   1 K 67                          
              NESTED LOOPS  19   1 K 48                          
                HASH JOIN  19   1 K 48                          
                  MAT_VIEW ACCESS FULL STATUSER.MV_TB_D_SERVICE 20   1 K 44   CMGPBI                      
                  TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 244   4 K 3   CMGPBI                      
                INDEX UNIQUE SCAN STATUSER.PK_TB_D_CP 1   7   0   CMGPBI                      
              MAT_VIEW ACCESS BY INDEX ROWID STATUSER.MV_TB_D_CONSUMECODE 1   17   1   CMGPBI                      
                INDEX UNIQUE SCAN STATUSER.PK_TB_D_CONSUMECODE 1     0   CMGPBI                      
            HASH JOIN  7 K 569 K 113                          
              TABLE ACCESS FULL STATUSER.TB_BI_CP_MBOX 847   8 K 23   CMGPBI                      
              HASH JOIN  7 K 499 K 90                          
                TABLE ACCESS FULL STATUSER.DIM_GAMETYPE 244   4 K 3   CMGPBI                      
                TABLE ACCESS FULL STATUSER.TB_BI_SERVICE_MBOX 7 K 381 K 86   CMGPBI                      
    TABLE ACCESS FULL STATUSER.HUAWEI_NUMBERDATA_INFO 126 K 1 M 272   CMGPBI