Re: Normalized Databases = Poor Performance?
- From: ryan_gaffuri@xxxxxxxxxxx
- To: sfaroult@xxxxxxxxxxxx, paul.baumgartel@xxxxxxxxxxxxxxxxx
- Date: Thu, 04 May 2006 19:56:08 +0000
The performance improvements from 3NF data models is because it's easier to
write sql. When you have denormalized databases you get data all over the place
and it's often out of synch. A column with a name in one table will often have
different values than a column with the same name in another table. In the past
I ended up writing really whacky queries because of this and when I had to go
back and get historical data, I had trouble getting a consistent view of the
data.
The downside in theory to highly normalized databases is you often have to do
alot of joins. If you can test this and see in typically OLTP environments the
performance hits are minimal. The vast majority of queries are index unique or
range scans without a large range. So your logical IO is going to be low in
spite of the joins.
You can get performance improvements by denormalizing for batch processing.
Doing 2 full tablescans, hash joining them together is alot more intensive than
doing just one. However, you really have to weigh the cost of denormalization
on your team. It is much harder to understand a database when data is
denormalized. When data is normalized you just follow the foreign keys.
Projects have turn over and every new person who comes in has to learn this
model to get up to speed.
There is a very good book by some guys who have been around for 20 years called
'The Pragmatic Programmer'. They talk about normalizing code and not writing
the same code in multiple places. The pros of doing that are the same as the
pros of doing it with the data model.
-------------- Original message --------------
From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
> 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
>
>
Other related posts:
