Question

千万级数据量 我的sql 效率很慢：请问以下sql 如何优化

千万级数据量 我的sql 效率很慢：请问以下sql 如何优化

select distinct s.id
from student s,
(select id
from student
where biaozhi in ('0', '1') /*标志位 包含正常和关注类的 0-正常 1-关注*/
and yuwen<> 0 /*语文成绩不为0*/
group by id) tj
where s.id is not null
and s.score >= '500' /*总分大于500*/
and s.id = tj.id
and s.updatedate =
to_char(to_date('2012-12-04', 'yyyy-mm-dd'), 'yyyymmdd')

Answer 1

select distinct s.id
from student s,student tj
where (tj.biaozhi ='0' or tj.biaozhi= '1') and tj.yuwen<> 0 and s.id is not null
and s.score >= '500' /*总分大于500*/
and s.id = tj.id
and s.updatedate =
to_char(to_date('2012-12-04', 'yyyy-mm-dd'), 'yyyymmdd')
//group by tj.id

Answer 2

select distinct id
from student
where updaedate='20121204' and score>=500 and yuwen<>0 and biaozhi in ('0','1') and id is not null