join left 大数据_【原创】oracle中大数据量join操作的试验

#Elapsed: 00:06:38.53 22674664

create table test.tmp_oicall_info_20070206

tablespace tbs_sub

as

select t1.MISC_MSG_ID,

t1.MSG_ID,

t1.MSG_TYPE,

t1.FEE_TYPE,

t1.FEE_VALUE,

t1.SEND_ADDRESS,

t1.RECV_ADDRESS,

t1.FEE_ADDRESS,

t1.DOWN_STATION,

t1.OUTTER_ID,

t1.SERVICE_ID,

t1.OSS_RECV_CODE,

t1.MT_TYPE,

t1.MT_CONTENT,

t1.MT_TIME,

t1.CARRY_MSG,

t1.CARRY_ID,

t1.RPT_FLAG,

t1.RPT_STATE,

t1.GATEWAY_RPT_STATE,

t1.link_id,

t1.err_detail from mqqflow.t_mt_sms_200702 partition(p_mt_sms_20070206) t1

#Elapsed: 00:05:07.04

#Elapsed: 00:04:29.64 Elapsed: 00:04:34.68  (说明前面create table的时候，不需要order by)

create table test.tmp_sms_mt_info

as

select t1.user_num,t1.service_id,SUB_CHANNEL_CODE,t1.channel_code channel_code,REG_MODE,UNREG_MODE,ENABLE_STATUS,

FIRST_REG_TIME,LAST_REG_TIME,LAST_UNREG_TIME

from  test.tmp_user_info_2 t1,test.tmp_oicall_info t2 where t1.service_id=t2.service_id and t1.user_num=t2.recv_address

CREATE INDEX IDX_tmp_sms_mt_info ON test.tmp_sms_mt_info

(USER_NUM, service_id)

NOLOGGING

TABLESPACE TBS_SUB

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

INITIAL          64K

MINEXTENTS       1

MAXEXTENTS       2147483645

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

NOPARALLEL;

///

select count(*) from mqqflow.t_sso_order_2007 partition(p_sso_order_200702) where stat_date=to_date('2007-02-07', 'YYYY-MM-DD')  2044 行

///

create table tmp_mt_sms

TABLESPACE TBS_STAT

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          64K

MINEXTENTS       1

MAXEXTENTS       2147483645

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING

as

select

t1.MISC_MSG_ID,

t1.MSG_ID,

t1.MSG_TYPE,

t1.FEE_TYPE,

t1.FEE_VALUE,

t1.SEND_ADDRESS,

t1.RECV_ADDRESS,

t1.FEE_ADDRESS,

t1.DOWN_STATION,

t1.OUTTER_ID,

t1.SERVICE_ID,

t1.OSS_RECV_CODE,

t1.MT_TYPE,

t1.MT_CONTENT,

t1.MT_TIME,

t1.CARRY_MSG,

t1.CARRY_ID,

t2.channel_code,

t2.SUB_CHANNEL_CODE,

t2.user_num,

t2.REG_MODE,

t2.UNREG_MODE,

t2.ENABLE_STATUS,

t2.FIRST_REG_TIME,

t2.LAST_REG_TIME,

t2.LAST_UNREG_TIME,

t1.RPT_FLAG,

t1.RPT_STATE,

t1.GATEWAY_RPT_STATE,

t1.link_id,

t1.err_detail

from  mqqflow.t_mt_sms_200702 partition(p_mt_sms_20070207)  t1

left join

test.tmp_sms_mt_info t2

on t2.user_num = t1.recv_address

and t1.service_id = t2.service_id

# Elapsed: 00:13:05.74

create table test. tmp_mt_sms

tablespace tbs_sub

as

select

t1.MISC_MSG_ID,

t1.MSG_ID,

t1.MSG_TYPE,

t1.FEE_TYPE,

t1.FEE_VALUE,

t1.SEND_ADDRESS,

t1.RECV_ADDRESS,

t1.FEE_ADDRESS,

t1.DOWN_STATION,

t1.OUTTER_ID,

t1.SERVICE_ID,

t1.OSS_RECV_CODE,

t1.MT_TYPE,

t1.MT_CONTENT,

t1.MT_TIME,

t1.CARRY_MSG,

t1.CARRY_ID,

decode(t3.link_id,null,t2.channel_code,'WEB') channel_code,

t2.SUB_CHANNEL_CODE,

t2.REG_MODE,

t2.UNREG_MODE,

t2.ENABLE_STATUS,

t2.FIRST_REG_TIME,

t2.LAST_REG_TIME,

t2.LAST_UNREG_TIME,

t1.RPT_FLAG,

t1.RPT_STATE,

t1.GATEWAY_RPT_STATE,

t1.link_id,

t1.err_detail

from test.tmp_oicall_info_20070206 t1

left join test.tmp_sso_mt

t3

on t3.stat_date=to_date('2007-02-06', 'YYYY-MM-DD')

and t3.link_id = t1.link_id

left join

test.tmp_user_info t2

on t2.user_num = t1.RECV_ADDRESS

and t1.service_id = t2.service_id