两张表:SQJZ_GYLD社区服务(1147502条记录),SQJZ_GYLD_JZDX社区劳动参加人员列表(2422060条记录)
SQJZ_GYLD建立的索引有:
SQJZ_GYLD_JZDX建立的索引有:
现要查询某区那天某个时刻之前参加过的劳动人员:
SELECT J.PNAME
FROM SQJZ_GYLD_JZDX J,SQJZ_GYLD D
WHERE D.ID = J.GYLD_ID
AND D.QUXIAN_ID='67840'
AND TO_CHAR(D.LAODONGRIQI, 'YYYY-MM-DD') = '2018-09-19'
AND TO_CHAR(J.QIANDAOSHIJIAN, 'YYYY-MM-DD HH24:MM:SS') <= '2018-09-19 18:00:00'
AND J.QIANTUISHIJIAN IS NULL
发现用了5.556秒,实在无法忍受,看下Explain Plan:
发现使用了全表扫描;
优化一下:
SELECT J.PNAME
FROM SQJZ_GYLD D, SQJZ_GYLD_JZDX J
WHERE D.ID = J.GYLD_ID
AND D.QUXIAN_ID='67840'
AND D.LAODONGRIQI BETWEEN TO_DATE('2018-09-19 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2018-09-19 23:59:59', 'YYYY-MM-DD HH24:MI:SS')
AND TO_CHAR(J.QIANDAOSHIJIAN, 'YYYY-MM-DD HH24:MI:SS') <= '2018-09-19 18:00:00'
AND J.QIANTUISHIJIAN IS NULL
用了3.777秒,还是无法忍受,Explain Plan如下:
SQJZ_GYLD_JZDX表进行了全表扫描;
继续优化:
SELECT J.PNAME
FROM SQJZ_GYLD_JZDX J,SQJZ_GYLD D
WHERE D.ID = J.GYLD_ID
AND D.QUXIAN_ID='67840'
AND J.LTS>=TO_DATE('2018-09-19', 'YYYY-MM-DD')
AND TO_CHAR(D.LAODONGRIQI, 'YYYY-MM-DD') = '2018-09-19'
AND TO_CHAR(J.QIANDAOSHIJIAN, 'YYYY-MM-DD HH24:MM:SS') <= '2018-09-19 18:00:00'
AND J.QIANTUISHIJIAN IS NULL
用了0.015秒。
这里加了J.LTS>=TO_DATE('2018-09-19', 'YYYY-MM-DD'),注意LTS条件要对查询结果没有影响,但是却使SQJZ_GYLD_JZDX进行了索引扫描,Explain Plan如下: