Date:         Fri, 16 Apr 1999 09:22:20 -0400
Reply-To:     ABELSOR <ABELSOR@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         ABELSOR <ABELSOR@WESTAT.COM>
Subject:      Re: Oracle and SAS
Content-Type: text/plain; charset=US-ASCII

Jules,
 
I'm certainly no Oracle expert, but one option might be to create separate views
for each year.  They wouldn't consume the amount of storage that separate tables
would demand.
 
HTH,
 
Bob Abelson
Westat
 
 
____________________Reply Separator____________________
Subject:    Oracle and SAS
Author: Jules Bosch <jxb@BELLATLANTIC.NET>
Date:       4/15/99 7:08 PM
 
Apologies to all if this appears twice but I posted it much earlier
today from another venue and it seems it never made it.
 
I'm seeking some comments or suggestions from an Oracle dB expert re:
the following.
 
I am responsible for producing SAS reports from a rather large Oracle
dB.  I have no responsibility for the dB. The dB is updated monthly with
the previous month's information.  Each time there is an update
(monthly) a new table is created with twenty-four months of data as in
dollar1-dollar24 plus keys.  By way of example, the Dec 98 update
includes data for the twenty-four month period Jan 97-Dec 98.  And, of
course, the Jan 99 update includes data for the twenty-four month period
Feb 97-Jan 99.  Obviously a large amount of data replication.
 
The project is young and the DBA knew that sooner or later this data
replication problem would have to be solved.  So, the DBA proposes
creating an Oracle table that will contain sixty months (five years) of
data, e.g. dollar1-dollar60 plus keys.  I suggested a separate Oracle
table be built for each year as it makes my life a little easier but the
DBA claims separate yearly Oracle tables would take up 60% more space
than the five-year table and more processing time.  The DBA may be right
but I have no way of knowing for sure.  I know counting columns and
bytes yields an answer but there more issues re: blocks, etc.
 
Of course, there may be other approaches to this problem, too.  So, any
comments or suggestions would be greatly appreciated.
 
Jules Bosch