Re: Newbie Question on Partitions

"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:02:09 CST