大表小表做包含连接的SQL优化

一个同行发来的一个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也没有什么一套正规模板步骤之类的，最终目的就是提高查询速度。还请路过的大神不要见笑。