Results 1 to 6 of 6

Thread: Data Aging

  1. #1
    Join Date
    Sep 2003
    Posts
    4

    Unanswered: Data Aging

    Hi

    This may be one for the gurus, or it could be standard practice. Don't know.

    I'm looking for some help in designing a database. The problem I have is related to data aging on Oracle instances. I can't find any help on it.

    The problem I have is that I've got 3 categories of data, which is new/warm/old. Essentially, new is data which is 1 hour old. Warm is data which is kept for 24 hours, old is kept for 6-8 weeks.

    So the data needs to age. I think obviously the mechanics of it, Is that I need to partition the data in 1 hour range perhaps, and have a pl/sql package to run every hour, that moves partitions about from new to old, like a sliding window mechanism.

    Thanks for your help.
    Bob

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    table needs some sort of date field populated with SYSDATE upon INSERT.
    You need 3 VIEWs with appropriate filtering in WHERE clause
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2003
    Posts
    4

    Data Aging

    It's really the mechanics of setting up an automatic way of aging data using partitioning and pl/sql, if partitioning is the way of doing it.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What does partitioning provide that VIEW does not?
    Why do you think PL/SQL is required as part of any solution?

    Do you have a solution (partitioning) in search of a problem?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Sep 2003
    Posts
    4

    Data Aging

    No it's a problem looking for a solution. I thought possibly that partioning would be one way of doing it, as a DBA I know suggested it would possibly be one way of doing it.

    The data is incoming messages, on a constant basis, that are strored in live/warm/old categories. Live is stored for 1 hour, warm for 24 hours and old for 6-8 weeks. A message is only added, never updated.

    I need live data to age to warm data, and new live data written into the instance. It needs to be done automatically, i.e. dynamically. I thought using a automated pl/sql package would roll all partitions forward, and add new data to the front partition, thereby making the data age?

    Bob.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Use a timestamp if you want live data, you simply query for it. Index the timestamp column, simple.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Tags for this Thread

Posting Permissions

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