| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| 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
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.
OR
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.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
April 2004 Iceland http://www.index.is/oracleday.php 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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Apr 22 2004 - 03:33:35 CDT
 |
 |