Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Question Unanswered: [seeking advice] Handling large amounts of archived data

    Hello, I have a question about database design.

    I have some 20 years of data coming down the pipeline, which I will need to store in an Oracle Database. This is expected to be huge (hundreds of gigabytes).

    This database will house all the data, as well as new data.

    The problem is reports. Half of the company needs reporting off of all the data, while the other half only needs current data (this has been defined as data from 2005 and up).

    My original thought is to create 2 databases, one for archived, and one for current, then create some union-views for people to report off of. This way, half the company can run their full reports on the views, while the other half can run quicker reports on the current database only.

    But that seems kind of redundant.

    Does anyone have suggestions?

    `LE

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Store your data in partationed tables. This will allow sub-sets of the data to be queried by date. A quick and dirty would be to make a partation for each year. Only those partations that contain data for the year(s) you specify will be queried.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    108

    Thumbs up

    Quote Originally Posted by beilstwh
    Store your data in partationed tables. This will allow sub-sets of the data to be queried by date. A quick and dirty would be to make a partation for each year. Only those partations that contain data for the year(s) you specify will be queried.
    That would certainly solve my problem, since I won't have to create views joining 2 identical tables.

    But what about overhead and performance? Will the users who need current data suffer?

    `Le

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Thats the beauty of partationed files, the partationed table has global indexes and the each partations have local indexes. It is like accessing seperate tables.

    For some good write-ups, I would go to HTTP://ASKTOM.ORACLE.COM and query on "partitioned table". He has a lot of very good articles on partationing.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    May 2006
    Posts
    132
    Definitely agree with beilstwh, however if you don't already have a license for it, be aware that Partitioning is an additional cost option.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    True, however if you have the enterprise version, I believe it comes with the database. An easy way to find out is to login to sql*plus, it will return something like

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
    With the Partitioning option
    JServer Release 8.1.7.4.0 - 64bit Production
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  7. #7
    Join Date
    May 2006
    Posts
    132
    True, unfortunately whether you are licensed to use it or not is a different story. 10g finally contains some good licensing information Oracle Partitioning.

    I'm sure a ton of folks are actually using it without a license.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •