Re: How to estimate number of subpartition

maniekp <maniekpozioma_at_poczta.onet.pl> wrote in message news:<bbdmq4$64j$1_at_news.onet.pl>...
> hello,
>
> Can anybody know what is the main factor of estimating
> number of subpartitions. I have fact table with one-day partitions
> , each day about 1M rows. Machine have 20 CPU, but when I set up
> 20 subpartitions that in each segment will be only 5k rows ...
>
> thx, maniek

Things to consider: how long do you keep your data in the fact table? 1 year? 2 years? Each subpartition will store in its own segment. Assuming you keep 2 years worth of data, and you have 20 subpartitioins in each and every partition, you will have 365*2*20=14600 segments for the data alone. For each local index you have on the table, 14600 segments will be added. Having so many segments may not be a good thing.

Will it be list or hash subpartitioning? If hash, make the number of subpartitions power of 2.

• Jusung Yang