Re: Normalized Databases = Poor Performance?

From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
To: paul.baumgartel@xxxxxxxxxxxxxxxxx
Date: Thu, 04 May 2006 21:47:32 +0200

The mention of "silver bullets" automatically rings for me this bell, a classic paper by the author of the no less classic "Mythical Man Month":

www.lips.utexas.edu/ee382c-15005/Readings/Readings1/05-Broo87.pdf

(a useful reference for meetings).

The only cases when I have ever seen denormalization improving performance was when the database was far from being normalized in the very first place (and yet it was a slippery slope; two wrongs don't make one right). Not to say that MVs are totally useless; but you should consider them as an ETL process of sort, mainly that you have two different problems at hand that happen to use what superficially looks like the same data, but are indeed different representations of reality.

HTH

Stéphane Faroult


Baumgartel, Paul wrote:

I don't know if it's still around, but at 2004 IOUG in Toronto, there was a presentation that discussed the benefits of normalization in general, and normalization beyond 3NF in particular, which included /better/ performance. I regret that I didn't attend Hotsos this year, but I had just started a new job... *Paul Baumgartel* *CREDIT SUISSE* Information Technology DBA & Admin - NY, KIGA 1 11 Madison Avenue New York, NY 10010 USA Phone 212.538.1143 paul.baumgartel@xxxxxxxxxxxxxxxxx www.credit-suisse.com
    -----Original Message-----
    *From:* oracle-l-bounce@xxxxxxxxxxxxx
    [mailto:oracle-l-bounce@xxxxxxxxxxxxx]*On Behalf Of *Ethan Post
    *Sent:* Thursday, May 04, 2006 3:10 PM
    *To:* _oracle_L_list
    *Subject:* Normalized Databases = Poor Performance?
In light of Jared's highly informative presention on why we should normalize for performance (Hotsos 2006) I was struck by this statement. http://www.quest-pipelines.com/newsletter-v7/newsletter_0406.htm
        Materialized views are an Oracle Silver Bullet when
        pre-joining tables together for super-fast response time.
        One issue with highly-normalized, non-redundant Oracle table
        designs (e.g. third normal form) is that Oracle experiences a
        high degree of overhead (especially CPU consumption) when
        joining dozens of tables together, over-and-over again.
        Using materialized views we pre-join the tables together,
        resulting in a single, fat, wide and highly-redundant table.
Not trying to start a flame war or anything here! While there are certainly "truisms" in the statement above, it does seem to me at first glance to be a statement that feeds into the "normalization hurts performance" mindset. I have not read the entire article yet. Thanks, Ethan

============================================================================== Please access the attached hyperlink for an important electronic communications disclaimer:

http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html ==============================================================================

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

References:
- RE: Normalized Databases = Poor Performance?
  - From: Baumgartel, Paul

Re: Normalized Databases = Poor Performance?

Other related posts: