Re: How does oracle evaluate where clauses?
From: Paul Dorsey <pdorsey_at_dulcian.com>
Date: Sun, 06 Jun 1999 19:34:15 GMT
Message-ID: <X2A63.7178$eF3.3650_at_news.rdc1.nj.home.com>
Date: Sun, 06 Jun 1999 19:34:15 GMT
Message-ID: <X2A63.7178$eF3.3650_at_news.rdc1.nj.home.com>
Be aware that the following
> where trunc(fielda) = trunc(sysdate)
will cause a full table scan even if you have an index on fielda
You should be VERY careful about writing code like that.
If you want to use the index, you need to do something like
where fielda between trunc(sysdate) and trunc(sysdate+1)-.00001
or make sure that fielda is truncated on entry.
-- Paul Dorsey Dulcian, Inc. www.dulcian.com 212 595 7223 Wayne McKinney wrote in message <7j942f$2dgq$1_at_news.hal-pc.org>...Received on Sun Jun 06 1999 - 21:34:15 CEST
>My experience shows that SYSDATE is evaluated only once. That's why I
>frequently have to use syntax like
>
> where trunc(fielda) = trunc(sysdate)
>
>Using the TRUNC function strips the seconds, minutes, hours, etc., off of
>the date and allows you to only compare the dates. If you don't care about
>the time, then this is the way to go.
>
>Jonathan M Whitehead wrote in message
<37579765.28314A8C_at_sheffield.ac.uk>...
>>Hi,
>>
>>Can anybody tell me how oracle would evaluate the following simple where
>>clause in this select statement:
>>
>>select fielda
>>from tablea
>>where fielda = sysdate;
>>
>>Does oracle evaluate sysdate once for the where clause and then use that
>>value to compare with each row or does it evaluate the value of sysdate
>>for every row returned by the select?
>>
>>Cheers,
>>
>>Jon.
>>
>
>