Oracle
求SQL查询优化
墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年，写一次发全网
我也要 关注 + 加入社区
SELECT *
FROM (SELECT ROW_NUMBER() OVER(order by TMPKSNF DESC, TMPKSBH DESC) AS Ro,
T.*
from (SELECT max(ml.mlwsbh) MLWSBH,
ml.bmsah,
JZ.JZSCRXM,
JZ.JZSCRGH,
JZ.CJSJ,
JZ.AJBH,
JZ.WSBH,
JZ.WSMC,
JZ.ZZZT,
JZPZ,
JZSHRBH,
JZSHR,
JZSHSJ,
JZ.JZBH,
jz.ZHXGSJ,
MBMC,
AJMB_BM AJLB_BM,
jz.DWBM CBDW_BM,
JZ.SFSC,
AJZT_F,
(select nvl2(max(wjxh), count(wjxh), null) ||
nvl2(max(wjxh), '-', null) ||
sum(case
when (select to_date(sysdate - configvalue) b
from XY_DZJZ_XTPZ
where configid = 15) < w.jzczsj then
'0'
else
'1'
end) as SFTX
from yx_dzjz_jzmlwj w
left join yx_dzjz_jzml m
on w.mlbh = m.mlbh
where m.mllx = 0
and m.bmsah = ml.bmsah) LSQ,
to_number(replace(replace(lower(substr(max(ml.MLWSBH),
0,
instr(max(ml.MLWSBH),
'/',
1,
1) - 1)),
's',
'-2'),
'm-',
-4)) TMPKSBH,
to_number(substr(max(ml.MLWSBH),
instr(max(ml.MLWSBH), '/', 1, 1) + 1,
decode(instr(max(ml.MLWSBH),
'/',
1,
2),
0,
length(max(ml.MLWSBH)) -
instr(max(ml.MLWSBH), '/', 1, 1),
instr(max(ml.MLWSBH), '/', 1, 2) -
instr(max(ml.MLWSBH), '/', 1, 1) - 1))) TMPKSNF
FROM yx_dzjz_jzml ml
join yx_dzjz_jzjbxx jz
on ml.jzbh = jz.jzbh
where 1 = 1
and ml.sfsc = 'N'
and jz.sfsc = 'N'
and sslbbm = '000000-000004'
group by ml.bmsah,
JZ.JZSCRXM,
JZ.JZSCRGH,
JZ.CJSJ,
JZ.AJBH,
JZ.WSBH,
JZ.WSMC,
JZ.ZZZT,
JZPZ,
JZSHRBH,
JZSHR,
JZSHSJ,
JZ.JZBH,
jz.ZHXGSJ,
MBMC,
AJMB_BM,
jz.DWBM,
JZ.SFSC,
AJZT_F) T
where 1 = 1
and MBMC <> '模式'
and trim(AJLB_BM) = '0701'
and AJZT_F || AJLB_BM in ('0701', '00701', '30701', '40701')
and ZZZT in (2, 3, 4, 5, 6)
and SFSC = 'N'
and trim(CBDW_BM) in (select distinct trim(QXBM)
from xt_dm_qx a
join XT_QX_RYJSFP b
on trim(a.jsbm) = b.jsbm
and trim(a.bmbm) = b.bmbm
and trim(a.dwbm) = b.dwbm
and gh = '0000'
and b.dwbm = '440300000000'
and a.qxlx = 0)
and trim(AJLB_BM) in (select distinct trim(QXBM)
from xt_dm_qx a
join XT_QX_RYJSFP b
on trim(a.jsbm) = b.jsbm
and trim(a.bmbm) = b.bmbm
and trim(a.dwbm) = b.dwbm
and gh = '0000'
and b.dwbm = '440300000000'
and a.qxlx = 1)) TT
WHERE TT.Ro between 1 and 50
总数据量就5W。ZZZT匹配的量大就非常慢，匹配的量少就很快。in (select改为exists也不行。
...全文
相关推荐
SQL查询语句的练习
**优化查询** 了解查询优化技巧非常重要，这包括合理设计数据库结构、选择合适的索引、避免全表扫描、减少子查询和临时表的使用等。
经典SQL查询50题.pdf
通过对比这两种答案，我们可以看到实际应用中不同的SQL写法和可能的优化方法。
经典SQL查询50题
**索引优化**：理解索引的工作原理，如何创建索引，以及何时使用索引可以提高查询性能。10.
第9章查询优化.ppt
示例分析： - 求选修了2号课程的学生姓名，这个查询可以通过不同的方式实现，如嵌套子查询、连接操作等。优化器会评估每个计划的代价，选择执行成本最低的。
SQL Server 2005范例代码查询辞典
通过深入学习和实践，开发者可以灵活地结合这些函数解决复杂的查询需求，优化数据库性能，提供更加准确的业务洞察。
