Tarby777 wrote:
> 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
The current design is bad and based on what you've written your proposed
design may be bad too. What is required to provide any advice is whether
the 50 foreign keys could be collapsed into a single lookup table. It is
always better to think vertically than horizontally.
--
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Wed Jan 11 2006 - 11:20:14 CST
