Denormalize or not?

What I'm trying to do is optimize a query. I've tried all I can with the SQL statements, i.e. it's doing what it should as explain plan shows.

Here is the scenario. I have a large table, about 500 million rows, which means I'm worried about disk space, but not as much as performance, i.e. speed of access.

The table have 26 columns

ID                         NUMBER(12)
SUM_ALL           NUMBER(13,2)
SUM_1_5           NUMBER(13,2)
SUM_6_10         NUMBER(13,2)
SUM_11_15       NUMBER(13,2)
SUM_16_20       NUMBER(13,2)
VAL1                  NUMBER(13,2)
VAL2                  NUMBER(13,2)
VAL3                  NUMBER(13,2)
VAL4                  NUMBER(13,2)
VAL5                  NUMBER(13,2)
VAL6                  NUMBER(13,2)
VAL7                  NUMBER(13,2)
VAL8                  NUMBER(13,2)
VAL9                  NUMBER(13,2)
VAL10                  NUMBER(13,2)
VAL11                  NUMBER(13,2)
VAL12                  NUMBER(13,2)
VAL13                  NUMBER(13,2)
VAL14                  NUMBER(13,2)
VAL15                  NUMBER(13,2)
VAL16                  NUMBER(13,2)
VAL17                  NUMBER(13,2)
VAL18                  NUMBER(13,2)
VAL19                  NUMBER(13,2)
VAL20                  NUMBER(13,2)

Things to note:

1. SUM_ALL is just VAL1 + VAL2 + VAL3 + . . . + VAL20
2. SUM_1_5 is just VAL1 + . . . + VAL5
3. SUM_6_10 is just VAL6 + . . . + VAL10
4. SUM_11_15 is just VAL11 + . . . + VAL15
5. SUM_16_20 is just VAL16 + . . . + VAL20
6. Most queries on this table are full table scans.

I want to get rid of the SUM fields to reduce the table size, reduce DISK I/O, and hopefully reduce execution time.

I'm thinking of creating a view to just generate the sum fields on the fly, but I want to know if it would reduce the load on the system. I don't think that summing 40 number columns for the 5 SUM columns would put much load on the system as retrieving 5 more columns from disk.

I appreciate any information on this.
Any references to books that discusses this type of problem are very much welcome.

Thanks in advance.

Please send your response to zlm101_at_psu.edu Received on Wed Jan 01 1997 - 00:00:00 CST