标题: 创建了个分区表,但是解释sql语句时还是扫描的所有行 [打印本页] 作者: habwn 时间: 2014-3-26 11:22 标题: 创建了个分区表,但是解释sql语句时还是扫描的所有行 -- 创建分区表的sql
CREATE TABLE netflow_report(
netflow bigint,
stat_time datetime,
domain VARCHAR(100),
filename VARCHAR(255),
source VARCHAR(20))
PARTITION BY RANGE(TO_DAYS(stat_time))
(
PARTITION p_20140323 VALUES LESS THAN (TO_DAYS('2014-03-24')),
PARTITION p_20140324 VALUES LESS THAN (TO_DAYS('2014-03-25')),
PARTITION p_20140325 VALUES LESS THAN (TO_DAYS('2014-03-26')),
PARTITION p_20140326 VALUES LESS THAN (TO_DAYS('2014-03-27')),
PARTITION p_20140327 VALUES LESS THAN (TO_DAYS('2014-03-28')),
PARTITION p_20140328 VALUES LESS THAN (TO_DAYS('2014-03-29')),
PARTITION p_20140329 VALUES LESS THAN (TO_DAYS('2014-03-30')),
PARTITION p_20140330 VALUES LESS THAN (TO_DAYS('2014-03-31')),
PARTITION p_20140331 VALUES LESS THAN (TO_DAYS('2014-04-01'))
);
--在客户端解释的sql
select cast(substr(stat_time,1,16) as CHAR)stat_time,domain,
sum(FLOOR(netflow/(10*60))) as netflow
from netflow where stat_time between '2014-03-25 00:00:00'
and '2014-03-25 23:59:59'
group by substr(stat_time,1,16),domain
order by stat_time desc