On 07/10/2009, at 11:46 AM, Lawrence Pit wrote: > I remember with Oracle up to 9 (I don't know about 10 and up) if you > used a function on a column, it would not use any index that was > defined on that column. Same for MS SQL Server.
Ahh, I missed that you were casting "created_at" to a date. MSSQL (2000) has a CAST operator, and though it lacks a date type (later versions do), use of this operator doesn't prevent the use of an index. Neither does the use of DATEDIFF or DATEADD, etc in expressions where an index could help. Even in a stored procedure, where the constant offset is passed in as a parameter of the procedure (and hence the stored query plan will be used). Using DATEPART(year, created_at) does prevent choice of an index. Not too surprising. > Probably with PostgreSQL you can index date(created_at) instead of > just +created_at+. But as Jeremy pointed out, if you're working with > time zones comparing against date(created_at) yields wrong results. Yes. Clifford Heath. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby or Rails Oceania" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/rails-oceania?hl=en -~----------~----~----~----~------~----~------~--~---
