select t2.terminalid,nvl(t1.cnt,0) count from (select terminalid,count(terminalid) cnt from EC_DATA_HOUR_INTERVAL
where TIMEDATA>=to_date('2012-02-02','yyyy-mm-dd') and
TIMEDATA<=to_date('2012-02-3','yyyy-mm-dd') and terminalid in('AAA')
group by terminalid) t1,(select decode(rownum,1,'AAA') terminalid from dual connect by rownum <= 1) t2
where t2.terminalid=t1.terminalid(+) and t2.terminalid is not nullterminalid建立了索引
执行要花费十几秒
查询一个月的数据浏览器卡死。求优化
where TIMEDATA>=to_date('2012-02-02','yyyy-mm-dd') and
TIMEDATA<=to_date('2012-02-3','yyyy-mm-dd') and terminalid in('AAA')
group by terminalid) t1,(select decode(rownum,1,'AAA') terminalid from dual connect by rownum <= 1) t2
where t2.terminalid=t1.terminalid(+) and t2.terminalid is not nullterminalid建立了索引
执行要花费十几秒
查询一个月的数据浏览器卡死。求优化
where TIMEDATA>=to_date('2012-02-02','yyyy-mm-dd') and
TIMEDATA<=to_date('2012-02-3','yyyy-mm-dd') and terminalid in('AAA')
group by terminalid) t1,(select decode(rownum,1,'AAA') terminalid from dual connect by rownum <= 1) t2
where t2.terminalid=t1.terminalid(+) and t2.terminalid is not null红色的可以使用=号,别的就这个sql来讲,没什么了应该,再请别的大神看看吧
NVL(t1.cnt,0) AS COUNTED
FROM
(SELECT terminalid,
COUNT(terminalid) cnt
FROM EC_DATA_HOUR_INTERVAL
WHERE TIMEDATA >=to_date('2012-02-02','yyyy-mm-dd')
AND TIMEDATA <=to_date('2012-02-3','yyyy-mm-dd')
AND terminalid IN ('AAA')
GROUP BY terminalid
) t1,
(--没看明白,为什么要做一个没用的中间表
SELECT DECODE(rownum,1,'AAA') terminalid FROM dual CONNECT BY rownum <= 1
) t2
WHERE t2.terminalid=t1.terminalid(+)--这种写法等同于LEFT JOIN, 实在无法利用到索引
AND t2.terminalid IS NOT NULL --NOT NULL的用法,无论你是否建立索引,直接进行全表扫描--猜测这样写的目的是想先把全表有可能的TERMINALID选择出来,在指定时间跨度内如果terminalid没有记录,则显示统计为0.
--试试这种写法,不过效率也不高。SELECT a.terminalid,
NVL(b.cnt,0) AS counted
FROM
(SELECT terminalid,
COUNT(1) AS cnt
FROM EC_DATA_HOUR_INTERVAL
GROUP BY terminalid
) a
JOIN
(SELECT terminalid,
COUNT(1) AS cnt
FROM EC_DATA_HOUR_INTERVAL
WHERE TIMEDATA >=to_date('2012-02-02','yyyy-mm-dd')
AND TIMEDATA <=to_date('2012-02-3','yyyy-mm-dd')
AND terminalid = 'AAA'
GROUP BY terminalid
) b
ON a.termInalid = b.termInalid
terminalid in('AAA') 只是举例只有一条数据条件的时候,不是永远都是AAA。
FROM (SELECT TERMINALID, COUNT(TERMINALID) CNT
FROM EC_DATA_HOUR_INTERVAL
WHERE TIMEDATA BETWEEN TO_DATE('2012-02-02', 'yyyy-mm-dd')
AND TO_DATE('2012-02-3', 'yyyy-mm-dd')
AND TERMINALID = 'AAA'
GROUP BY TERMINALID) T1,
(SELECT DECODE(ROWNUM, 1, 'AAA') TERMINALID
FROM DUAL
CONNECT BY ROWNUM <= 1) T2
WHERE T2.TERMINALID = T1.TERMINALID(+)
AND T2.TERMINALID IS NOT NULL
你应该把执行计划给弄出来看看,