RE: normalization
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <malcolmarnold@xxxxxxxxx>, <cstephens16@xxxxxxxxx>
- Date: Wed, 2 Nov 2005 06:46:48 -0500
It seems to me that you're presuming a particular ratio between the
frequency of update and the frequency of retrieval. Now there is an actually
useful ratio - if the forecast rate of change is low compared to the
forecast rate of retrieval then there is an argument for pre-calculation in
the physical model (by whatever means). Of course if this is a very "narrow"
table then the cost of the extra size may be significant. If the average row
is very long and the addition of the virtual column greatly increased the
number of multiblock rows that would also be a problem. Of course those are
extreme examples just to make the point that "which is faster" can only be
determined with actual data and operational details.
Steve's distinction between logical and physical model considerations was
exactly the right place to put the argument. My suggestion would be to first
test physically with representative data before you lock in a decision.
Either way you should only be talking about a change in the application code
of bulk replacing an equation with the virtual column name or the reverse.
IF particular data pushes the results in favor of pre-calculation, I
advocate a canonical identification of the virtual columns. I've seen v_
(for virtual) and c_ (for calculated) used as prefixes to good effect. I'm
not aware of any standard.
Regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of malcolm arnold
Sent: Wednesday, November 02, 2005 6:20 AM
To: cstephens16@xxxxxxxxx
Cc: Oracle-L Freelists
Subject: Re: normalization
I'm signing up for the denormalised team...
The resource cost of maintaining a calculated field the few times a
row is inserted or updated would seem to be a lot less than resource
cost of re-calculating it everytime it's selected (over and over and
over).
And if we're talking about summary fields, IMO, these should not be
maintained by triggers, because I hate triggers, and also because I
believe summaries should be maintain asyncronously. Maintaining
summaries syncronously means you serialise on the highest level of
summary.
Malcolm.
<snip>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
