Flashback versions query

Question

The select statement returns two rows

id || description
1        TWO
2        TWO 

I am kind of expecting three rows

id || description
1        ONE
1        TWO
2        TWO 

according to the statement from the link which reads It returns all the committed occurrences of the rows for a query of an object, while NOT displaying the UNCOMMITTED row versions. The code is as follows :-

CREATE TABLE digits
(id NUMBER(2),
description VARCHAR2(15));
INSERT INTO digits VALUES (1,'ONE');
UPDATE digits SET description ='TWO' WHERE id=1;
INSERT INTO digits VALUES (2,'TWO');
COMMIT;
DELETE FROM digits;
SELECT id,description FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

The only reason i could think of is that if timestamp minvalue and maxvalue takes the DML timestamp value and not the DDL ones .....please throw some light on this !

Answer 1

The row you think is missing would come from this:

INSERT INTO digits VALUES (1,'ONE');

... but the data is never committed in that state because this:

UPDATE digits SET description ='TWO' WHERE id=1;

... occurred before your COMMIT. So that aligns with the statement you quoted, 1, ONE is not a committed occurrence of the row. There was never any point in time where another session could see those values.

If you look at the version data pseudocolumns you can see that both rows are seen as inserts with their current data:

CREATE TABLE digits (id NUMBER(2), description VARCHAR2(15));
EXEC dbms_lock.sleep(10);

INSERT INTO digits VALUES (1,'ONE');
UPDATE digits SET description ='TWO' WHERE id=1;
INSERT INTO digits VALUES (2,'TWO');
COMMIT;

SELECT id, description, versions_xid, versions_operation
FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

        ID DESCRIPTION     VERSIONS_XID     V
---------- --------------- ---------------- -
         2 TWO             08001B005C0D0100 I
         1 TWO             08001B005C0D0100 I

If you commit between the first insert and update you can see the three rows and how they were modified:

CREATE TABLE digits (id NUMBER(2), description VARCHAR2(15));
EXEC dbms_lock.sleep(10);

INSERT INTO digits VALUES (1,'ONE');
COMMIT;
EXEC dbms_lock.sleep(10);

UPDATE digits SET description ='TWO' WHERE id=1;
INSERT INTO digits VALUES (2,'TWO');
COMMIT;

SELECT id, description, versions_xid, versions_operation
FROM digits
VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

        ID DESCRIPTION     VERSIONS_XID     V
---------- --------------- ---------------- -
         2 TWO             060018007C0C0100 I
         1 TWO             060018007C0C0100 U
         1 ONE             05000B00450C0100 I

I'm not entirely sure why the SLEEP calls are needed, but it doesn't quite work without them (the pseudocolumns are blank and only the current data is shown). I don't think I've ever seen a good explanation of that, but it isn't likely to be an issue in a real-world case.

---

From the documentation:

Specify BETWEEN TIMESTAMP ... to retrieve the versions of the row that existed between two timestamps. Both expressions must evaluate to a timestamp value and cannot evaluate to NULL. MINVALUE and MAXVALUE resolve to the timestamp of the oldest and most recent data available, respectively.

Normally MINVALUE would be restricted by the undo retention; since this is a new table it would go back to the point the table was created, which is less than the undo retention. You can't go back beyond that because it wouldn't make any sense: if you tried to use an explicit timestamp value before the table creation time, it would tell you the table structure had changed. The DML/DDL distinction you're making isn't really relevant though, you're only seeing two rows because of when you committed the data.