Re: Newbie Question on Partitions

Oops, hit the send button too quickly.

I forgot to mention that if this archive data needs to be sent to another (data warehouse) database, then transportable tablespaces could be a useful mechanism for which again a separate tablespace might prove useful.

Cheers

Richard

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:fM44a.49794$jM5.124677_at_newsfeeds.bigpond.com...
> "M2" <me_at_quietplaceiwithnospam.com> wrote in message
> news:ke_3a.65$Aq3.12506_at_news.optus.net.au...
> > Hi,
> > just a quick newbie question on partitions. I have a very large table
(40
> > million rows) that I want to move into partitions. The plan here is to
> > create two partitions one for "archive" rows and the other for
production
> > rows. I will then periodically update rows to set the archive flag
> (causing
> > a change in partition) and move the data off to a warehouse. The
partition
> > will then be dropped to recover the space.
> >
> > So, my question is, will this be efficient if both the archive and
> > production partitions are in the same tablespace? Everything I read
seems
> to
> > mention partitions synonymously with different tablespaces but is it
> > possible to do it in the same space? What would I lose out on?
> >
>
> Hi Matt,
>
> Providing the tablespace is sliced and diced enough to prevent excessive
> contention and I/O bottlenecks, having both current and archive data in
the
> same tablespace shouldn't be an issue per se. I'm assuming the data
becomes
> "archived" in a randomish sequence that prevents partitioning in an other
> manner impossible. I'm also assuming backup/recovery and availability
issues
> are similar for both forms of data and that having the flexibility to
backup
> just the production data is not a desirable requirement (otherwise
separate
> tablespaces could prove useful).
>
> Note also there will be an overhead when this archive flag gets set in
that
> row movement will result.
>
> Cheers
>
> Richard
>
>
Received on Mon Feb 17 2003 - 08:06:01 CST