Topic
3 replies Latest Post - ‏2012-06-11T21:31:41Z by MatthiasNicola
redpaul
redpaul
2 Posts
ACCEPTED ANSWER

Pinned topic splitting history based on business time?

‏2012-06-07T09:57:22Z |
looking a v10.1 temporal tables it is only possible to split system_time based history into a separate table.

Is there a reason why business time based history can't also be split into a separate table?
i.e is there anything precluding this changing in the future?

Just interested as we would like to split business time history now (on v9.7 LUW) but be in a good position to migrate to v10.x in the future.

Thanks Paul.
Updated on 2012-06-11T21:31:41Z at 2012-06-11T21:31:41Z by MatthiasNicola
  • MatthiasNicola
    MatthiasNicola
    7 Posts
    ACCEPTED ANSWER

    Re: splitting history based on business time?

    ‏2012-06-07T16:04:45Z  in response to redpaul
    Hi Paul,

    I'm not quite sure what you mean by "business time history rows". If you consider a table with business time only (i.e. not a bitemporal table) then there are no history rows. All rows in a table with business time are current; that is, they represent current information about the past, present, or future of your business.

    History rows are the before-images of updated or deleted rows, but these before-images are captured only in a system time or bitemporal table.

    For example, imagine we have a table with business time, and a row that is valid (in business time) for the year of 2015:

    ( ... , ..., 2015-01-01, 2016-01-01)

    Then imagine we have an update that causes a row-split that results in the following two rows:

    ( ... , ..., 2015-01-01, 2015-06-01)
    ( ... , ..., 2015-06-01, 2016-01-01)

    Which of these rows should be considered history? They both still describe future business time.

    The row that is history now is the original row from 2015-01-01 to 2016-01-01. This row would be captured as history in a bitemporal table.

    Does that make sense?

    Matthias


    Matthias Nicola
    http://www.tinyurl.com/pureXML
    http://nativexmldatabase.com/
  • redpaul
    redpaul
    2 Posts
    ACCEPTED ANSWER

    Re: splitting history based on business time?

    ‏2012-06-11T09:12:39Z  in response to redpaul
    Hi Matthias,

    Many thanks for your explanation and I understand your points now.

    The perspective I am looking at this is from is a DataWarehouse implementation where the customer requires all history in the warehouse to be business time related i.e ATT

    What I had not considered, is that in the general case the ATT may also hold muliple future versions. We were only considering the case of everything being either current or history.

    Many Thanks.
    Paul.
    • MatthiasNicola
      MatthiasNicola
      7 Posts
      ACCEPTED ANSWER

      Re: splitting history based on business time?

      ‏2012-06-11T21:31:41Z  in response to redpaul
      Hi Paul,

      I have one additional comment on this topic.

      Let's say today is June 11, 2012, and I have a row with a business validity period from 06-12-2012 to 06-13-2012. Today, this period is in future. Tomorrow, this row reflects the present. The day after tomorrow this row describes the past. If we wanted to enforce separate physical storage for future, past, or present business time, we would have to examine and move rows every day - if the data type is DATE.

      If you chose the data type for business time to be TIMESTAMP(0), then rows could move from future to present to past every single second...

      Thanks,

      Matthias


      Matthias Nicola
      http://www.tinyurl.com/pureXML
      http://nativexmldatabase.com/