Table design decision

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 Received on Wed Jan 11 2006 - 08:16:46 CST