一个查询优化问题急！

jam_hzh 2016-10-13 04:44:42

SELECT

  EC.EXE_RECORD_ID         AS exeRecordId,

  EC.INNER_CODE            AS innerCode,

  EC.EXE_TYPE              AS exeType,

  EC.EXE_DATE              AS exeDate,

  EC.EXE_STATUS            AS exeStatus,

  EC.EQUIP_ID              AS equipId,

  EC.EQUIP_TYPE            AS equipType,

  SD.STATE_DISPLAY_NAME    AS EQUIPTYPEZH,

  SD.STATE_DISPLAY_NAME_EN AS EQUIPTYPEEN

FROM TL_EXECCMDLOG EC

  LEFT JOIN TP_SYSBASEDICT SD

    ON SD.STATE_CODE = 'equipment_type'

       AND SD.STATE_VALUE = EC.EQUIP_TYPE

WHERE 1 = 1

      AND TO_CHAR(EC.EXE_DATE, 'YYYY-MM-DD') >= '2016-10-01'

      AND '2016-10-13' >= TO_CHAR(EC.EXE_DATE, 'YYYY-MM-DD')

      AND EC.INNER_CODE = 'WKQ'

ORDER BY EC.EXE_DATE DESC

左表300w+数据

右表为字典表，求大神帮优化，

...全文

609 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

小凤雏 2016-10-24

打赏
举报

数据量不是很大，如果超过千万级，考虑日期分区；这种百万级别的，使用索引就可以了，你上面如果对EC.EXE_DATE字段建立索引，这种情况估计会失效，因为索引字段上面不能使用函数计算等，考虑使用函数索引create index emp_ind on TL_EXECCMDLOG(TO_CHAR(EC.EXE_DATE, 'YYYY-MM-DD')) ，当然你也可以修改原来方法，使得索引有效； SELECT EC.EXE_RECORD_ID AS exeRecordId, EC.INNER_CODE AS innerCode, EC.EXE_TYPE AS exeType, EC.EXE_DATE AS exeDate, EC.EXE_STATUS AS exeStatus, EC.EQUIP_ID AS equipId, EC.EQUIP_TYPE AS equipType, SD.STATE_DISPLAY_NAME AS EQUIPTYPEZH, SD.STATE_DISPLAY_NAME_EN AS EQUIPTYPEEN FROM TL_EXECCMDLOG EC LEFT JOIN TP_SYSBASEDICT SD ON SD.STATE_VALUE = EC.EQUIP_TYPE AND SD.STATE_CODE = 'equipment_type' WHERE 1 = 1 and EC.EXE_DATE between to_date( '2016-10-01 00:00:00','yyyy-MM-dd hh24:mi:ss') AND to_date( '2016-10-13 23:59:59','yyyy-MM-dd hh24:mi:ss') AND EC.INNER_CODE = 'WKQ' ORDER BY EC.EXE_DATE DESC