Sybrand,
Are you suggesting that denormalization is never/not usually necessary for
performance gains in Oracle? If so, is it your opinion that 3rd normal form
is the optimal normalization plan in most cases or would you say the degree
of normalization depends on the project?
I've inherited a database that is mostly normalized into what I would call
3rd normal form (if I'm using the term correctly), but several of the larger
tables ( >=10's of millions of rows) contain columns that can be found in
cross reference tables (for example, the table contains a column for the
security_id_code and the security_id_description even though an xref table
containing a list of codes and corresponding desicriptions also exists).
Rather than join the larger tables to an xref table we run scripts that
update the denormalized tables in order to minimize the joins required when
reports are later run against those tables. As you might guess, this has
bitten us in the a** more than a few times. I thought this type of
procedure was fairly common until I read your post. If I ever get a free
minute I'll try testing this theory on my own of course, but since it's
basically working as is I'm not allowed to prioritize this. Just curious if
you find in your experience that this type of denormalization is more
detrimental than helpful. It sounded like it in your previous post.
Thanks for your input...
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:tebvttsjo3mggvm80is17su6hbk50qfu0d_at_4ax.com...
> On Wed, 31 Oct 2001 06:32:13 GMT, "WKC" <wkchen_at_bigfoot.com> wrote:
>
> >Under what circumstances that a table should reference/link to itself? I
've
> >seen ppl do that. Is it a good practice to have that kinda of design?
> >
> >And also how much of a performance sacrify when a query involving joining
2
> >or more tables, compare to a single table inqury?
> >
> >any input is appreciated. thanks
> >
> >-Ken
> >
> I do not know what ppl means, but anyway:
>
> 1 If you have hierarchical relationships in your design, and the
> number of levels in your hierarchy is unlimited the best solution is
> to use a reference to itself.
> Consider the emp case. A non-self referential solution is not going to
> work as any employee can have a unlimited number of people higher in
> the hierarchy. If any parent will always have only *children* and
> *never* have *grandchildren*, in that case you could *consider* using
> a two-table design
>
> 2
> Your priorities are completely wrong.
> The *first* priority in data modeling is having a *normalized* design.
> A *normalized* design means you won't have update and delete
> anomalies.
> Consider the case where you for the sake of your *myth* of performance
> sacrifices *merge* the dept and emp table into one table.
> What is going to happen when you need to have the data of dept 10
> alone?
> What is going to happen when you need to update the location of
> department 20.
> I strongly *urge* you to study normalization. I say this because you
> will run from one problem into another if you don't.
> Performance shouldn't be an issue. In some cases you are going to
> notice your 3NF data model is resulting in performance problems, in
> that case you can consider *controlled* redundancy.
> You seem to be heading in the direction of a fully *un*normalized
> datamodel. You will regret this soon.
>
> Hth
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Wed Oct 31 2001 - 20:36:42 CST
