Re: Newbie Question on Partitions

Well, this is a fairly common problem in building a data warehouse. You need to feed your warehouse with data from the prodcution system, but you don't want the extraction process to trash your production system peformance. Typically the ETL process is done during hours when the system is not busy - all the more better if you only have to run it on a weekly or monthly basis. How frequent do you need to archive your data? If not very frequent, you might as well just try to avoid peak hours when archiving.

I can see 2 steps that are impacting oyur production system performance:

1. ETL queries that move data from production to either DW or staging area. You said very little about this step. I will refrain from guessing and suggesting possible solutions.
2. Removal of the above mentioned data. You remove data from a table either by using "delete", "truncate" or "drop partition". You can not truncate the whole table, so you either just delete the rows or like you said, manually move the rows to a partition and then dropping the partition. I have not sit down and tried to prove it, but it is almost certain that the partitioing apparoach will end up costing your more.

I would say if you can not partition you table with a proper key then you will have to rely on the good old "delete" to do the work.

• Jusung Yang

"M2" <me_at_quietplaceiwithnospam.com> wrote in message news:<T8d4a.77$Aq3.14282_at_news.optus.net.au>...
> I'm sorry (and possible embarrased) to say that actually was what I was
> suggesting. I work for an education institution and need to gracefully move
> out old session data to keep sizes down. The sessions start and end on
> different dates each year and the systems provided for these often don't
> start on the start of session anyway. This didn't seem to fit any mold for
> identifying it's destination partition at the time of insert.
>
> So how do you efficiently deal with something like this where information
> needs to move from one partition to another post creation?
>
> Is partitioning maybe not the way to go? All I am really after is a way to
> flag a (large) group of rows to be archived using an external process (it's
> complicated logic). After doing this I want to be able to move the data off
> to the warehouse and recover the space in the production system with as
> little effect on the production system as possible.
>
>
> "Jusung Yang" <JusungYang_at_yahoo.com> wrote in message
> news:130ba93a.0302171037.1a76e4e1_at_posting.google.com...
> > There is no such requirement that partitions should be placed in
> > different tablespaces. Though it may help sometime by distributing IO.
> >
> > I certainly hope that you are not trying to partition the production
> > table by the archive flag, which seems to be what you were suggesting.
> > Updating the partition key will cause physical row movement which I
> > would try to avoid. It is best that you build the partition key so
> > that when a new record is created it already knows which partition it
> > belongs and move into that partition automatically.
> >
> >
> > - Jusung Yang
> >
> >
> > "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?
> > >
> > > Matt.
Received on Tue Feb 18 2003 - 01:24:08 CST