"如果不加a.end_date > to_date('20031010'),速度很快,10秒搞定" 那么肯定是索引起了反作用,改成 select count(*) from tf_call a, tf_vip b where (a.calling_code = b.serv_code or a.called_code=b.serv_code) and trunc(a.end_date) > to_date('20031010','yyyymmdd');
alter table tf_call add paitition ADD PARTITION p1 VALUES LESS THAN(to_date('20011010','yyyymmdd'); alter table tf_call add paitition ADD PARTITION p2 VALUES LESS THAN(to_date('20021010','yyyymmdd'); alter table tf_call add paitition ADD PARTITION p3 VALUES LESS THAN(to_date('20031010','yyyymmdd'); alter table tf_call add paitition ADD PARTITION p1 VALUES LESS THAN(to_date(MAXVALUE); 根本想改变sql性能,是从表结构做起,若tf_call一天可产生上万条记录,哪可用分区表方法. 至于trunc(a.end_date)可以做一个函数索引 create index ix_tf_call on tf_call(trunc(end_date));
那么肯定是索引起了反作用,改成
select count(*) from tf_call a, tf_vip b
where
(a.calling_code = b.serv_code or a.called_code=b.serv_code)
and trunc(a.end_date) > to_date('20031010','yyyymmdd');
alter table tf_call add paitition ADD PARTITION p2 VALUES LESS THAN(to_date('20021010','yyyymmdd');
alter table tf_call add paitition ADD PARTITION p3 VALUES LESS THAN(to_date('20031010','yyyymmdd');
alter table tf_call add paitition ADD PARTITION p1 VALUES LESS THAN(to_date(MAXVALUE); 根本想改变sql性能,是从表结构做起,若tf_call一天可产生上万条记录,哪可用分区表方法.
至于trunc(a.end_date)可以做一个函数索引
create index ix_tf_call on tf_call(trunc(end_date));
方法2: 使用count(字段名)而不是count(*),此处的字段名是一个有效索引列.最好在使用此索引前先rebuild