创建函数索引优化一条SQL

开发发来一条SQL让优化

Select Distinct Oi.Customer_Id, Oi.Order_Id, Oi.Create_Time, 1 Type
  From t_To_Order_Info Oi
  Left Join t_To_Consign_Pymt_Record Cpr
    On Oi.Order_Id = Cpr.Order_Id
 Where 1 = 1
   And Exists (Select 1
          From t_To_Order_Tickets
         Where t_To_Order_Tickets.Order_Id = Oi.Order_Id
           And To_Char(Departure_Time, 'yyyyMMdd') =
               To_Char(Sysdate, 'yyyyMMdd'))
   And Not Exists
 (Select 1 From t_To_Order_Change Oc Where Oc.Order_Id = Oi.Order_Id)
   And ((Cpr.Pymt_Type = 1 Or
       Cpr.Pymt_Type = 3 And Oi.Guest_Pymt_Sts_Id = 2) Or
       (Cpr.Pymt_Type = 2 And
       Cpr.Partner_Id In
       (Select p.Partner_Id
            From t_Tp_Partner p
           Where p.Is_Company_Inner = 1) And Oi.Guest_Pymt_Sts_Id = 2) Or
       (Cpr.Pymt_Type = 2 And
       Cpr.Partner_Id In
       (Select p.Partner_Id
            From t_Tp_Partner p
           Where p.Agent_Class_Id = 1
             And p.Is_Company_Inner != 1) And
       To_Char(Oi.Issue_Time) <> ' ') Or
       (Cpr.Pymt_Type = 2 And
       Cpr.Partner_Id In
       (Select p.Partner_Id
            From t_Tp_Partner p
           Where p.Agent_Class_Id != 1) And To_Char(Oi.Issue_Time) <> ' '))

这么一条SQL，要跑30多秒

我第一的想法是看执行计划，t_to_order_info和t_TO_Order_Tickets这两张表是hash连接，但是两表走的是全表扫描，我首先是将目标定向了t_to_order_info走全表扫描的原因，以为是exists造成的，在这里我并不知道是哪里没走索引造成的，我将SQL多运行了几次，然后再em工具里面的TOP SQL里面找到了这条SQL，看了oracle给的建议

首先运行了如下命令收集了索引的统计信息
dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'IDX_BT_T_TO_ORDER_HANG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'TO_RELATIONCHANGE_FK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'PK_T_TO_ORDER_CHANGE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);
end;

做完次操作后效率没有提高，再看到oracle给的一个建议，就是说
t_TO_Order_Tickets
        wheret_TO_Order_Tickets.Order_ID = oi.Order_ID
          andto_char(Departure_Time,'yyyyMMdd') =
               to_char(sysdate,'yyyyMMdd'))
这里出了问题，Departure_Time本来是有索引的，但是这里没有走索引，因为to_char的方式无法让它再走索引了，
这是问题的关键，这种问题可以通过函数索引来解决


SQL> create  index TEST20110217.IDX_HANSU on TEST20110217.T_TO_ORDER_TICKETS(TO_CHAR("DEPARTURE_TIME",'yyyyMMdd'));
SQL> exec dbms_stats.gather_index_stats('TEST20110217','IDX_HANSU');

上面创建了函数索引变收集了新索引的统计信息，SQL的效率得到了大大的提高，差不多1秒之类就OK了，同时验
证了一点就是一个列上函数索引和普通索引可以并存他们各走各的，不影响性能，问题本来在这里就已经解决了了
，但是开发的后面需要在DEPARTURE_TIME+1这将再次引起不走索引的情况，那么必须通过改写SQL的方式来解决

and    Departure_Time between to_date( to_char(sysdate-1,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS')
                                           and to_date( to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS'))

其实问题的关键就在departure_time，用上面这种方式不变departure_time，改变等号的那边方可解决走索引的问题。

总结：
整个问题的关键地方是如何定位到问题是departure_time列不走索引而引起的性能问题，
这次是通过em工具找出来的提醒，那么如果下次在em工具中找不到TOPSQL,自己如何分
析定位到这个点上面来。t_TO_Order_Tickets这张表后面where字段有等号的地方，就
是这种字段。这次调优没有经验，下次在看到如果在时间字段上面做了手段，就应该将
问题快速的定位到这个上面来