1. 原SQL语句
SELECT TITLE, CODE, TYPE, YEAR, SS_DATE,URL
FROM (SELECT TITLE, CODE, TYPE,TO_CHAR(SS_DATE,'yyyy-mm-dd') SSBDATE,URL
FROM TABLE_A
WHERE 1=1 AND ISVALIDATE='1'
UNION ALL
SELECT TITLE,CODE,YEAR,SS_DATE,UBRL
FROM TABLE_B
WHERE 1=1 AND ISVALIDATE='1' )
WHERE 1=1
AND SS_DATE>='2019-07-09'
AND SS_DATE<='2019-07-10'
ORDER BY SS_DATE;
查询10条数据,执行计划查看要1.4秒以上。
2 改下后SQL语句:将to_char字段类型转换改成子查询外进行。
SELECT TITLE, CODE, TYPE, YEAR, SS_DATE,URL
FROM (SELECT TITLE, CODE, TYPE,SS_DATE,URL
FROM TABLE_A
WHERE 1=1 AND ISVALIDATE='1'
UNION ALL
SELECT TITLE,CODE,YEAR,SS_DATE,UBRL
FROM TABLE_B
WHERE 1=1 AND ISVALIDATE='1' )
WHERE 1=1
AND SS_DATE>=TO_DATE('2019-07-09','YYYY-MM-DD')
AND SS_DATE<=TO_DATE('2019-07-10','YYYY-MM-DD')
ORDER BY SS_DATE DESC;
查询计划发现,同样数据需要0.02秒。