一个同行发来的一个SQL查询慢的问题。
原SQL:
WITH w AS
(SELECT DISTINCT s.tel, c.mainnum
FROM T_INFO_VIOLATEDPEOPLE s, CASEFILE c
WHERE INSTR(s.tel, c.mainnum) > 0)
SELECT w.mainnum, w.tel
FROM T_INFO_CASETOPEOPLE p, T_A_CASEINFO t, T_INFO_VIOLATEDPEOPLE s, w
WHERE s.ID = p.peopleid
AND t.ID = p.caseid
AND p.peopletype = 'vio'
AND p.DELFLAG = '0'
AND s.tel IS NOT NULL
AND s.tel = w.tel
虽然他这里没有必要使用WITH 这么写,但是先不管。先看慢在哪里。(由于执行计划发的是截图,QQ文件整理的时候图片被清空了。这里就说思路吧)
刚发过来的时候也没在意,先是把SQL改写了下,用了几个HINT试下看看有效果没,果然,NO FUNCTION...
那就慢慢找原因。让他执行了一下WITH里面的语句,执行很长时间都没出结果,索性直接叉掉,问题是不是出这里不确定,但是确定的是这里肯定有问题。
让他看下T_INFO_VIOLATEDPEOPLE 和 CASEFILE 表行数。分别是160W+ 和 1000+。
由于DISTINCT 和 INSTR(s.tel, c.mainnum) > 0 的原因,原SQL不好走正确的索引(GROUP BY两张表)。
所以最后的思路是使用UNION来改写。
CREATE INDEX IDX_TEL_REVERSE ON T_INFO_VIOLATEDPEOPLE(REVERSE(TEL));
CREATE INDEX IDX_TEL ON T_INFO_VIOLATEDPEOPLE(TEL,1);
SELECT /*+ leading(B) use_nl(A,B) index(A IDX_TEL_REVERSE) */
A.TEL, B.MAINNUM
FROM T_INFO_VIOLATEDPEOPLE A
INNER JOIN CASEFILE B
ON REVERSE(A.TEL) LIKE REVERSE(B.MAINNUM) || '%'
UNION
SELECT /*+ leading(B) use_nl(A,B) index(A IDX_TEL) */
A.TEL, B.MAINNUM
FROM T_INFO_VIOLATEDPEOPLE A
INNER JOIN CASEFILE B
ON A.TEL LIKE B.MAINNUM || '%'
WHERE A.TEL IS NOT NULL;
创建一个反转索引,再使用UNION把原SQL拆分成两块,索引走了index range scan。终于是K.O了。这里也是有凑了个巧,他的返回结果集很少,也就20多行。这块解决了,然后直接放回原SQL,整个SQL也是很快的出结果了。那么说明,问题就出在这里了。
由于我也不是专职做SQL优化的,优化SQL也没有什么一套正规模板步骤之类的,最终目的就是提高查询速度。还请路过的大神不要见笑。