Re: To De-Normalize or Normalize ?

In article <360a208f.11106841_at_news.tele.dk>,   cr_at_post.dk (Christian Roig) wrote:
> How do you get faster response-times from queries ?
>
> Some people argue that you
>
> A)
> have to De-Normalize your database, so that you have fewer joins, which in
 turn
> results in faster response-times from your queries.
>
> B)
> have to Normalize your database, so that you can have more data in memory,
 which
> in turn results in faster response-times from your queries.
>
> What is YOUR experience ?
>
> Why and/or When do YOU either De-Normalize or Normalize your database ?
>
> TIA - Christian
>
> *-*-* Disclaimer *-*-*
> The Questions, Answers, Opinions etc. stated in this
> message are my own !
>

During the database design, you should always NORMALIZE. Oracle is, afterall, a relational database, so you should follow the general rules for normalizing your instance first.

Your real question is: When to De-NORNALIZE for performance? along with: How do you improve performance?

If you are very good at it, you might be able to determine the best approach using analytical methods. I, for one, am not that well versed in the theory or techniques.

Instead I use the tools ORACLE gives me: the EXPLAIN PLAN command and the SQL_TRACE setting. Know what types of queries to avoid. And yes sometimes denormalizing IS the way to go for performance improvements. Don't forget the ANALYZE command either.

So using 1. knowledge of the application and how it will use the data results, 2. the normalized database design and real data relations and business rules, and 3. the performance information from EXPLAIN PLAN and SQL_TRACE, I can usually find changes in either the Query, the database, or the application that provide some improvements.

so IMHO, there are no clear cut rules. It is the application that drives the performance requirements, and that's always different. And ORACLE has so many different was of optimizing, it is not a simple question of normal/denormal. (Look at CLUSTERS, logically NORMAL, but physically DENORMAL.)

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----== Posted via Deja News, The Leader in Internet Discussion ==-----
http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum