Re: Table partitioning

On 2013-10-28 12:47, Herouth Maoz wrote:
> Thanks. Assuming there is an index on the time_arrived column, and that there are about 10.5 million records in each child table, how bad will performance be if the query actually accesses all the 12 tables? Will it be as bad as using the full table?
>
>
In the case where you don't add a predicate to your select statement
that matches your constraint checks, meaning your statements are
visiting every child table, you're not going to experience any
performance gains over leaving everything in one table (plus you're
adding on extra maintenance overhead and likely degrading query
performance, even if only by a small amount).

What are your concerns with creating/dropping tables? You may have even
better success with creating a child table for each week. Fifty-two
extra tables is extra maintenance but the performance boost you could
get by dividing out your data is probably significant, and that kind of
maintenance should be automated anyway.