目前项目组中的 分页语句, 存在 很大问题, 按照 道理来说 分页语句首页 都不会很慢的。 结果应该是 秒出的。 但我们项目组中 的分页, 哥首次优化时候, 出现35S, 这个肯定不行的。
SQL 涉及到保密问题, 简写
select b.*
from (select rownum as r, a.*
from (select smzor.AREA_NAME, **************************
from ST_MNTR_ZD_ORDER_REPORT smzor, dim_area da
where smzor.local_area_id = 3
and smzor.area_id = da.area_id
order by smzor.HWY_RETURN_FLAG desc,
smzor.HWY_RETURN_DT desc nulls last) a) b
where b.r >= 1
and b.r <= 20;
哥一看 这个SQL, 发现 dim_area 这个表莫, 很明显 这个 过滤数据用的, 测试下 果然 是的, 这个表 对于的数据是 n:1, 而且 select 后面的数据都是 zmzor 中的。
所以 一看 改成 半连接, 再一看 order by 我靠啥需求, 明显的全表扫描, 而且还是 desc 排序, 而且还是 null 最后。关键的是 妈的 最后 r>=1 , r<20 , 这个肯定不合理, 要想 排序 秒出, 必须建索引了, 索引见起来 还得慎重,
哥考虑 再三 结果 建索引 idx_lhd ( local_area_id , HWY_RETURN_FLAG desc, HWY_RETURN_DT, 0 )
select b.*
from (select rownum as r, a.*
from (select /*+ index( idx_lhd smzor ) */ smzor.AREA_NAME, **************************
from ST_MNTR_ZD_ORDER_REPORT smzor
where smzor.local_area_id = 3
and smzor.area_id = da.area_id
and exists ( select /*+ no_unnest */ 1 from dim_area da where smzor.area_id = da.area_id )
order by smzor.HWY_RETURN_FLAG desc,
smzor.HWY_RETURN_DT desc nulls last) a) b
where b.r >= 1
and b.r <= 20;
核对数据, 前后数据一致, 但前面的 35s 左右, 后面的 0.12s 左右 。
两次的计划
前后数据对比发现 前面扫了 很多很多, 后面 扫了 10条, 所以 哥这个改写 结果秒出了。。。。。 吼吼.......
哥当时的思路是 先是小表 驱动大表, 运用 试图推入 吧 rownum<=10 推入到扫描中, 建索引, 利用索引的有序性, 扫描数据, 如何建索引, 关键是看 过滤条件和 连接条件。 结果哥写了 好多的hint 发现 很难写出这个理想的hint, 后来 把SQL拆掉 搞了, 发现 只有 去掉 脏数据 过滤 条件 速度才提高上来。 于是哥想到了 no_unnest 这个hint 结果 给解了, 看来哥要 好好理解 这个hint 了.... 确实有用的 。。。。
哥一直努力的方向........................SQL 优化。