Re: A table with 4000 partitions

Vsevolod Afanassiev wrote:
>
> 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?
>
> Again, I am talking about DSS environment, with big "historic facts"
> table
> being gradually populated from one end (let's say by direct loads)
> and truncated at the other end, the information is never updated,
> users only access this table by running reports, so there is never a
> need
> to return just a few rows.
>
> I saw a note on the Metalink that says that the maximum number of
> partitions
> per table is 64,000.
>
> Thanks,
> Sev

The main issues the implementation details which can hurt you..

Example 1: Partitions are always numbered from 1 - thus when you drop the oldest one, all the others have to be renumbered - a big dictionary operation.

Example 2: If you have indexes, and you get the wrong kind of query, every index may need to be probed - could be lots of overhead

Example 3: Parse times can get massive if elimination is not obvious to the optimizer - basically you're giving Oracle a query and telling it to find the best way to find the best way through 4000 segments ( plus possibly another 4000 index segments)

Summary - it can be done, but (extreme) care is needed.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."