Re: 90GB table on Windows 2000

John,

It's slightly worse than you think.

If you have a PK/FK relationship between partitioned tables (and Oracle thinks you will because that's probably what partition-wise joins are really good for) you can only
exchange partitions into and out of the
parent and child tables if you set the
parent key and child foreign key constraint to NOVALIDATE (as you seem to have
discovered). (And make sure you do it
on the incoming as well as outgoing data)

However, if you want to drop or truncate a parent partition, having dropped the
"matching" child partition, you have do
DISABLE the child's foreign key constraint to do so.

Added to which, even if you don't have the issue of parentt/child referential integrity, then you need to NOVALIDATE any
unique/primary key constraints when
you exchange, otherwise Oracle checks
that the incoming data doesn't breach
uniqueness when compared with the
rest of the data in the table (even when the index enforcing the constraint is
local).

On the plus side - if you can guarantee
that the data has always been unique,
then you do NOT need to VALIDATE
the constraint - ENABLE ensures that
future data will not be allowed to breach the constraint, VALIDATE goes back and
checks that existing data does not breach the constraint, so if you know it is correct . . .

The real problem is having to disable the child's FK constraint to drop/truncate - it gives a window of opportunity when
bad data can get into the system (maybe
you should rename revoke privileges on
the table whilst you do it).

Despite comments by Daniel, some data
warehouse systems do run without constraints (or constraints which are DISABLE RELY)
in order to get the optimizer to know how the data hangs together, without the overhead of maintaining indexes. BUT they are data warehouse which are only subject to batch loads, and the batch code is supposed to prove that the constraints are not needed.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA__________November 7/9   (Detroit)
____USA__________November 19/21 (Dallas)
____England______November 12/14

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html





John Summers wrote in message
<5d76b757.0210160649.13f5c9bc_at_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.