工作中的一次SQL优化

先大致看下这个报表SQL：

SELECT ……
       o214015.DATE_POSTED     as E214040,
       o214015.POSTED_BY       as E214041,
       o214015.EXCHANGE_RATE   as E214042,
       o214015.WHSE            as E294709
  FROM CSNME.CSN_APPROVED_INVOICE o214015
 WHERE (o214015.POSTED_BY LIKE '218612')
   AND (o214015.DATE_POSTED BETWEEN '2017-07-24' AND '2017-07-24')

这个视图的核心部分：

select
……
 (select TO_CHAR(T.AUDIT_DATE, 'YYYY-MM-DD')
    from general_audits t
   where T.TABLE_NAME = 'OPENITEMS'
     AND T.AUDIT_INFORMATION = 'Invoice posted to Accounts Payable'
     AND T.AUDIT_INDEX LIKE (ap1.supplierno || '%' || ap1.invoiceno || '%')
     AND ROWNUM = 1) as date_posted,
 (select T.ENTERED_BY_EMPLOYEENO
    from general_audits t
   where T.TABLE_NAME = 'OPENITEMS'
     AND T.AUDIT_INFORMATION = 'Invoice posted to Accounts Payable'
     AND T.AUDIT_INDEX LIKE (ap1.supplierno || '%' || ap1.invoiceno || '%')
     AND ROWNUM = 1) as posted_by,
 ap1.exchange_rate
  from a_p_work1 ap1
 where ap1.update_flag = 'Y'
 …… 

两个参数：员工号和时间。 先是查询a_p_work1 ap1这个主表，然后在general_audits t表里面 进行标量子查询进行过滤。过滤条件放在了子查询确实很奇葩。这也是导致慢的因素。 这个速度据用户反应大概要4个小时，早上点击一下查询，然后下午等结果！！！！！

接下来进行优化：
1 添加必要的索引
有一个简单的概念，Oracle中sql会进行查询转换，而对于视图来说，会进行视图合并。简单来说，视图外的条件，会放到里面去执行。
因此标量子查询其实是这样的：

  select T.AUDIT_INDEX
   from general_audits t
  where T.TABLE_NAME = '……'
    AND T.AUDIT_INFORMATION = '……'
    and T.ENTERED_BY_EMPLOYEENO = '员工号'
    and T.DATE_POSTED = to_date(……)

然后再执行这样的

  T.AUDIT_INDEX LIKE (ap1.supplierno || '%' || ap1.invoiceno || '%')  

一个模糊匹配，这个匹配的处理最后说。

然后对这四个条件，建立联合索引。建立索引后，可以看到索引的效率是极高的，和表的数据量基本上没有关系，本张表有超过2500万条数据。

上面第一步优化结束。

2 对于时间字段
不要将其转成字符串，再和字符串进行比较；而应该将字符串转为date，然后和数据库中的date进行比较。
本sql中的具体问题如下：

前者的弊端：
表中有多少条数据就会调用多次转换函数，这张表有70万条，就会进行70万次转化，用第二种方式，只会调用2次转换函数。
时间的比较要比字符串快一些，因为时间的比较直接是数字或者说是整数的比较，这个整数就是时间减去1970年1月1日的值。 因此这个地方要改写，这个改写很简单，不再叙述。

（下面两个问题更重要一点）
3 标量子查询中的 rownum = 1是否可以去掉

general_audits和 a_p_work1 ap1进行连接查询，还有其他表里子查询中的条件，如果查出来的值audit_date 和 entered_by_employeeno 没有重复的，那么rownum=1就可以去掉。
当然如果没有重复的加rownum=1 就是多余的。但是有人写sql就会出现这样的错误。我们需要优化的往往都是有问题的sql，各样问题都有可能出现，所以并不需要对这个问题感到奇怪。
如果rownum=1 不能去掉，那么就要用聚集函数max去处理。

4 现在就要将子查询转为连接，那么什么情况下子查询可以转为左连接，什么情况下转为内连接呢?
还是根据audit_date 和 entered_by_employeeno这两个值进行判断，如果没有null出现，那么就可以转为内连接，如果有null出现，那么就要转为左连接。这个结论没有问题，大家可以验证。根据我们的判断，我们的sql这两个值不可能为null，因为这两个正好是我们的查询条件，自然不会有null被查询出来。

3 和4这两个问题，可以用暴力方式直接修改，然后比对前面的结果是否一致。由于本sql查询需要 4个小时，所以暴力方式不能使用了。

5 本次sql优化的个性问题：

经过上面的优化，现在变成了两个表的内连接查询general_audits和 a_p_work1 ap1 。
但是中间的判断条件是：

T.AUDIT_INDEX LIKE (ap1.supplierno || '%' || ap1.invoiceno || '%') 

我们开始处理这个模糊匹配
1 确定模糊匹配，匹配的是什么数据。
经过我们查看数据比对和一点点的直觉判断，这个匹配，其实只是匹配的是“空格”，并非具体的值。因此这个模糊匹配实质上是一个等值查询。
数据类似下面：

2 对AUDIT_INDEX的值进行切割然后使用等值。
经过观察数据，发现两个字段直接有的有空格，有的没有，空格的数量也不确定，不能根据空格来切割，所以用%来连接两个字段也是有道理的。
又发现第一个好像全是字母，第二个字段全是数字，不过很快被否定。事情就僵在了这儿，因此前人这样写 T.AUDIT_INDEX LIKE (ap1.supplierno || ‘%’ || ap1.invoiceno || ‘%’)
也无可厚非。（PS ：AUDIT_INDEX后面还有其他内容）

3 灵光一现：
在多次观察这个SQL之后，发现ap1.supplierno这个字段的长度为6，这个就好办了，我们只要截取AUDIT_INDEX前6个字符就好了。果然发现那些空格就是为了补充supplierno这个长度的不足以到6位。

（PS：优化前的sql，大概要4个小时，优化后秒处，1s左右，不超过5s）

优化后的sql的大概的样子：

select 
       to_char(o214015.DATE_POSTED, 'YYYY-MM-DD') as DATE_POSTED,
       o214015.POSTED_BY,
       o214015.EXCHANGE_RATE,
       o214015.WHSE
  FROM csnme.csn_approved_ice_bak_txc o214015
 WHERE (o214015.POSTED_BY =‘1111’)
   AND (o214015.DATE_POSTED BETWEEN to_date('2017-08-29', 'YYYY-MM-DD') AND
       to_date('2017-08-31', 'YYYY-MM-DD'))
 order by REMIT_NAME

视图核心sql

select t1.audit_date            as date_posted,
       t1.entered_by_employeeno as posted_by,
       ap1.exchange_rate

  from a_p_work1 ap1
 inner join (select T.AUDIT_INDEX,
                    t.entered_by_employeeno,
                    t.audit_date,
                    trim(substr(t.AUDIT_INDEX, 0, 6)) as supplierno_s,
                    trim(substr(t.AUDIT_INDEX,  7, instr(t.AUDIT_INDEX, ' ', 7, 1))) as invoiceno_s
               from general_audits t
              where T.TABLE_NAME = 'OPENITEMS'
                AND T.AUDIT_INFORMATION =
                    'Invoice posted to Accounts Payable') t1

    on (t1.supplierno_s = ap1.supplierno and ap1.invoiceno = t1.invoiceno_s)

 where ap1.update_flag = 'Y'

索引，时间格式处理，内连接，字符串切割都有了。