oracle usehash,/*+use_hash(t1 t2)*/的神奇

--查询除收入以外的用户信息

DROP TABLE GD_11403_USER_INFO;

CREATE TABLE GD_11403_USER_INFO AS

SELECT

PROD_INST_NUM

,INSTALL_ADDR

,CUST_NAME

,CUST_RATETP_DESC

,CONT_METHOD

,PROM_NAME

,PROM_END_DT

FROM        OCRM_BT_AST_TELEPHONE

WHERE       BUREAU = '浦东'

AND         SUB_BUREAU IN ('临沂','六里','三林','杨思','永泰','云莲','周家渡')

AND         CUST_RATETP_DESC IN('商客C','商客D')

AND         STAT_TP = '1'

;

--取DM_DEV_BILL表中3,4,5月份的数据

DROP TABLE GD_11403_BILL_3;

CREATE TABLE GD_11403_BILL_3 AS

SELECT      *

FROM        DM_DEV_BILL@DMPD_DM_TMP

WHERE       DTA_DATE = TO_DATE('20080301','YYYYMMDD');

--用设备号关联取各收入

--3月

DROP TABLE GD_11403_INCOME;

CREATE TABLE GD_11403_INCOME AS

SELECT

/*+use_hash(t1 t2)*/

T1.PROD_INST_NUM

,T1.INSTALL_ADDR

,T1.CUST_NAME

,T1.CUST_RATETP_DESC

,T1.CONT_METHOD

,T1.PROM_NAME

,T1.PROM_END_DT

,T2.TOT_FEE AS TOT_FEE_3

,0 AS TOT_FEE_4

,0 AS TOT_FEE_5

,(T2.LOCAL_FEE + T2.PHS_FEE) AS LOC_FEE_3

,0 AS LOC_FEE_4

,0 AS LOC_FEE_5

,T2.DDD_FEE AS DDD_FEE_3

,0 AS DDD_FEE_4

,0 AS DDD_FEE_5

,T2.IDD_GAT_FEE AS IDD_GAT_FEE_3

,0 AS IDD_GAT_FEE_4

,0 AS IDD_GAT_FEE_5

FROM        GD_11403_USER_INFO                  T1

,GD_11403_BILL_3                        T2

WHERE       T1.PROD_INST_NUM = T2.PROD_INST_NUM(+)

;

CREATE GD.IDX_GD_11403_BILL_3  ON GD_11403_BILL_3  (PROD_INST_NUM)

建这个索引20分钟没有反映

GD_11403_USER_INFO                  T1    --2W条数据

GD_11403_BILL_3                         T2    --1000W条数据