優化Oracle with全表掃描的問題

壹頁書發表於2014-07-30
優化Oracle
今天開發接了一個很BT的需求。
找一個人的所有好友，查詢所有好友的所有作品，然後按照時間倒序排列,取若干記錄，
然後關聯作品評論表。

作品包括原唱表，翻唱表，伴奏表，視訊表，部落格表和照片表,
不同的作品型別還要關聯不同的專輯表，最後還要關聯使用者表..

結果就是這個SQL很長...
with
t1 as (select to_userid from friend_list f where f.userid=411602438),
t2 as (
  select 'mc' as t,rid,createtime 
  from
  (
    select mc.rowid rid,mc.createtime from music_cover mc,t1 where mc.userid=t1.to_userid and mc.opus_stat >0 order by mc.createtime desc
  ) where rownum< 100
  union all
  select 'mo',rid,createtime 
  from
  (
    select mo.rowid rid,mo.createtime  from music_original mo,t1 where mo.userid=t1.to_userid and mo.opus_stat >0 order by mo.createtime desc
  ) where rownum< 100
  
  union all
  select 'mv',rid,createtime 
  from
  (
    select mv.rowid rid,mv.createtime  from music_video mv,t1 where mv.userid=t1.to_userid and mv.opus_stat >0 order by mv.createtime desc
  ) where rownum< 100
  
  union all
  select 'ma',rid,createtime 
  from
  (
    select ma.rowid rid,ma.createtime from music_accompany ma,t1 where ma.userid=t1.to_userid and ma.opus_stat >0 order by ma.createtime desc
  ) where rownum< 100
  
  union all
  select 'bl',rid,createtime
  from
  (
    select bl.rowid rid,bl.createtime  from blog_list bl,t1 where bl.userid=t1.to_userid and bl.opus_stat >0 order by bl.createtime desc
  ) where rownum< 100
  
  union all
  select 'pl',rid,createtime
  from
  (
    select pl.rowid rid,pl.createtime  from photo_list pl,t1 where pl.userid=t1.to_userid and pl.opus_stat >0 order by pl.createtime desc
  ) where rownum< 100
),
t3 as 
(
  select * from 
  (
    select * from t2 order by createtime desc
  )
  where rownum<100
),
t4 as
(
select 
 t3.t,
 decode(t3.t,
'mc',2,
'mo',2,
'mv',2,
'ma',2,
'pl',4,
'bl',5
) type_code,
 mc.userid||mo.userid||mv.userid||ma.userid||bl.userid||pl.userid userid,
 mc.file_url||mo.file_url||mv.file_url||ma.file_url||bl.file_url||pl.file_url file_url,
 mc.opus_Name||mo.opus_Name||mv.opus_name||ma.opus_name||bl.opus_name||pl.opus_name opus_name,
 mc.opus_id||mo.opus_id||mv.opus_id||ma.opus_id||bl.opus_id||pl.opus_id opus_id,
 mc.createtime||mo.createtime||mv.createtime||ma.createtime||bl.createtime||pl.createtime createtime,
 mv.opus_desc||mo.opus_desc||mc.opus_desc||ma.opus_desc||bl.opus_desc||pl.opus_desc opus_desc,
 mv.album_id||mo.album_id||mc.album_id||ma.album_id||bl.album_id||pl.album_id album_id,
 mv.visit_num||mo.visit_num||mc.visit_num||ma.visit_num||bl.visit_num||pl.visit_num visit_num
from t3
left join music_cover mc on(t3.rid=mc.rowid)
left join music_original mo on(t3.rid=mo.rowid)
left join music_video mv on(t3.rid=mv.rowid)
left join music_accompany ma on(t3.rid=ma.rowid)
left join blog_list bl on(t3.rid=bl.rowid)
left join photo_list pl on(t3.rid=pl.rowid)
)
select /*+ ordered use_nl(t4,base) */
base.nickname,
decode(t4.type_code,
2,(select al.album_name from music_album al where al.album_id=t4.album_id),
4,(select al.album_name from photo_album al where al.album_id=t4.album_id),
5,(select al.album_name from blog_album al where al.album_id=t4.album_id)
) album_name,
(select count(*) from user_comment com where com.typeid=t4.type_code and t4.opus_id=com.to_id and status=1) commentTotal,
t4.*
from t4,mvbox_user.user_baseinfo base where base.userid=t4.userid;


建立索引消除排序
create index inx_music_cover on music_cover(userid,opus_stat,operTime);
create index inx_music_original on music_original(userid,opus_stat,operTime);
create index inx_music_video on music_video(userid,opus_stat,operTime);
create index inx_music_accompany on music_accompany(userid,opus_stat,operTime);
create index inx_blog_list on blog_list(userid,opus_stat,operTime);
create index inx_photo_list on photo_list(userid,opus_stat,operTime);
create index inx_user_comment on user_comment(to_id,typeid,status);

使用者表比較大，是一個有900w記錄的分割槽表。檢視執行計劃，都符合預期，只是最後關聯使用者表的時候，使用了全表掃描，
直接導致這個SQL執行了20s左右。但是使用者表的關聯欄位明明已經建立了索引。。


模擬如下,使用Oracle的HR示例使用者
create table emp as select * from hr.employees;
create index inx_hire_date on emp(hire_date);
create index inx_emp_id on emp(employee_id);
with t1 as
(
select user_id from
(
select employee_id user_id from emp order by hire_date desc 
) where rownum<20
)
select first_name,last_name from emp t2,t1 where t2.employee_id=t1.user_id;

執行計劃


解決方案如下,使用Oracle Hint
with t1 as
(
    select user_id from
    (
        select employee_id user_id from emp order by hire_date desc 
    ) where rownum<20
)
select /*+ ordered use_nl(t1,t2) */ first_name,last_name from t1,emp t2 where t2.employee_id=t1.user_id;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1242731/，如需轉載，請註明出處，否則將追究法律責任。

相關文章