sql - 闪回版本查询

Question

select语句返回两行

id || description1        TWO2        TWO 

I am kind of expecting three rows

id || description1 ONE1        TWO2        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;

我能想到的唯一原因是如果 时间戳最小值和最大值 采用 DML 时间戳值而不是 DDL 时间戳值......请对此有所了解!

Answer 1

您认为缺少的行将来自于此:

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

...但数据从未在该状态下提交，因为:

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

... 发生在您的 COMMIT 之前.所以这与您引用的声明一致， 1, ONE不是该行的已提交事件。从来没有任何时间点另一个 session 可以看到这些值。

如果你看 version data pseudocolumns您可以看到这两行都被视为带有当前数据的插入:

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

如果您在第一个 insert 之间提交和 update您可以看到三行以及它们是如何修改的:

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

我不完全确定为什么 SLEEP需要调用，但没有它们就不能正常工作(伪列是空白的，只显示当前数据)。我认为我从未见过对此有很好的解释，但这在现实世界中不太可能成为问题。

来自 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.

通常 MINVALUE将受到撤消保留的限制；由于这是一个新表，它会返回到创建表的点，这小于撤消保留。您不能再返回，因为它没有任何意义:如果您尝试在表创建时间之前使用显式时间戳值，它会告诉您表结构已更改。不过，您所做的 DML/DDL 区别并不真正相关，因为您提交数据的时间，您只能看到两行。