Looking for table design input
Date: Fri, 27 Mar 2009 10:13:35 -0500
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.
Please consider the environment before printing this email.Received on Fri Mar 27 2009 - 10:13:35 CDT