RE: normalization

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of malcolm arnold Sent: Wednesday, November 02, 2005 6:20 AM To: cstephens16_at_gmail.com
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>

--
http://www.freelists.org/webpage/oracle-l