oracle语句优化--函数索引

待优化语句执行大概需要一分钟：

select PO.SOURCECODE OT_CUSID,
       PO.MERCHANT_NO OT_ACCTNO,
       case
           when OT.TRAN_NO is null then
            ZD.CHECKNO
           else
            OT.TRAN_NO
       end OT_TRAN_NO,
       (select DICT_CODE_LOINC
          from COM_DICT
         where DICT_TYPE = 'CHANNEL_CODE'
           and DICT_CODE = OT.CHANNEL_CODE) OT_CHANNEL_CODE,
       case
           when OT.CHANNELTRANSNO is null then
            ZD.CHECKREFNO
           else
            OT.CHANNELTRANSNO
       end OT_CHANNELTRANSNO,
       case
           when OT.TRAN_NO is null then
            ZD.CHECKNO
           else
            OT.TRAN_NO
       end ZD_CHECKNO
  from ACC_CHECKBATCHDETAIL ZD
  left join (select *
               from PAY_ORDERTRANS T
              where T.ORDER_STATE in ('05', '02')
                and TO_CHAR(T.FINISH_DATE, 'YYYY-MM-DD') = '2019-09-16') OT
    on ZD.CHECKNO in
       (OT.CHANNELTRANSNO,
        OT.BANK_ORGTRANDATE || SUBSTR(OT.CHANNELTRANSNO, -6))
   and OT.ORDER_STATE = ZD.TRANSTATE
  left join (select *
               from PAY_ORDER
              where TO_CHAR(ORD_DATE, 'YYYY-MM-DD') = '2019-09-16') PO
    on OT.ORDER_NO = PO.ORDER_NO
 where TO_CHAR(TRANDATE, 'YYYY-MM-DD') = '2019-09-16';

查看执行计划，发现有两处全表扫描：

• ACC_CHECKBATCHDETAIL
• PAY_ORDERTRANS

对ACC_CHECKBATCHDETAIL的CHECKNO，TRANSTATE字段建索引，执行后效果没有大的改善。
对PAY_ORDERTRANS 的FINISH_DATE字段建函数索引后，执行时间得到大的改善：

create index pk_tochar_date on PAY_ORDERTRANS (TO_CHAR(FINISH_DATE, 'YYYY-MM-DD'));

表PAY_ORDERTRANS 的执行计划改为走索引pk_tochar_date，CPU消耗及IO消耗都降到了原百分之一左右。语句的总执行时间也降到了秒级，于是停止优化。