Re: 90GB table on Windows 2000

John Summers wrote:

> Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3DA5C735.46F62F0F_at_exesolutions.com>...
> > NorwoodThree wrote:
> >
> > > PARTITION PARTITION PARTITION
> >
> > And get a real operating system.
> >
> > Daniel Morgan
>
> This seems to be the point where the topic digressed into a
> Windows/UNIX topic.
> That's not the direction I'm after. I have a few comments/questions on
> the partitioning and it looks like there's fair expertise reading
> this.
> In my scenario, I'm upgrading to Oracle 8i from 7.3.4. The purpose
> is to partition several main tables (and indexes). Outside of the fact
> that I've shown the query performance will increase (about 10X). It
> also allows me to redesign the archive/restore scheme being used.
> Currently the archive consists of spooling data and creating sqlldr
> control files... these files can then be restored to another "archive"
> instance of the database. This process is painfully slow. I've jumped
> at the chance to archive by exchanging partitions and then exporting
> them (data files and metafile). My research shows that this is a slam
> dunk performance-wise... however....
> I've got several primary keys, foreign keys, and other constraints
> that are pretty damn useful and I'd like to keep them. They point all
> over the place (to tables that are not partitioned nor exported). To
> handle the exchange/export, I've got to disable the constraints. Once
> I've done this, I can exchange/export.
> Unfortunately, I cannot re-enable constraints and validate them. We're
> talking about a 100G+ database with the main table containing 2
> billion rows. I can enable with the novalidate option... and I've
> found some really peculiar optimizer decisions on some queries that
> result in them never returning. I finally came upon using the enable
> novalidate and then the "rely" option.
> This "seems" to work... I asked Oracle to confirm this as an
> acceptable solution. Their comment," We do not recommend this
> approach... constraints should be enabled and validated." And they
> promptly ran and hid.
>
> The question I have... has anybody USED partitions successfully on a
> large database WITH constraints... AND used the exchange/export on the
> partitioned tablespaces to archive data. Yes, we're talking about date
> range partitioned and you're looking at a X year rolling window (where
> at any given time, I may need to bring back 1 or more partitions).
> I've perused these newsgroups on and off for a while and I've never
> seen someone claiming real life experience giving their story.
> Any takers?
> john.

I can't imagine any table without constraints. So I have never done a partitioned table any other way.

But my reason for suggesting you get a real O/S is that 90GB of data in a multiuser environment will tax Windows. And getting a 64 bit O/S won't hurt.

Daniel Morgan Received on Wed Oct 16 2002 - 10:54:46 CDT