Oracle数据库FDA闪回查询底层执行逻辑与转换规则问询

问题背景 #

你编写的闪回查询语句如下：

select * from TEST_TABLE as of timestamp (timestamp 2021.05.05 15:00:15);

直接复制执行计划中的谓词条件到TEST_TABLE的WHERE子句后查询结果和原语句不一致，原因是AS OF查询并非仅在原表增加过滤条件，而是需要联合基表和闪回归档的历史数据做拼接计算。

核心执行逻辑 #

• 第一步：时间转SCN
  Oracle会先将你输入的目标时间2021.05.05 15:00:15转换为对应时刻的系统变更号（SCN），即执行计划中出现的148411288669，另一个SCN155682149589为你执行本次查询时刻的当前系统SCN。
• 第二步：多源数据过滤关联
  闪回查询的数据来源分为两部分，执行计划中的谓词分别作用在不同的数据源上：
  1. 业务基表TEST_TABLE当前数据：对应执行计划id=7的过滤条件，筛选目标SCN时刻仍然在当前基表中存在、未被删除的有效记录：

     (T.VERSIONS_STARTSCN IS NULL OR T.VERSIONS_STARTSCN <= 148411288669) 
     AND (T.VERSIONS_ENDSCN IS NULL OR T.VERSIONS_ENDSCN > 148411288669) 
     AND (T.VERSIONS_OPERATION IS NULL OR T.VERSIONS_OPERATION <> 'D')
     

  2. 闪回归档历史数据：存放在Oracle内部的闪回归档系统表中，通过行ID和基表做左外连接（对应执行计划id=9的关联条件RID(+)=ROWIDTOCHAR("T".ROWID)），执行计划中id=4、5、8的过滤条件作用在这部分数据上，筛选出目标SCN时刻有效、但后续被更新或删除、已经不在当前基表中的历史记录，同时排除目标SCN之后才产生的变更数据。
• 第三步：结果合并去重
  Oracle会隐式将基表的有效记录和闪回归档的有效历史记录合并去重，最终得到目标时间点的全量数据，这个合并步骤你手动仅查询基表无法实现，自然结果和原闪回查询不一致。

等价手动实现方式 #

如果要手动写查询实现和AS OF语句完全一致的效果，需要使用VERSIONS BETWEEN SCN语法（要求表已开启闪回归档或行移动功能），示例如下：

select * from TEST_TABLE
VERSIONS BETWEEN SCN 148411288669 AND 148411288669
where (VERSIONS_STARTSCN IS NULL OR VERSIONS_STARTSCN <= 148411288669)
AND (VERSIONS_ENDSCN IS NULL OR VERSIONS_ENDSCN > 148411288669)
AND (VERSIONS_OPERATION IS NULL OR VERSIONS_OPERATION <> 'D');

内容的提问来源于stack exchange，提问作者andrea_sottolamontagna