Re: Query problem with date partitioning

[Phil Hildebrand]

It's most likely because you are using a 'Hash' partition.

Hash partitioning will end up with 17 buckets where the to_days(localdatetime)mod17 determines the partition that the row drops into.

When using the '=', the optimizer knows exactly what partition the function evaluates to because it can run the number through the function, where as the > could return many rows, and with only 17 partitions, chances are you will have rows that end up in most or all of the partitions, thus it would make sense that the optimizer would not be able to 'prune' out any of the partitions. The optimizer can't evaluate which partition it's in without the actual value (and it won't know how many values with >=).

If you used a range partition, however:

partition by range (to_days(localdatetime))
( partition pyesterday values < 733548,
partition ptommorow values < 733549,
partition pforever values less than maxvalue
)

you could look for localdatetime >= now(), and only ptommorow and pforever should get scanned.