db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Suresh Thalamati <suresh.thalam...@gmail.com>
Subject Re: [jira] Commented: (DERBY-355) Document TIMESTAMPADD and TIMESTAMPDIFF functions
Date Thu, 16 Jun 2005 23:04:17 GMT
How about modifying it to ?

Note that you should avoid using a datetime column
inside a timestamp arithmetic function in WHERE
clauses if there is a index on the column because the 
optimizer will not use any index on the column.

Thanks
-suresht.


Jeff Levitt wrote:

>--- "Suresh Thalamati (JIRA)"
><derby-dev@db.apache.org> wrote:
>
>  
>
>>description of  TIMESTAMPADD/TIMESTAMPDIFF seems to
>>indicate that  column name 
>>should not be put in these escape function because 
>>it will use index, I am not sure that is right ?
>>my understanding   is if you put the column name
>>inside the function ,  Index will NOT be used. 
>> 
>>TIMESTAMPADD page:  (in the end ..)
>>Note that you should not put a datetime column
>>inside a timestamp arithmetic function in WHERE
>>clauses because the optimizer will use any index on
>>the column.
>>
>>TIMESTAMPDIFF section: (in the end .,)
>>Note that you should not put a datetime column
>>inside a timestamp arithmetic function in WHERE
>>clauses because the optimizer will use any index on
>>the column.
>>
>>
>>jack wrote in his patch submission:
>>More examples:
>> SELECT * FROM t WHERE {fn TIMESTAMPDIFF(
>>SQL_TSI_DAY, CURRENT_DAY, promisedDate)} <= 1
>>Selects all rows from t with promisedDate at most
>>one day from now. (It also selects rows with a
>>promisedDate in the past). Note that this is
>>probably not the optimal way to express the query.
>>If promisedDate is indexed the Derby optimizer will
>>not use the index. Better is
>> SELECT * FROM t WHERE promisedDate <= {fn
>>TIMESTAMPADD( SQL_TSI_DAY, 1, CURRENT_DAY)
>>
>>    
>>
>
>Thanks for the feedback Suresh,
>
>I've already tweaked this patch about 5 times, so this
>time I want to make sure I get it right before I
>submit a new patch.  How would you suggest I rewrite
>it so that it is accurate?
>
>  
>



Mime
View raw message