Archive for December, 2012

Keep the latest modification’s timestamp for each row

December 19, 2012

It is well known that Oracle stores the latest modification’s timestamp
with SCN (System Change Number) on data block level.
Sometimes we want to keep this information on row level.
To achieve this feature we can use the ROWDEPENDENCIES clause in the CREATE TABLE statement.

DROP TABLE emp2 PURGE;
CREATE TABLE emp2
( employee_id NUMBER(6),
   last_name  VARCHAR2(25),
   dml        VARCHAR(20),
   salary     NUMBER(8,2))  ROWDEPENDENCIES;

With this option Oracle will allocate six bytes for storing the appropriate SCN for each row.
Note, that You can not add this clause later, with an ALTER TABLE statement. However you can use it in CTAS:

CREATE TABLE emp33 AS SELECT * FROM employees ROWDEPENDENCIES;

Let’s populate this table with the following INSERT SELECT statement:

INSERT INTO emp2
SELECT employee_id,  last_name , 'Insert First', salary
FROM employees WHERE salary<3000;
COMMIT;

 

Now check the result with ORA_ROWSCN pseudo column and convert it into timestamp:

SELECT  SCN_TO_TIMESTAMP(ORA_ROWSCN)  TIMESTAMP,
last_name , dml, salary,
TO_CHAR(ORA_ROWSCN,'999,999,999,999,999') ORA_ROWSCN
FROM emp2;

TIMESTAMP                         LAST_NAME   DML                  SALARY ORA_ROWSCN
--------------------------------- ----------- -------------------- ------ --------------------
19-DEC-12 12.11.42.000000000 PM   Oconnell    Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Grant       Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Baida       Insert First           2900           30,401,401
19-DEC-12 12.11.42.000000000 PM   Tobias      Insert First           2800           30,401,401
19-DEC-12 12.11.42.000000000 PM   Himuro      Insert First           2600           30,401,401
19-DEC-12 12.11.42.000000000 PM   Colmenares  Insert First           2500           30,401,401
19-DEC-12 12.11.42.000000000 PM   Mikkilineni Insert First           2700           30,401,401
19-DEC-12 12.11.42.000000000 PM   Landry      Insert First           2400           30,401,401
19-DEC-12 12.11.42.000000000 PM   Markle      Insert First           2200           30,401,401
19-DEC-12 12.11.42.000000000 PM   Atkinson    Insert First           2800           30,401,401
19-DEC-12 12.11.42.000000000 PM   Marlow      Insert First           2500           30,401,401
19-DEC-12 12.11.42.000000000 PM   Olson       Insert First           2100           30,401,401
19-DEC-12 12.11.42.000000000 PM   Rogers      Insert First           2900           30,401,401
19-DEC-12 12.11.42.000000000 PM   Gee         Insert First           2400           30,401,401

We can issue a MERGE statement (introduced in Oracle 9i, enhanced in Oracle 10g)
to modify the content of this table:
– Let’s increase the salary of those people whose salary less than or equal 2500$
– Delete those rows from the target table where the salary greater than 5000$
– Insert those rows from the source whose salary greater than 12000$
Consider the statement:

MERGE INTO emp2  c USING
(SELECT employee_id, last_name, 'x',salary FROM EMPLOYEES  ) e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
 UPDATE SET
c.last_name = UPPER(e.last_name),
 c.dml       = 'Update',
c.salary    = e.salary * 2 WHERE e.salary <= 2500      DELETE WHERE (c.salary>5000)
 WHEN NOT MATCHED THEN
  INSERT VALUES(e.employee_id, e.last_name,'Insert Second', e.salary)
  WHERE e.salary>12000;
COMMIT;

Check the result with the following SELECT statement:

SELECT  SCN_TO_TIMESTAMP(ORA_ROWSCN)  TIMESTAMP,
last_name , dml, salary,
TO_CHAR(ORA_ROWSCN,'999,999,999,999,999') ORA_ROWSCN
FROM emp2;

TIMESTAMP                         LAST_NAME   DML                  SALARY ORA_ROWSCN
--------------------------------- ----------- -------------------- ------ --------------------
19-DEC-12 11.47.55.000000000 AM   Oconnell    Insert First           2600           30,397,931
19-DEC-12 11.47.55.000000000 AM   Grant       Insert First           2600           30,397,931
19-DEC-12 11.47.55.000000000 AM   Baida       Insert First           2900           30,397,931
19-DEC-12 11.47.55.000000000 AM   Tobias      Insert First           2800           30,397,931
19-DEC-12 11.47.55.000000000 AM   Himuro      Insert First           2600           30,397,931
19-DEC-12 11.51.27.000000000 AM   COLMENARES  Update                 5000           30,398,452
19-DEC-12 11.47.55.000000000 AM   Mikkilineni Insert First           2700           30,397,931
19-DEC-12 11.51.27.000000000 AM   LANDRY      Update                 4800           30,398,452
19-DEC-12 11.51.27.000000000 AM   MARKLE      Update                 4400           30,398,452
19-DEC-12 11.47.55.000000000 AM   Atkinson    Insert First           2800           30,397,931
19-DEC-12 11.51.27.000000000 AM   MARLOW      Update                 5000           30,398,452
19-DEC-12 11.51.27.000000000 AM   OLSON       Update                 4200           30,398,452
19-DEC-12 11.47.55.000000000 AM   Rogers      Insert First           2900           30,397,931
19-DEC-12 11.51.27.000000000 AM   GEE         Update                 4800           30,398,452
19-DEC-12 11.51.27.000000000 AM   PHILTANKER  Update                 4400           30,398,452
19-DEC-12 11.47.55.000000000 AM   Seo         Insert First           2700           30,397,931

You can see two the different SCN numbers with two different timestamps accordingly.

Advertisements