Re: Normalized Databases = Poor Performance?

  • From: David Aldridge <david@xxxxxxxxxxxxxxxxxx>
  • To: post.ethan@xxxxxxxxx
  • Date: 21 May 2006 08:07:58 -0700
  • Date: Mon, 08 May 2006 07:18:06 -0600

Aside from refresh performance one of the problems of using MV's, or
summary tables in general, in an OLTP environment is that 3NF-and-higher
schemas are prone to fan and chasm traps from storing metrics in
multiple joined tables (eg. invoice_header and invoice_detail). To get
the correct numbers from parent tables in a fan trap you have to divide
the parent's metrics by the number of child rows, either in the summary
itself or when querying it. This generally slows the process
prohibitively or does not allow fast refresh (becuase you need analytic
functions in the MV definition).

You'd have to be very careful about how you built your MV, and you'd
just select metrics from a single table with no children, in other
words. Rather like a star or snowflake.


Ethan Post wrote: 
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



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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 05-2006