CREATE TABLE IF NOT EXISTS YK_VIDEO_STAT (
DATE DATE NOT NULL,
VIDEOID VARCHAR NOT NULL,
DEVICE VARCHAR NOT NULL,
VV BIGINT,
TS BIGINT,
COMMENT BIGINT,
UP BIGINT,
DOWN BIGINT,
FAVOR BIGINT,
QUOTE BIGINT
CONSTRAINT PK PRIMARY KEY (DATE DESC, VIDEOID, DEVICE)
) VERSIONS=1,COMPRESSION='SNAPPY';
this is the ddl of my table.there will have 15 million rows every day,so 3 month will
be 1350 million. (videoid in (1~200 million interger,every day only 10 million distinct),device in ('phone','pad','pc','other'))
Running a query similar to the following costs at least 40s,it is too long.Can anybody give me some suggestions if query at least in the 3 month range ?
select date,videoid,device,sum(vv),sum(ts),sum(comment),sum(up),sum(down),sum(favor),sum(quote) from yk_video_stat where videoid=10034569 and date >= to_date('20130901','yyMMdd') and date<=to_date('20130910','yyMMdd')
CREATE TABLE IF NOT EXISTS YK_VIDEO_STAT ( DATE DATE NOT NULL, VIDEOID VARCHAR NOT NULL, DEVICE VARCHAR NOT NULL, VV BIGINT, TS BIGINT, COMMENT BIGINT, UP BIGINT, DOWN BIGINT, FAVOR BIGINT, QUOTE BIGINT CONSTRAINT PK PRIMARY KEY (DATE DESC, VIDEOID, DEVICE) ) VERSIONS=1,COMPRESSION='SNAPPY';
this is the ddl of my table.there will have 15 million rows every day,so 3 month will be 1350 million. (videoid in (1~200 million interger,every day only 10 million distinct),device in ('phone','pad','pc','other')) Running a query similar to the following costs at least 40s,it is too long.Can anybody give me some suggestions if query at least in the 3 month range ? select date,videoid,device,sum(vv),sum(ts),sum(comment),sum(up),sum(down),sum(favor),sum(quote) from yk_video_stat where videoid=10034569 and date >= to_date('20130901','yyMMdd') and date<=to_date('20130910','yyMMdd')