关于查询中的时间函数TO_date OR TO_char

[fanzs007]

已经做了useday列索引
1、---------------用TO_date函数查询速度很快-----------------------
select a.goodsid,b.vipid from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and a.goodsname like '%(特%'
and b.useday between
TO_DATE('2012-03-31 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2012-03-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

2、-------------用TO_char函数查询速度好慢-------------------------------------

select goods from pub_goods a,resa_sa_dtl b where a.goodsid=b.goodsid and b.vipid>0
and b.vipid not in(3344,1674,9028) and to_char(b.useday,'yyyy-mm-dd')=
to_char(sysdate,'yyyy-mm-dd')
and a.goodsname like '%(特%'

我想查询条件中b.useday等于今天的日期就行了（sysdate代入1中的TO_date函数）。

[ICE-word]

你第一个是明确查询当然快了
第2个是全部关联查找这样会慢！

[fanzs007]

如何才能在查询条件中等于今天呢？

[fanzs007]

我想要的功能是查询条件中b.useday等于今天的日期就行了
不能用to_char，太慢了。

[ssqtjffcu]

select a.goodsid, b.vipid
  from pub_goods a, resa_sa_dtl b
 where a.goodsid = b.goodsid
   and b.vipid > 0
   and b.vipid not in (3344, 1674, 9028)
   and a.goodsname like '%(特%'
   and b.useday >trunc(sysdate,'dd')

[ssqtjffcu]

select a.goodsid, b.vipid
  from pub_goods a, resa_sa_dtl b
 where a.goodsid = b.goodsid
   and b.vipid > 0
   and b.vipid not in (3344, 1674, 9028)
   and a.goodsname like '%(特%'
   and b.useday >trunc(sysdate,'dd')

[hudingchen]

SELECT ...
FROM ...
WHERE b.useday >= trunc(SYSDATE)
AND b.useday <= trunc(SYSDATE) + 0.99999

[啊彪123]

其实也可以创建函数索引！

[buryMyLove]

b.useday这个字段上有所引吧？你的第一条语句走索引，第二条语句，因为对b.useday字段使用了to_char函数，就不走索引了吧，你可以看下执行计划……

[zhangqin12356]

[Quote=引用 4 楼 的回复:]

SQL code

select a.goodsid, b.vipid
from pub_goods a, resa_sa_dtl b
where a.goodsid = b.goodsid
and b.vipid > 0
and b.vipid not in (3344, 1674, 9028)
and a.goodsname like '%(特%'
and b……
[/Quote]

+1

学习了

[hupeng213]

可以试试

and trunc(sysdate) - b.useday = 0