调优SQL Statement

问题消失了，没有调整什么，本来2个小时的running，现在回到正常的20分钟了，奇怪得很！也没有办法重新问题了！多谢大家的建议！收起

回复 10# l954368978

这个可以靠调整比较操作符实现，>改>=什么的。收起

回复 8# shadowflare


   隐式转换成时间戳类型的话，会导致有可能不是自己想要的结果，比如'2015-04-21'转换后是'2015-04-21-00.00.00.000000'，而实际想要的是'2015-04-21-23.59.59.999999'收起

回复 7# l954368978
不一定，有些函数和表达式还是可以使用索引的。已知的是各种转换数据类型的函数和表达式，只要这种转换是安全转换（转换后类型可兼容转换前类型，不丢失信息）。收起

回复 6# steve25
不用date，系统会自动把右边的字符串转timestamp类型，这点智能化还是有的。收起

把'2015-04-21'这种参数按照timestamp类型写就行了，换一种思路而已。在字段上有函数或者其他运算的话，即使有索引也无法使用收起

是timestamp类型，一定要加date()才行

CREATION_TS
--------------------------
2005-04-11-08.19.57.583124
2005-04-11-08.20.02.205117收起

n.CREATION_TS这些是否都是符合’YYYY-MM-DD‘格式的字符串，是的话所有date()都可以去掉，直接引用字段即可。套上date()会影响选择度的计算，使返回行数的估计值偏大，从而倾向于使用不用索引的执行计划。收起

现有执行计划可以贴一下不？

( ((date(n.CREATION_TS)>'2015-04-21') and (date(n.CREATION_TS)<='2015-04-22')) or
           ((date(i.CREATED_TS)>'2015-04-21') and (date(i.CREATED_TS)<='2015-04-22')) or
           ((date(n.UPDATED_TS)>'2015-04-21') and (date(n.UPDATED_TS)<='2015-04-22')) or
           ((date(i.UPDATED_TS)>'2015-04-21') and (date(i.UPDATED_TS)<='2015-04-22')) or
           ((i.ACTION_DT>'2015-04-21') and (i.ACTION_DT<='2015-04-22')) or
           ((c.OUTCOME_DT>'2015-04-21') and (c.OUTCOME_DT<='2015-04-22')) )
看看几个字段的过滤度怎么样。这些字段上是否建了索引？ 如果有建索引，索引是怎么建的，这些字段上能否改为不使用函数的？收起

SQL:
select distinct n.NOTE_AGENCY as Agency, n.DISPLAY_NOTE_TYPE_CD as NoticeType, n.NOTE_NO as NoticeNumber,
       n.INFRINGEMENT_TS as OffenceTime,
       date(n.CREATION_TS) as NoticeEnteringDate,
       (case when n.UPDATED_TS >  i.UPDATED_TS then date(n.UPDATED_TS) else date(i.UPDATED_TS) end) as UpdatingDate,
       n.ISSUING_OFCR as IssuingMember,
       n.ROAD_NAME as Street,
       n.SCENE_STTN_CD as SceneStation,
       n.LOCAL_ATHY_CD as LocalAuthority,
       n.UPDATED_BY as NoticeUpdater,
       substr(n.SECTOR, 1, 6) as Sector,
       i.IFMT_NUMBER as OffenceNumber,
       substr(i.JUSTICE_OFFENCE, 1, 4) as PrecedentCode,
       i.IFMT_STATUS_CD as OffenceStatus,
       i.ACTION_DT as ActionDate,
       date(i.CREATED_TS) as InfringementEnteringDate,
       i.FEE as Amount,
       i.EXCESS_SPEED as Excess,
       i.UPDATED_BY as InfringementUpdater,
      c.OUTCOME_DT as OutcomeDate,
       c.OUTCOME_TYPE_CD as OutcomeType
from PIPS.NOTICE n
inner join PIPS.INFRINGEMENT i on (i.NOTICE_ID=n.NOTICE_ID) left outer join PIPS.COURT_CASE c on (c.NOTICE_ID=i.NOTICE_ID and c.IFMT_NUMBER=i.IFMT_NUMBER) where
          n.NOTE_TYPE_CD in (1,3,4,5,10) AND
         ( ((date(n.CREATION_TS)>'2015-04-21') and (date(n.CREATION_TS)<='2015-04-22')) or
          ((date(i.CREATED_TS)>'2015-04-21') and (date(i.CREATED_TS)<='2015-04-22')) or
          ((date(n.UPDATED_TS)>'2015-04-21') and (date(n.UPDATED_TS)<='2015-04-22')) or
          ((date(i.UPDATED_TS)>'2015-04-21') and (date(i.UPDATED_TS)<='2015-04-22')) or
          ((i.ACTION_DT>'2015-04-21') and (i.ACTION_DT<='2015-04-22')) or
          ((c.OUTCOME_DT>'2015-04-21') and (c.OUTCOME_DT<='2015-04-22')) )
         AND(i.IFMT_STATUS_CD in (1,2,3,7,8,9,10,11,13) or (i.IFMT_STATUS_CD = 6 and c.outcome_type_cd = 26 ) or (i.IFMT_STATUS_CD in (4, 5) and ( c.outcome_type_cd is null or c.outcome_type_cd in (6,17,18,26,38,39)))
    )
AND n.NOTE_TYPE_CD not in(8, 9)
and n.note_status_cd > 0
====
--
--
-- LIST OF RECOMMENDED INDEXES
-- ===========================
--  no indexes are recommended for this workload.


--
--
-- RECOMMENDED EXISTING INDEXES
-- ============================收起