Re: oracle datamart - partioned views

Steve,
Be reasonable - dropping a 4,000 partition table is not something you would do in a real datawarehouse,
and when you do you will be dropping at least 4,000 x N (where N-1 is the number of indexes) segments.
Think what this does for seg$, ts$, fet$ and uet$ activity.

But I do sympathise. In a fit of testing I've just dropped a few partitioned tables and left myself
with 25,000 entries in USER_FREE_SPACE - I'm having a problem now creating a simple table -
18 CPU minutes and waiting ... presumably Oracle is having some trouble deciding which extents
to coalesce.

Steve Haynes <steve_at_rwx777.demon.co.uk> wrote in article <zGAVdGADLmO0Ewet_at_rwx777.demon.co.uk>...
> In article <875543262.18411_at_dejanews.com>, dgray_at_bdsinc.com writes
> > for warehousing! - I'm going to oracle 8 soon any body know if
 oracle 8
> >is
> > any better.
> >
> Oracle 8 has greatly improved partioning over the old 7.3
> partitioned views. We are designing a monster system around
> it at present. No performance stats yet I'm afraid.
> One thing to bear in mind is to keep the number of partitions
> down to minimise parsing overheads. Dropping a small (initial)
> table with 4000 partitions (and no data in any partition) took
> over an hour. (with nothing else running on the box).
> Aim at around a few hundred partitions max.
> Steve
> ---------------------------------------------------------
> "The floggings will continue until morale improves."
> ---------------------------------------------------------
>
Received on Thu Oct 09 1997 - 00:00:00 CDT