Oracle的SQL优化之路

1、union all的效率比in的效率高（union all 走索引）

2、十亿级数据查询优化。

使用的表数据量：

select count(1) from rec_label;--1044898004
select count(1) from CUST_LABEL;--31620455
select count(1) from vod_sit_merge;--760063

select count(1) from MIGU_VIDEO_WATCHING_STA;--195633

优化前：300多秒

select tt.contentid video_id,vsm.vodname1 vodname1,nvl(vsm.cat1,'--') cat1,nvl(vsm.reviewscore, '0') reviewscore,
nvl(mvws.rec_val, '0') rec_val,nvl(to_char(to_date(vsm.commercialtime,'yyyymmddhh24miss'),'yyyy-mm-dd'),'--') as RELEASEYEAR,
preferencenum  from (
  select rl.contentid,count(1) preferencenum from REC_LABEL rl inner join CUST_LABEL cl on rl.bill_no=cl.cust_id
  and cl.MEMBER_FLAG = '1'  and cl.GENDER in(0,1) and cl.AGE in(2,3) and cl.CITY_ID in(571,572,574) group by rl.contentid
) tt  inner join VOD_SIT_MERGE vsm on vsm.vodid=tt.contentid  
left join MIGU_VIDEO_WATCHING_STA mvws on mvws.vod_id=tt.contentid  

where vsm.vodname1 is not null;

优化后：90秒

select rl.contentid video_id,
(select vsm.vodname1 from VOD_SIT_MERGE vsm where vsm.vodid=rl.contentid and vsm.vodname1 is not null) as vodname1,
(select nvl(vsm.cat1,'--') from VOD_SIT_MERGE vsm where vsm.vodid=rl.contentid and vsm.vodname1 is not null) as cat1,
(select nvl(vsm.reviewscore, '0') from VOD_SIT_MERGE vsm where vsm.vodid=rl.contentid and vsm.vodname1 is not null) as reviewscore,
(select nvl(mvws.rec_val, '0') from MIGU_VIDEO_WATCHING_STA mvws where mvws.vod_id=rl.contentid) as rec_val,
(select nvl(to_char(to_date(vsm.commercialtime,'yyyymmddhh24miss'),'yyyy-mm-dd'),'--') from VOD_SIT_MERGE vsm where vsm.vodid=rl.contentid and vsm.vodname1 is not null) as RELEASEYEAR,
count(1) as preferencenum from REC_LABEL rl inner join CUST_LABEL cl on rl.bill_no=cl.cust_id
  and cl.MEMBER_FLAG = '1'  and cl.GENDER in(0,0) and cl.AGE in(2,3) and cl.CITY_ID in(571,572,574) 

  group by rl.contentid;

小结：group by 子句优化，面对大数据处理，还需要继续优化！。。。。。。