Re: Help: How should i partition this table

joe bayer wrote:
> What do you mean ?
>
> There is 52 weekes each year, so if we partition by week, there will be 520
> weeks for 10 yrs. And the query
> "select count(*) from A where end_time > to_date('2005/01/01','yyyy/mm/dd')
> "
> will scan all 520 partitions, will it?
>
> ---Partition by week and no query will look at more than 2 weeks worth of
> data.
>
> What do you mean by this?
>
>

>>Partition by week and the problem disappears completely doesn't it?
>>Right now your query hits 4.3 weeks worth of data possibly crossing
>>to 8.6 weeks. Partition by week and no query will look at more than
>>2 weeks worth of data.
>>-- 
>>Daniel A. Morgan
>>Relational theory is not something that is simply a nice-to-have.
>>http://www.psoug.org
>>damorgan_at_x.washington.edu
>>(replace x with u to respond) 

1. Do not top post
2. It is not the number of partitions ... it is the number of rows. The number of rows will be smaller if you partition with finer granularty.

But the point is one that can only be answered by someone that applies mental processes to the work at hand. What is the bell curve for the queries being run? What type of query constitutes the far-right or far-left side of the bell curve? What type of query constitutes the largest volume? Partition to improve the performance of MOST of the queries. You can NEVER partition to improve the performance of ALL of the queries. Someone can always design a query that full-table scans every partition. And what is this? A single CPU machine?

-- 
Daniel A. Morgan
Relational theory is not something that is simply a nice-to-have.
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)