oracle

最新推荐文章于 2024-09-08 13:34:16 发布

木子文轩

最新推荐文章于 2024-09-08 13:34:16 发布

阅读量320

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/lisu_suby/article/details/40659109

版权

oracle 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

下面分享一个生产异常sql的优化，已在生产库上测试过，性能有近百倍的提升并且查出来的1703条数据完全相同。原sql执行使用了174.984秒，优化后的sql执行了1.829秒。

原SQL如下：

select *

from (select rownum rdd, d.*

from (select content.*,

(case content.recom

when '1' then

when '2' then

else

end) recomIndex,

(casecontent.istop

when '1' then

else

end) istopIndex,

round(to_number(to_date(content.hiddenDate,

'yyyy-MM-dd') - trunc(sysdate))) as days,

round(to_number(to_date(content.dateEnd, 'yyyy-MM-dd') -

trunc(sysdate))) as days2

from (selectmax(t.resource_link) as url,

max(t.resource_title) as title,

max(t.istop) as istop,

max(t.date_released) as publishDate,

max(to_char(to_date('01/01/1970','MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'endtime',

cms_expandx.expand_value) +

1000 * 60 * 60 * 8) /

(1000 * 60 * 60 * 24),

'yyyy-MM-dd')) as endtime,

max(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'dateEnd',

cms_expandx.expand_value) +

1000 * 60 * 60 * 8) /

(1000 * 60 * 60 * 24),

'yyyy-MM-dd')) as dateEnd,

max(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'hiddenDate',

cms_expandx.expand_value) +

1000* 60 * 60 * 8) /

(1000 * 60 * 60 * 24),

'yyyy-MM-dd')) as hiddenDate,

max(decode(cms_expandx.expand_key,

'notPublish',

cms_expandx.expand_value)) as isPublish,

max(decode(cms_expandx.expand_key,

'isNewPic',

cms_expandx.expand_value)) as isNewPic,

max(t.ispublish) as isPublish2,

max(decode(cms_expandx.expand_key,

'redirectUrl',

cms_expandx.expand_value)) as contentPageUrl,

max(decode(cms_expandx.expand_key,

'recom',

cms_expandx.expand_value))as recom

from (select tt.resource_id,

tt.resource_link,

tt.resource_title,

tt.date_released,

tt.ispublish,

tt.istop

from (select distinct cms_expand.resource_id,

cms_props.resource_link,

cms_props.resource_title,

cms_props.date_released,

cms_props.ispublish,

cms_props.istop,

(select count(8)

from cms_online_resource_expand citycount

where citycount.expand_key like

'city%'

and citycount.resource_id =

cms_props.resource_id

group by citycount.resource_id) as citycount

from cms_online_resource_expand cms_expand

left join cms_online_resource_props cms_props on cms_expand.resource_id =

cms_props.resource_id

where(cms_props.resource_path like

'/sites/creditcard.pingan.com/youhuihuodong/%.shtml' or

cms_props.resource_path like

'/sites/creditcard.pingan.com/youhuinew/%')

and (cms_props.resource_type = '8027' or

cms_props.resource_type =

'19010')) tt

left join cms_online_resource_expand cms_expand1 on tt.resource_id =

cms_expand1.resource_id

left join cms_online_resource_expand cms_expandx on cms_expandx.resource_id =

t.resource_id

group by cms_expandx.resource_id) content

where (isPublish = 'false' or isPublish2 = '1')

order by istopIndex desc,

recomIndex desc,

content.publishDatedesc) d

);

写的确实太复杂了，说一下几个突出的问题：

1，标红的子查询是没有必要的（有用的只是where后的几个条件，这个完全可以放在父查询中）：

1.1 子查询生成的citycount字段父查询是没有引用的;

1.2 使用的是左连接，但select 出来的全是右表的字段，而且where条件全是对右表的限制。

2，对大表cms_online_resource_expand （生产数据库量855513）使用了4次，分析了一下，这4次完全可以改成1次与cms_online_resource_props 连接实现。

其实仔细分析一下这个sql，逻辑是不难的，只是思路有点问题导致使用太多子查询，走了太多的弯路。

下面思路是比较合理的：

1，根据cms_online_resource_props表（生产数据库量114189）查出符合条件的较小的结果集记作rows1，

2, 根据rows1结果集中的RESOURCE_ID与大表cms_online_resource_expand的RESOURCE_ID做关联得到rows2，并做相应的行转列操作。

所以所有的逻辑只有以下几条

FROM cms_online_resource_expand cms_expandx,

cms_online_resource_props cms_props

WHERE cms_expandx.resource_id = cms_props.resource_id

AND (cms_props.resource_path LIKE

'/sites/creditcard.pingan.com/youhuihuodong/%.shtml'OR

cms_props.resource_path LIKE

'/sites/creditcard.pingan.com/youhuinew/%')

AND (cms_props.resource_type = '8027'OR

cms_props.resource_type = '19010')

GROUP BYcms_props.resource_id

HAVINGMAX(decode(cms_expandx.expand_key,'notPublish', cms_expandx.expand_value)) = 'false'ORMAX(cms_props.ispublish) = '1'

ORDER BY istopindex DESC,

recomindex DESC,

MAX(cms_props.date_released) DESC

再加上对字段的操作就变成了优化后的sql：

SELECTrownum rdd,

d.*

FROM (SELECTMAX(cms_props.resource_link)ASurl,

MAX(cms_props.resource_title) AS title,

MAX(cms_props.istop) AS istop,

MAX(cms_props.date_released) AS publishdate,

MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'endtime',

cms_expandx.expand_value) +

1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),

'yyyy-MM-dd')) AS endtime,

MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'dateEnd',

cms_expandx.expand_value) +

1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),

'yyyy-MM-dd')) AS dateend,

MAX(to_char(to_date('01/01/1970', 'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'hiddenDate',

cms_expandx.expand_value) +

1000 * 60 * 60 * 8) / (1000 * 60 * 60 * 24),

'yyyy-MM-dd')) AS hiddendate,

MAX(decode(cms_expandx.expand_key,

'notPublish',

cms_expandx.expand_value)) AS ispublish,

MAX(decode(cms_expandx.expand_key,

'isNewPic',

cms_expandx.expand_value)) AS isnewpic,

MAX(cms_props.ispublish)AS ispublish2,

MAX(decode(cms_expandx.expand_key,

'redirectUrl',

cms_expandx.expand_value)) AS contentpageurl,

MAX(decode(cms_expandx.expand_key,

'recom',

cms_expandx.expand_value)) AS recom,

(CASEMAX(decode(cms_expandx.expand_key,

'recom',

cms_expandx.expand_value))

WHEN'1'THEN

WHEN'2'THEN

ELSE

END) recomindex,

(CASEMAX(cms_props.istop)

WHEN'1'THEN

ELSE

END) istopindex,

round(to_number(to_date(

MAX(to_char(to_date('01/01/1970',

'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'hiddenDate',

cms_expandx.expand_value)+

1000 * 60 * 60 * 8) /

(1000 * 60 * 60 * 24),

'yyyy-MM-dd')),

'yyyy-MM-dd') - trunc(SYSDATE))) AS days,

round(to_number(to_date(

MAX(to_char(to_date('01/01/1970',

'MM/DD/YYYY') +

(decode(cms_expandx.expand_key,

'dateEnd',

cms_expandx.expand_value)+

1000 * 60 * 60 * 8) /

(1000 * 60 * 60 * 24),

'yyyy-MM-dd')),

'yyyy-MM-dd') - trunc(SYSDATE))) AS days2

FROM cms_online_resource_expand cms_expandx,

cms_online_resource_props cms_props

WHERE cms_expandx.resource_id = cms_props.resource_id

AND (cms_props.resource_path LIKE

'/sites/creditcard.pingan.com/youhuihuodong/%.shtml'OR

cms_props.resource_path LIKE

'/sites/creditcard.pingan.com/youhuinew/%')

AND (cms_props.resource_type = '8027'OR

cms_props.resource_type = '19010')

GROUP BYcms_props.resource_id

HAVINGMAX(decode(cms_expandx.expand_key,'notPublish', cms_expandx.expand_value)) = 'false'ORMAX(cms_props.ispublish) = '1'

ORDER BY istopindex DESC,

recomindex DESC,

MAX(cms_props.date_released) DESC

) d;

木子文轩

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle

下面分享一个生产异常sql的优化，已在生产库上测试过，性能有近百倍的提升并且查出来的1703条数据完全相同。原sql执行使用了174.984秒，优化后的sql执行了1.829秒。原SQL如下：select * from (select rownum rdd, d.* from (select content.*, (c
复制链接

扫一扫

专栏目录