Re: sysdate query

takesiro wrote:
> Hi!
>
> I think roy's idea is correct.
> When Function compares in WHERE, It doesn't work with INDEX.
>
> For Example.
> A) - SELECT col FROM tbl WHERE SIGN(col) = -1
> B) - SELECT col FROM tbl WHERE col < 0
> B can use INDEX, A cannot.
>
> This TRUC(sysdate) is function. So it is unable to use INDEX.
> But when -0, it will probably be DATE(because VCHAR - VCHAR is impossible)
> Therefore it will work CBT.
>
> Regards.

Your example is okay, but your last paragraph is not quite right. The issue with FUNCTIONs and INDEX is if the FUNCTION is applied to the column of the index. So the fact that SYSDATE is in the where clause does not affect this. So

If there is an INDEX on dateCol
TRUNC(SYSDATE) will not affect the optimizer but TRUNC(dateCol) will prevent it from using the INDEX.

And since internally a DATE type is a number (number of days from the epoch), where hours,minutes,seconds are in teh faractional portion, the type of TRUNC(SYSDATE) is still DATE (internally now just a whole number). The Character datatypes never enter the picture.

 HTH,
   Ed Received on Wed May 10 2006 - 13:50:44 CDT