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

Few years ago I went through the same dilemma on a large DW project (> 1TB) and decided that I needed a formula that could be applied to any table on that specific project. Here's what worked for us then:

	STRATEGY:
 	+------------------------------------------+
 	Goal:
 	        20 EXTENTS per SUBPARTITION
 	Rule:   
 	        2sp     for 0-40        EXTENT TABLE
 	        8sp     for 41-160      EXTENT TABLE
 	        16sp    for 161+        EXTENT TABLE
 	+------------------------------------------+

see http://www.dbatoolz.com/SCRIPT_26.HTM for the script (as usual)

Note that I also used SMALL/MEDIUM/LARGE tablespace sizing strategy in conjunction with the above "formula" otherwise it doesn't work. More on SMALL/MEDIUM/LARGE tablespaces in
http://www.dbatoolz.com/SCRIPT_22.HTM (s_db_segs*.sql series of scripts).

You might have to adjust size of SMALL/MEDIUM/LARGE as well as SUBPARTITION formula but I think the key here is for you to have a strategy. Hopefully this is enough to get you started ...

Vitaliy
DBAToolZ.com (free sql scripts and SQLDIR script parser) Aspeda.com (free Oracle monitoring tool) Received on Mon Jun 02 2003 - 21:52:32 CDT