Re: A table with 4000 partitions

vafanassiev_at_aapt.com.au (Vsevolod Afanassiev) wrote in message news:<4f7d504c.0201281505.2b81d143_at_posting.google.com>...
> What are possible advantages/disadvantages of using very large number
> of partitions, let's say, 4000 or more, in DSS environment?
> I see following
> Advantages:
> 1. Reduces the need for indexes, and potentially no indexes at all
> 2. More flexibility in removing information by using "alter table
> truncate partition"
> 3. It is possible to run several direct loads in parallel in
> differet partitions
> 4. More flexibility with tablespaces/datafiles
>
> Disadvantages:
> 1. Bigger shared pool
> 2. Higher ENQUEUE_RESOURCES
> 3. Some operations are slower, for example, snapshot refresh
>
> Anything else?
>

Another disadvantage I can think of is, each partition is at least one segment. This is essentially the same as adding another 4000 objects to your system. This can quickly frag you system tablespace. If you perform any space calculation against dba_segments, dba_extents, you will notice it takes much longer.

This may also increase your overall recursive CPU time.

But these are minor to me. They should not outweigh your business decision. Received on Thu Jan 31 2002 - 12:11:48 CST