Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: 90GB table on Windows 2000
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