优化Oracle with全表扫描的问题

今天开发接了一个很BT的需求。
找一个人的所有好友，查询所有好友的所有作品，然后按照时间倒序排列,取若干记录，
然后关联作品评论表。

作品包括原唱表，翻唱表，伴奏表，视频表，博客表和照片表,
不同的作品类型还要关联不同的专辑表，最后还要关联用户表..--------------------------------------------------------------------------------Linux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm--------------------------------------------------------------------------------
结果就是这个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左右。但是用户表的关联字段明明已经创建了索引。。更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-08/104927p2.htm

PostgreSQL 运行在 FreeBSD 和 Linux 的表现的测试数据MySQL运行状态监控（pt-mysql-summary）相关资讯 Oracle优化 Oracle with

Oracle 查询技巧与优化（今 09:02）
Oracle数据库性能优化之内存磁盘（09/19/2015 15:42:44）
Oracle热点表优化总结（02/25/2015 09:39:32）

Oracle多表查询优化（11/24/2015 12:02:59）
Oracle优化实战（绑定变量）（06/16/2015 20:01:38）
Oracle 多表查询优化（02/24/2015 20:46:48）

本文评论查看全部评论（0）

评论声明