Looking for table design input

From: <genegurevich_at_discover.com>
Date: Fri, 27 Mar 2009 10:13:35 -0500
Message-ID: <OF4390A563.3012543E-ON86257586.0052DFEA-86257586.0053A69A_at_discover.com>

Hello everybody:

I am looking for some input on a table design (oracle 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.

Received on Fri Mar 27 2009 - 10:13:35 CDT

Original text of this message