Think about the long term
sPh, October 13, 2004 - 11:00 am UTC
> just -- please -- before you actually
> do this, make sure you are solving a real
> problem.
And having done it, think about how you will handle the situation where the summarized qtys get out of sync with the detail. Oh no - can't happen - won't happen - will NEVER happen.
Every application I have ever seen that maintains summarized qtys, on every database (sorry Tom), eventually gets out of sync. You must provide a method for analyzing and re-syncing.
Right now I am working with an ERP package that maintains summaries all over the place, that gets out of sync regularly, and that provides no supported re-sync utility. Lot of fun explaining to the users.
sPh
October 13, 2004 - 12:06 pm UTC
with the triggers in place, what you state is not possible actually.
Another approach would be an ON COMMIT materialized view of the details -- it would not get out of sync either.
can you prove otherwise, give us a "use case" whereby with the above logic -- one would get "out of sync".
(NOT that I'm promoting this technique, just that -- it won't get out of sync, but I love being proven wrong when wrong so....)
David Aldridge, October 13, 2004 - 11:44 am UTC
It seems that if it is a regular requirement that the child sums be viewed along with the parent record, and that performance and accuracy -- not limiting concurrency nor finding the two out of sync -- are very important, then the time might have come to consider using a hash cluster for the child table to reduce the LIO's associated with reading all the child records.
Thoughts?
October 13, 2004 - 12:20 pm UTC
alternative data structures such as
b*tree clusters
hash clusters
iots
sorted hash clusters (10g)
single table hash clusters
should always be considered :) yes.
an IOT would be possibly beneficial as well -- to keep the child records for a given foreign key "together"
At the minimum ... migration of children should somehow be addressed ...
Gabe, October 13, 2004 - 12:09 pm UTC
create or replace trigger c_trigger
after insert or delete or update on C
for each row
begin
if updating and :new.x != :old.x then
raise_application_error(-20000,'cannot migrate child record to another parent');
end if;
if ( inserting or updating ) then
update p set qty = nvl(qty,0)+:new.qty where x = :new.x;
end if;
if ( updating or deleting ) then
update p set qty = nvl(qty,0)-:old.qty where x = :old.x;
end if;
end;
/
Also, QTY in the parent should somehow be protected from direct changes ... time for the API.
October 13, 2004 - 2:52 pm UTC
I goofed, add:
2 after insert or delete or update of qty,x on C for each row
the ,x -- to fire when we reparent a record... thanks.
Bypass trigger
A reader, October 13, 2004 - 2:01 pm UTC
"with the triggers in place, what you state is not possible actually"
Well, if the detail table is modified using a method that bypasses triggers (direct path, /*+ append */, etc) ...
October 13, 2004 - 3:09 pm UTC
did you try it :)
/*+ append */ is a noop with triggers in place (or RI actually)....
but yes, a direct path load would bypass the triggers -- which would be a huge problem for any system using triggers in any fashion though. Everything done by the trigger (supply complex default value -- which is sort of what this is -- would be bypassed as well)
Wouldn't dare not to ...
Gabe, October 13, 2004 - 3:07 pm UTC
<quote>did you try it?</quote>
See "insert or delete or update of qty on C" ...
^^^
flip@flop> create table p ( x int primary key, qty number );
Table created.
flip@flop> create table c ( a int primary key, x references p, qty
2 number not null );
Table created.
flip@flop> create or replace trigger c_trigger
2 after insert or delete or update of qty on C for each row
3 begin
4 if ( inserting or updating )
5 then
6 update p set qty = nvl(qty,0)+:new.qty where x = :new.x;
7 end if;
8 if ( updating or deleting )
9 then
10 update p set qty = nvl(qty,0)-:old.qty where x = :old.x;
11 end if;
12 end;
13 /
Trigger created.
flip@flop> insert into p values (1,null);
1 row created.
flip@flop> insert into p values (2,null);
1 row created.
flip@flop> insert into p values (3,null);
1 row created.
flip@flop> insert into c values (100,1,55);
1 row created.
flip@flop> insert into c values (101,2,30);
1 row created.
flip@flop> insert into c values (102,2,20);
1 row created.
flip@flop> select * from p;
X QTY
---------- ----------
1 55
2 50
3
flip@flop> select * from c;
A X QTY
---------- ---------- ----------
100 1 55
101 2 30
102 2 20
flip@flop> update c set x=3 where x=2;
2 rows updated.
flip@flop> select * from c;
A X QTY
---------- ---------- ----------
100 1 55
101 3 30
102 3 20
flip@flop> select * from p;
X QTY
---------- ----------
1 55
2 50
3
All I'm saying is ... well, it is demo but it should cover at least the basic angles ... no big deal really.
October 13, 2004 - 3:33 pm UTC
sorry -- I retracted that before you posted this :)
as I hit the submit, i said "wait, let me check this out further" and found the goof!
sorry about that.
a dangerous business
graeme king, October 13, 2004 - 3:27 pm UTC
i agree with tom regarding the bypassing of triggers. It's really dangerous. your basically bypassing the business rules and then duplicating them somewhere else so you can 'fix' things after the fact.
i guess as long as they were stored in a package that was executed by the trigger and the batch program after it loads the data then that would be the best compromise.
David Aldridge, October 13, 2004 - 6:28 pm UTC
I know that this suggestion contravenes the premise of George's question, but it seems that the trigger methodology s*u*c*k*s in a number of ways -- this sort of application functionality ought to be more integrated into the application code itself, to my mind. Triggers are fine for auditing and such like, but I don't like to see them used for this sort of thing.
October 13, 2004 - 6:50 pm UTC
oh no, this is exactly what I believe a trigger is useful for!
You see -- this is in no way shape or form "something that should be anywhere NEAR application code"
this is something that needs to be AS FAR from a piece of application code as possible.
Else, that application becomes the only gateway to the data....
And applications have short life spans.
But data, data lives forever.
There is zero chance I would let the application maintain an aggregated field like this.
I might use
create materialized view mv
refresh fast on commit
as
select fkey, count(*)
from details
group by fkey;
instead of a trigger (it'll have internalized triggers) and to reduce the amount of serialization that needs to take place, but put it into application code? never, not a chance.
David Aldridge, October 13, 2004 - 7:01 pm UTC
Let me just clarify that I'm not talking about a J2EE or whatever application sitting on another box somewhere -- I'm talking about the server-side PL/SQL code that provides the API to the data that any external GUI would interface with.
For the purpose of maintainability, and maybe for providing a locking mechanism for the parent record, I'm suggesting that the code ought to be part of the procedures that modify the child record, rather than tucked out of sight in a trigger where it is less visible.
October 13, 2004 - 7:19 pm UTC
that would imply "gotta use a TAPI" to access this table -- i'm not a fan of them.
(search for TAPI to read about it if "table API" doesn't mean anything...)
The update provides the locking mechanism for the parent...
but we'll probably have to agree to disagree -- which is OK, especially on a point like this. This is one time I happen to think the trigger would be OK (there are few of those). In general I don't like triggers that do tons of magical things -- but this is pretty straight forward.
A reader, October 13, 2004 - 11:35 pm UTC
"just -- please -- before you actually do this, make sure you are solving a real problem. summing up qty's for a parent record is "pretty darn fast". before you do something like this, just make sure you are solving a real problem!"
How would you implement this summing up qtys for a parent record without storing it?
select x,(select sum(qty) from c where c.x=p.x) the_sum
from p;
What if a predicate needs to be applied on the_sum above?
[Yes, I realize that we had a similar discussion about this on </code>
http://tinyurl.com/4357f <code>but I ask again because of your pretty darn fast comment above]
October 14, 2004 - 9:26 am UTC
I'd have to ask you how often do you do that (eg: I would NOT use a scalar subquery for this -- that is for sure).
If you do it 15 times a second, you would be solving a real world problem.
If you do it 15 times a year, you would not be (probably).
Thank you very much.
George Rajan, October 14, 2004 - 12:59 am UTC
Thank you very much for your quick and concise answer.
(I have received the same mail seven times from you in response to this question)
October 14, 2004 - 9:38 am UTC
you asked to be notified when it is updated (so you are getting yet another one :)
Thank you very much.
George Rajan, October 14, 2004 - 1:08 am UTC
Thank you very much for your quick and concise answer. It is very useful to me.
(Plese note that I received seven copies of your email answer.)
Thank you very much.
George Rajan, October 14, 2004 - 1:09 am UTC
Thank you very much for your quick and concise answer. It is very useful to me.
(Please note that I received seven copies of your email answer.)
A reader, October 14, 2004 - 11:07 am UTC
"eg: I would NOT use a scalar subquery for this -- that is for sure)"
Right, I thought as much, so what would you use for this?
October 14, 2004 - 11:31 am UTC
a join.
A reader, October 14, 2004 - 2:09 pm UTC
"a join"
So something like
create or replace view v as
select p.x,sum(c.qty) the_sum from p,c
where p.x=c.x
group by x;
This wouldnt perform very well if I apply a predicate on v.the_sum either, right? So why is this any better/worse than the scalar subquery?
Also, is it a good idea to put a 'group by' in a view. That would affect query plans adversely if the view is further joined to other tables/view, wouldnt it? It would be forced to materialize V because of the group by?
Thanks
October 14, 2004 - 7:38 pm UTC
why wouldn't it "perform well", i would expect nice big juicy full table scans, nice big hash joins -- could take just very few seconds for millions of rows.
like i said -- 15 times a YEAR or 15 times a second -- different needs for different things.
I use views to answer a question. this would not be the right view for all questions.
MV and scalar subquery
A reader, November 30, 2004 - 8:11 pm UTC
Suppose I have a view using a scalar subquery like
create or replace view v as
select
cust_id,
(select sum(sales) from cust_sales where cust_id=c.cust_id) sales,
...
from cust c;
How can I create a MV for the 'sales' field above that pre-computes the answer so that the above query can be rewritten (transparently to my apps) to use the MV?
Thanks
November 30, 2004 - 9:00 pm UTC
you cannot, the mv cannot have the scalar subquery and there will be no translation from the view V to the MV which would be "different looking"
however if they all query a view, no reason you cannot replace the view with a join -- and the mv will have a job and there you go, it'll rewrite.
A reader, November 30, 2004 - 9:15 pm UTC
"however if they all query a view, no reason you cannot replace the view with a join -- and the mv will have a job and there you go, it'll rewrite"
Can you please show how to do that in this context?
Thanks
December 01, 2004 - 8:03 am UTC
instead of:
create or replace view v as
select
cust_id,
(select sum(sales) from cust_sales where cust_id=c.cust_id) sales,
...
from cust c;
you would
create or replace view v as
select
cust_id, sum(cust_sales.sales) sales
...
from cust c, cust_sales
where c.cust_id = cust_sales.cust_id;
using
where c.cust_id = cust_sales.cust_id(+) if truly needed.
"Applications come. Applications go. Data lives forever" - quote
AR, February 06, 2006 - 1:44 am UTC
Tom,
I'm curious about the origins of this rather cool quote.
"Applications come. Applications go. Data lives forever."
It appears on the first page of your book and you've used it a few times on this site as well.
Strangely enough, I found it on Sybase's main page too! (www.sybase.com - Click article "Ride the IT Wave".
</code>
http://www.sybase.com/detail?id=1038712 <code>
)
Applications come and go, but data lives forever, said one of Sybases founders - Dr Raj Nathan, senior vice president, Sybase Information Technology Solutions Group
February 07, 2006 - 12:29 am UTC
I believe
X come, X go, Y lives forever
has been around in some form for a long long time. Stick in anything you want for X/Y and you have a new saying.