Table design decision
- From: "Tarby777" <nick_williamson@xxxxxxxxxxx>
- Date: 11 Jan 2006 06:16:46 -0800
Hi all,
I've inherited a monster table, and have to make a decision about
how/whether to normalize it.
It has 50 FK columns, each one with a FK constraint and a supporting
index. The worst thing from a design perspective is that in each row,
only exactly one of those FK columns will ever be populated in any
given row. All the columns get populated, but never in the same row. So
as things stand, every row contains 49 nulls, and there are 49 index
writes that are of little use because the application will never
"select * from this_table where FK column is null".
BTW, each one of the FKs is set up for cascading deletes, so a single
table with a single FK column isn't an option. Currently, a row in the
existing table is deleted automatically when a row in any of the 50
tables referenced by the FKs is deleted.
I'm thinking about replacing the existing table with 50 new tables,
each having one FK column with a supporting index, but I'm unsure of
what how it might affect performance. I plan to implement this
structure in a new DB to test it, but I'd be interested to hear from
you all about whether you think the way in which I plan to revamp this
table will help or hinder performance. I'm guessing that writes will be
quicker, mainly because there will be 49 fewer index keys to write, but
I'm not so sure about reads. At the moment, all the data is held in one
table, so I'm guessing that there's at least *some* chance that during
a SELECT, a single block read might find > 1 of the required rows. In
the new model, those two rows could be in different tables and that
would mean two physical reads, two blocks read into the buffer pool and
a possible negative impact on the buffer pool hit rate. Also, I guess I
might need to increase the dictionary cache size, or suffer the pain of
more recursive SQL.
I appreciate it might not be possible to give detailed recommendations
without knowing how many rows are involved / how often the table is
read/written etc, but any advice you can give is welcome. FWIW, the app
runs on 9iR0, 9iR2, 10g and XE.
TIA
Tarby
.
- Follow-Ups:
- Re: Table design decision
- From: DA Morgan
- Re: Table design decision
- From: Mark C. Stock
- Re: Table design decision
- Prev by Date: Re: Unable to Delete table
- Next by Date: Error in writing to directory /tmp/OraInstall2005...
- Previous by thread: Unable to Delete table
- Next by thread: Re: Table design decision
- Index(es):
Relevant Pages
|
|
