Re: Reasonable number of partitions?

[Shlomi Noach]

Thanks for the detailed reply!

I do indeed expect to have a good performance gain using partition pruning.
My database resembles somewhat a web-based mail service, where a certain table holds data for all users notifications.
Suppose, for example, that you have a Yahoo! mail account, and the table in which your emails reside also holds data for all other users.
What I wish to partition is the HASH for the user ID on this table. Since user queries are only relevant to his/her own data, and never to other users data, I expect a performance boost using partition pruning.

My real scheme, though, also has sub-users, and that's where I wanted to add sub-partitions.

At any case, I took notice of the total 1024 partitions limit, and the fact it's not possible (yet!) to sub partition a HASH partition.

Is there a rough estimation, for some benchmark, for the performance gain achieved by partitioning a table to 10 partitions, to 50, etc.?

Thanks
Shlomi