Re: Looking for table design input

From: <genegurevich_at_discover.com>
Date: Fri, 27 Mar 2009 13:57:57 -0500
Message-ID: <OF23360FAA.7DD43831-ON86257586.0067FBE1-86257586.006830FD_at_discover.com>

At this point I am trying to do now is to compe up with an initial design. I do lean towards a more normalized one , but I am also trying to see whether there is reasonable possibility that denormalizing will indeed give a significant performance improvement. If it looks reasonable I would rather start with the denormalized design so that we don't have to make a big change at the end of the process

thank you

Gene Gurevich

                                                                       
             Michael Moore                                             
             <michaeljmoore_at_gm                                         
             ail.com>                                                   To
             Sent by:                  oracle-l_at_freelists.org        
             oracle-l-bounce_at_f                                          cc
             reelists.org                                            
                                                                   Subject
                                       Re: Looking for table design input
             03/27/2009 12:49                                          
             PM                                                        
                                                                       
                                                                       
             Please respond to                                         
             michaeljmoore_at_gma                                         
                  il.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

Please consider the environment before printing this email.

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

Received on Fri Mar 27 2009 - 13:57:57 CDT