select count(1) abc from (select * from T_BLOG_ARTICLE t where to_char(t.upload_time,'YYYY-MM-DD')>='2010-12-12' and to_char(t.upload_time,'YYYY-MM-DD')<='2014-10-10' order by article_id) ;
===========================
尽量不要用虚表,除非虚表中的记录量和主表记录量相比可以忽略不计。
然后,去掉Order By语句后,速度有明显提升吗?如果没有,那就不是Order By的问题
你条件有问题,不要用to_char,改正这样试试:
select count(1) abc from (select * from T_BLOG_ARTICLE t where t.upload_time>=to_date('2010-12-12' ,'yyyy-mm-dd') and t.upload_time<=to_date('2014-10-10','yyyy-mm-dd') order by article_id) ;
,对日期建立索引
这样速度会有一个明显的提升
create table T_BLOG_ARTICLE
(
article_id NUMBER not null,
user_id NUMBER,
article_title VARCHAR2(200),
article_content LONG,
articletype_id NUMBER,
subject_id NUMBER,
upload_time DATE,
check_flag NUMBER,
check_user NUMBER,
click_times NUMBER,
recommend_flag NUMBER,
order_index NUMBER,
cover_img VARCHAR2(300),
article_role NUMBER
)
tablespace JIAOYAN
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table T_BLOG_ARTICLE
is '博客文章表';
-- Add comments to the columns
comment on column T_BLOG_ARTICLE.article_id
is '博文id';
comment on column T_BLOG_ARTICLE.user_id
is '发布人id';
comment on column T_BLOG_ARTICLE.article_title
is '文章标题';
comment on column T_BLOG_ARTICLE.article_content
is '文章内容';
comment on column T_BLOG_ARTICLE.articletype_id
is '文章分类id';
comment on column T_BLOG_ARTICLE.subject_id
is '学科学段id';
comment on column T_BLOG_ARTICLE.upload_time
is '上传时间';
comment on column T_BLOG_ARTICLE.check_flag
is '0未屏蔽1屏蔽';
comment on column T_BLOG_ARTICLE.check_user
is '审核人';
comment on column T_BLOG_ARTICLE.click_times
is '点击次数';
comment on column T_BLOG_ARTICLE.recommend_flag
is '1推荐0不推荐';
comment on column T_BLOG_ARTICLE.order_index
is '排序顺序';
comment on column T_BLOG_ARTICLE.cover_img
is '封面图片';
comment on column T_BLOG_ARTICLE.article_role
is '显示权限 0所有人 1私有 2好友';
-- Create/Recreate indexes
create index USER_ID on T_BLOG_ARTICLE (USER_ID)
tablespace TSKPJIAOYAN
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_BLOG_ARTICLE
add constraint ARTICLE_ID primary key (ARTICLE_ID)
using index
tablespace TSKPJIAOYAN
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
select count(1) abc from (select * from T_BLOG_ARTICLE t where to_char(t.upload_time,'YYYY-MM-DD')>='2010-12-12' and to_char(t.upload_time,'YYYY-MM-DD')<='2014-10-10' order by article_id) ;
数据时百万级别的