Re: Partitions implementation with views

Hannu Krosing <hannu@tm.ee> writes:

> Greg Stark kirjutas E, 01.12.2003 kell 18:15:
> > Separate OS partitions is a reasonable use of partitioned tables, but the
> > biggest advantage is being able to drop and load partitions very quickly, and
> > without impacting performance at all. loading or dropping millions of records
> > becomes a simple DDL operation instead of a huge DML operation.
> 
> How does that mix with foreign key checks ?

That's a good question. I don't know how it mixes in Oracle as the partitioned
tables were always the ones at the end of the chain of references. That is,
there were foreign key constraints on them but no other tables referenced
them. Perhaps that may be common as partitioning is useful on BIG tables and
that makes them likely to be on the "many" side of all the "one-to-many"
relationships.

Or perhaps one often has lots of tables partitioned on the same key (like
fiscal year) and design things so you never have references across years. Then
you would be able to disable constraints and drop the old year without risking
any broken references.

As I say I'm not sure, but I from what I'm reading now It seems they don't mix
at all well in Oracle. It looks like if you have any foreign key references
from any non-empty tables to your partitioned table then you're basically
barred from removing any partitions. I guess you're expected to disable the
constraints while you do the operation.

That does make a lot of sense if you think of a partitioned table as just a
bunch of syntactic sugar over a view with a big union of all the partition
tables. You can't expect the database to recheck everything whenever you want
to redefine the view.

Alternatively you can think of partitioned tables as a bunch of syntactic
sugar over inherited tables. In which case it's no coincidence that foreign
keys and inherited tables don't mix very well either.

I do think it would be possible to design something better than just ruling
them incompatible. Presumably you would want an index on the foreign key
target columns to make the constraints fast. Therefore presumably the
partition key is the leading columns of the foreign key target columns.
Therefore all you really need to verify the partition drop is safe is an index
on the partition key columns in the referencing table and you can do a simple
index lookup to see if any records with the same leading columns exist to
verify the foreign key reference.

So for example:

Table: invoice
--------------
invoice_FY integer
invoice_id integer
...
"invoice_pkey" primary key, btree (invoice_FY,invoice_id)


Table payment
-------------
payment_FY integer
payment_id integer
...
invoice_FY integer
invoice_id integer
...   "invoice_idx" btree (invoice_FY,invoice_id)   "$1" FOREIGN KEY (invoice_FY,invoice_id) REFERENCES
invoice(invoice_FY,invoice_id)ON DELETE CASCADE
 

In this situation when you drop a partition from invoice for FY 2002 only one
fast lookup to check for "EXISTS (select 1 WHERE invoice_FY = 2002)" would be
necessary.


-- 
greg