Re: A table with 4000 partitions

Possible additional disadvantage:

4. Management nightmare.
5. Incredibly long DDL to store for version control

Is the point getting into a book of world records or is there some reason to believe it will actually accomplish something related to a real problem? That's the question I'd ask.

Daniel Morgan

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
Received on Mon Jan 28 2002 - 10:51:33 CST