请问如何优化这条语句:
select * from info where handlestate not in ('预审','草稿') and curstime>'2010-01-01' order by curstime desc;
其中:
HANDLESTATE NOT NULL VARCHAR2(20);
CURSTIME VARCHAR2(32); //时间竟然用字符型!怎么建索引,怎么优化语句。谢谢!
select * from info where handlestate not in ('预审','草稿') and curstime>'2010-01-01' order by curstime desc;
其中:
HANDLESTATE NOT NULL VARCHAR2(20);
CURSTIME VARCHAR2(32); //时间竟然用字符型!怎么建索引,怎么优化语句。谢谢!
直接在curstime上创建索引试试。
select * from info where curstime>'2010-01-01' and handlestate not in ('预审','草稿') order by curstime desc;
但好象执行时间没明显改善(有用到索引)。另外有资料说not in的效率很低,不知该怎么改。
curstime列上建立聚集索引,另外order by 影响性能,可以考虑放在前端排序
not in 是效率极低的写法,尽量使用minus或外连接加以替代
典型实例:
1) select col1 from tab1 where col1 not in (select col1 from tab2);
2) select sum(col2) from tab1 where col1 not in (select col1 from tab2);
替代方案
select col1 from tab1 minus select col1 from tab2;
select sum(a.col2) from tab1 a, tab2 b
where a.col1=b.col2(+) and b.col1 is null;
and curstime>'2010-01-01' 上面两个条件放前放后,确实不好确定, 看看那个数据量少就把那个放到后面,
order by curstime desc;
最好不要排序;
可以把该字段建立索引,
oracle QQ群:54775466
欢迎大家来一起探讨。