Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: 90GB table on Windows 2000

Re: 90GB table on Windows 2000

From: John Summers <john.summers_at_medtronic.com>
Date: 16 Oct 2002 07:49:14 -0700
Message-ID: <5d76b757.0210160649.13f5c9bc@posting.google.com>


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. Received on Wed Oct 16 2002 - 09:49:14 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US