Re: Antique hunter - looking for info on insert into partion (union-all) view
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 22 Apr 2004 09:37:13 +0100
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
----- Original Message -----
From: "Don Granaman" <granaman@xxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, April 22, 2004 10:03 PM
Subject: Antique hunter - looking for info on insert into partion
(union-all) view
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: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
