Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Antique hunter - looking for info on insert into partion (union-all) view

Re: Antique hunter - looking for info on insert into partion (union-all) view

From: Jonathan Lewis <>
Date: Thu, 22 Apr 2004 09:37:13 +0100
Message-ID: <001401c42845$0458a7f0$7102a8c0@Primary>

Way back in the dim and distant past of 7.3 partition views, you had to write "front-end code" that knew which table the insert should go to. That's probably why there is no information on inserts for PVs.

A funny thought about PVs - Oracle has been handling them better in 8 and 9 than it ever did in 7, even with the 'partition_views_enabled" = false, because nowadays they aren't a special feature, just a piece of code that can be optimised through pushing predicates and complex view merging.
(And until list-partitioning appeared, they could
still be VERY effective for short lists of discrete values). And they still ARE viable for partitioning when you want different partitions in different databases !

If you want to use 'instead of' triggers for inserts, it's pretty easy (in fact the manuals (7.3) have an example, I think): you should create constraints on the tables to allow only the correct data, then the trigger should simply intercept inserts on the view and insert to the correct table.

If want to get really smart, you can write code that reads the view definition (or dependency tree) to get the table names, then reads the constraints, then writes the code to regenerate the trigger.

It's the Update and Delete triggers that are nearly impossible. A delete trigger OUGHT to check the WHERE clause of the incoming delete so that it only tries to delete rows from the tables that might contain data. (to avoid wasted effort)

An update trigger may need to:

    Copy a row from one table to another, changing     the relevant columns as it goes, then delete the     original row.

    It may have to update in place ONLY the columns     that were referenced in the view update statement.     It's NOT going to be efficient code.

On top of all that - if you have a 3rd party developer tool that knows how to use Oracle, it will probably be trying to do updates by ROWID - and I don't think you can get the rowid to do anything useful in a partition view.

I've only ever done this on a data warehouse where all data changes were driven through generated code that knew which table to use.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

April 2004 Iceland June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

The subject line really says it all. Someone I know is seriously considering it and asked me about this v7.3-compatible form of "logical" partitioning (in 9i no less!) with an "instead of " insert trigger on the view. It has been *way* too long. It hasn't been in my memory cache for years and only small fragments even seem to be in "secondary storage". The google mine doesn't yield much of use except for Jonathan's papers like pv.doc, but I haven't yet found the one with any discussion of insert. So, I am now looking for examples or papers. Pointers anyone? Someone must know where some dusty info on this is squirreled away. Perhaps circa 1995? At the Smithsonian? ;-)

Don Granaman
Resurrected OraSaurus

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Thu Apr 22 2004 - 03:33:35 CDT

Original text of this message