Looking for table design input

I am looking for some input on a table design (oracle 10.2.0.3). Here is what I know:

• the table will probably have several hundreds of millions of rows (I am looking for a better estimate from my customers)
• it will store the counts per ID pre date per code for each of the ID. Something like

ID - Year - Month - Day - Code1 - Code2 - Code3 - Count

• We will have a primary key on all the columns except for the count
• The data will be stored for the 15 half-month periods (the Day will be either 1 or 15)
• The data will be selected based on the ID and the users will need to get all the data
• I will partition the table by Year/Month/Day to make purging easier.

What I wonder is whether the following design (which I do not like very much) may give a better performance:

ID - Code1 - Code2 - Code3 - Count1 - Count2 - ... Count15

Because it may potentially return less rows and therefore require less IO.

Any thoughts?

thank you

Gene Gurevich

Please consider the environment before printing this email.

--
http://www.freelists.org/webpage/oracle-l