select count(*) from hn_guest where g_gname like '孙%' 100万中,速度也很快啊,
一用到like时,oracle就会进行全表扫描
可不可以试试子查询? select count(*) from (select * from hn_guest where g_gname like '孙%') as a where a.g_indate>'200405010000' and a.g_indate<'200504010000' 反正select count(*) from hn_guest where g_gname like '孙%'才三十多条, 速度应该和select count(*) from hn_guest where g_gname like '孙%'速度差不多才是
Oracle好像不用写as,写了反而报错,试试这个: select count(*) from (select * from hn_guest where g_gname like '孙%') t1 where t1.g_indate>'200405010000' and t1.g_indate<'200504010000'
试试这个 select count(*) from hn_guest where g_gname like '孙%' and g_indate>'200405010000' and g_indate<'200504010000'
建议看一些oracle优化的书
这样写 select count(*) from ( select * hn_guest where gg_indate<'200504010000' and _indate>'200405010000' ) where g_gname like '孙%'
如果是这样的条件: g_indate>'200405010000' and g_indate<'200504010000' and g_gname like '孙%' 需要建立: (g_gname,g_indate)双列的索引
把execution plan贴上来,别人才能帮你,只是简单的介绍速度什么的,帮不了你的 在sqlplus中 set autotrace on set timing on 然后执行你的语句以及h_wg(东方)写的语句 select count(*) from (select * from hn_guest where g_gname like '孙%') t1 where t1.g_indate>'200405010000' and t1.g_indate<'200504010000' 把显示的结果都贴上来
100万中,速度也很快啊,
select count(*) from (select * from hn_guest where g_gname like '孙%') as a
where a.g_indate>'200405010000' and a.g_indate<'200504010000'
反正select count(*) from hn_guest where g_gname like '孙%'才三十多条,
速度应该和select count(*) from hn_guest where g_gname like '孙%'速度差不多才是
select count(*) from (select * from hn_guest where g_gname like '孙%') t1 where t1.g_indate>'200405010000' and t1.g_indate<'200504010000'
select count(*) from hn_guest where g_gname like '孙%' and g_indate>'200405010000' and g_indate<'200504010000'
select count(*) from
(
select *
hn_guest where gg_indate<'200504010000' and _indate>'200405010000'
)
where g_gname like '孙%'
g_indate>'200405010000' and g_indate<'200504010000' and g_gname like '孙%'
需要建立:
(g_gname,g_indate)双列的索引
在sqlplus中
set autotrace on
set timing on
然后执行你的语句以及h_wg(东方)写的语句
select count(*) from (select * from hn_guest where g_gname like '孙%') t1 where t1.g_indate>'200405010000' and t1.g_indate<'200504010000'
把显示的结果都贴上来