select b.pieceid,
a.chnid,
b.title,
to_char(a.confirmtime, 'yy-mm-dd') as pubtimeymd,
to_char(b.pubtime, 'yy-mm-dd') as pubtimeymdys,
case
when a.confirmtime < sysdate - 3 then
0
else
1
end as isnewest,
b.outurl,
b.showtarget,
b.psource,
b.viewtimes,
b.intro,
case
when b.imageid is null then
(select id
from (select mainid, id
from cms_store
where mainid is not null
and LOWER(doctype) in ('jpg', 'png', 'gif')
order by intime desc)
where rownum < 2
and mainid = b.storeid)
else
b.imageid
end imageid
from cms_chn_piece a, cms_piece b
where
b.pieceid = a.pieceid
and a.CHNID = 2108
and status=1
and a.COMPANYID = 'txqxx'
order by SHOWSORT desc
这个sql执行要3分多,总共关联出来的才1500多态数据,哪位帮忙看看
对应的执行计划如下
------解决思路----------------------
给a表建相关索引
------解决思路----------------------
cms_store这张表有多少条数据?
------解决思路----------------------
cms_chn_piece数据量多大,
CHNID = 2108 and a.COMPANYID = 'txqxx' 过滤后数据量有多大
------解决思路----------------------
在select 后面加上 /*+ hash_join(a,b) */ 不然先对a 表进行子查询先 先缩表的数据范围
------解决思路----------------------
统计信息偏差有点大
cms_chn_piece表重新采集统计信息
dbms_stats.gather_table_stats
然后找出这个查询中cms_chn_piece表由6万筛选到1500主要是由哪个字段过滤的,在那个字段上建索引
------解决思路----------------------
把cms_piece换成驱动表试试
------解决思路----------------------
首先要先给出相关表的索引啊。
另外这个标量子查询有优化空间,参考下面:
select b.pieceid, a.chnid, b.title,
to_char(a.confirmtime, 'yy-mm-dd') as pubtimeymd,
to_char(b.pubtime, 'yy-mm-dd') as pubtimeymdys,
case
when a.confirmtime < sysdate - 3 then
0
else
1
end as isnewest, b.outurl, b.showtarget, b.psource, b.viewtimes,
b.intro,
case
when b.imageid is null then
(select max(id) keep(dense_rank first order by intime desc) id
from cms_store
where mainid is not null
and lower(doctype) in ('jpg', 'png', 'gif')
and mainid = b.storeid)
else
b.imageid
end imageid
from cms_chn_piece a, cms_piece b
where b.pieceid = a.pieceid
and a.chnid = 2108
and status = 1
and a.companyid = 'txqxx'
order by showsort desc
------解决思路----------------------
select b.pieceid,
a.chnid,
b.title,
to_char(a.confirmtime, 'yy-mm-dd') as pubtimeymd,
to_char(b.pubtime, 'yy-mm-dd') as pubtimeymdys,
case
when a.confirmtime < sysdate - 3 then
0
else
1
end as isnewest,
b.outurl,
b.showtarget,
b.psource,
b.viewtimes,
b.intro,
case
when b.imageid is null then
(select id
from (select mainid, id
from cms_store
where mainid is not null
and LOWER(doctype) in ('jpg', 'png', 'gif')
-- 改了此处,关联条件内置,如果没影响结果,效率会高很多
and mainid = b.storeid
order by intime desc)
where rownum < 2 )
else
b.imageid
end imageid
from cms_chn_piece a, cms_piece b
where
b.pieceid = a.pieceid
and a.CHNID = 2108
and status=1
and a.COMPANYID = 'txqxx'
order by SHOWSORT desc