有一条sql,需要取三个表某个字段的合集,故想通过建视图提高查询的效率。不知道这方法可行不?附上sql:
select /*+ parallel(his,8) */ his.idva, b.opnid
from
(select mobile from CH_BBC_ALLSALES where calcmonth='201111' union all
select mobile from CH_BBC_CONTDATA where calcmonth='201111' union all
select mobile from CH_BBC_WADTTASK where calcmonth='201111') t, CH_BBC_JFOPNMAP b, DSMPUSERDATA_HIS his
where
his.idva=t.mobile
and his.spid=b.entid
and his.SPBIZCODE=b.busiid
and his.opr='07'
and to_date(his.efftt,'yyyymmddhh24miss') between add_months(to_date('20111101','yyyymmdd'),-2) and to_date('20111130235959','yyyymmddhh24miss')
union all
select a.servnumber, b.opnid from
(select /*+ parallel(b,8) */ b.subsid, b.canceloid, c.opnid from CM_SUBS_SPSERVICE b, CH_BBC_JFOPNMAP c
where b.spid=c.entid and b.spbizid=c.busiid) b,
(select /*+ parallel(rec,8) */ rec.servnumber, rec.entityid, rec.oid from
(select mobile from CH_BBC_ALLSALES where calcmonth='201111' union all
select mobile from CH_BBC_CONTDATA where calcmonth='201111' union all
select mobile from CH_BBC_WADTTASK where calcmonth='201111') t, CS_REC_RECEPTION rec
where rec.servnumber=t.mobile
and rec.recdefid='Cancel'
and rec.recdate between add_months(to_date('20111101','yyyymmdd'),-2) and to_date('20111130235959','yyyymmddhh24miss')) a
where a.entityid=b.subsid and a.oid=b.canceloid;
select /*+ parallel(his,8) */ his.idva, b.opnid
from
(select mobile from CH_BBC_ALLSALES where calcmonth='201111' union all
select mobile from CH_BBC_CONTDATA where calcmonth='201111' union all
select mobile from CH_BBC_WADTTASK where calcmonth='201111') t, CH_BBC_JFOPNMAP b, DSMPUSERDATA_HIS his
where
his.idva=t.mobile
and his.spid=b.entid
and his.SPBIZCODE=b.busiid
and his.opr='07'
and to_date(his.efftt,'yyyymmddhh24miss') between add_months(to_date('20111101','yyyymmdd'),-2) and to_date('20111130235959','yyyymmddhh24miss')
union all
select a.servnumber, b.opnid from
(select /*+ parallel(b,8) */ b.subsid, b.canceloid, c.opnid from CM_SUBS_SPSERVICE b, CH_BBC_JFOPNMAP c
where b.spid=c.entid and b.spbizid=c.busiid) b,
(select /*+ parallel(rec,8) */ rec.servnumber, rec.entityid, rec.oid from
(select mobile from CH_BBC_ALLSALES where calcmonth='201111' union all
select mobile from CH_BBC_CONTDATA where calcmonth='201111' union all
select mobile from CH_BBC_WADTTASK where calcmonth='201111') t, CS_REC_RECEPTION rec
where rec.servnumber=t.mobile
and rec.recdefid='Cancel'
and rec.recdate between add_months(to_date('20111101','yyyymmdd'),-2) and to_date('20111130235959','yyyymmddhh24miss')) a
where a.entityid=b.subsid and a.oid=b.canceloid;
增加中间表会增加数据的空间,这个不现实呀,就算是中间表,数据也可能上千万数据的。
这个是否会增加数据的占用空间?