Re: Looking for table design input

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 27 Mar 2009 10:43:59 -0700
Message-ID: <26fdee6e0903271043n1ee33177i3d2aacbe7db4198c_at_mail.gmail.com>



If performance is going to be a problem, and you can improve performance be denormalization, then do it. There are many factors which you have not mentioned such as Service Level Agreement, frequency of update, average transaction size etc etc. that will determine your final decision.

On Fri, Mar 27, 2009 at 8:13 AM, <genegurevich_at_discover.com> wrote:

> Hello everybody:
>
> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Mar 27 2009 - 12:43:59 CDT

Original text of this message