1

if i have table1, table2, table3..table50 that stores different information about a product

what would be the efficient way to keeping track of incremental changes in a way that if i want to go back and pull how that particular product looked in a give date, it would be very fast and accurate. i would want to track changes in a way that it can be retrieved very fast and also reduce too many redundancy.

Natasha Thapa
  • 979
  • 4
  • 20
  • 41

1 Answers1

0

1.If you are on Oracle 11g, Oracle Flashback technology is the feature that lets you do this. http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFGJHCJ.

2.In older versions, you can use the DBMS_WM package and enable versioning for the tables that you need. However, there are certain restrictions on the kinds of tables you can enable versioning for. http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96628/long_ref.htm#80312

3.The other implementations I have seen so far have their own version of some procedures of DBMS_WM. Basically, have a structure like..

SQL> desc scott_emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                     NOT NULL VARCHAR2(10)
 JOB                                       NOT NULL VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                  NOT NULL DATE
 SAL                                       NOT NULL NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                    NOT NULL NUMBER(2)
 EFF_DATE                                           DATE
 END_DATE                                           DATE

Where the final two columns are used to see for what time period a record was "logically active" in the Database. The implementation is done using triggers where

  • Each Insert/Update is Converted to "Expire the Current Row(update)+ Insert a New Row"
  • Each Delete is Converted to "Expire the Current row"

The last approach might solve your purpose if you only want to track changes to some columns (eg. Let's say only dept and salary changes are all you care about).

Please do not choose a model like this. (Do not Store each column change as a separate row) http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1769392200346820632

Rajesh Chamarthi
  • 18,568
  • 4
  • 40
  • 67
  • Which version of oracle are you using? When you say performant, do you have a set of data (size) that you are working against? how many of the queries will go against historic data and how many will go against current data? Why are none of the above options suitable for your case? – Rajesh Chamarthi Jun 24 '11 at 01:33
  • i use 10g, i am not sure about option 2, but option 3 i had previously also considered, but looked as if the database is going to grow very fast if the changes happens fast – Natasha Thapa Jun 24 '11 at 02:30
  • You are correct. The size of the table will grow considerably fast if you need to track changes to all columns. Also triggers running on so many tables will have a hit on performance too. – Rajesh Chamarthi Jun 24 '11 at 14:19
  • isn't flashback dependent on memory and log files as to far you can go? – Natasha Thapa Jun 24 '11 at 14:28
  • Yes. Since the past versions of the records are recovered using UNDO data, how far you can go back depends on ..whether undo is available for that point or not. In oracle 11g, "Flashback Data Archive (Oracle Total Recall) provides the ability to track and store all transactional changes to a table over its lifetime". – Rajesh Chamarthi Jun 24 '11 at 15:22