绑定变量和NVL列ORA_ROWSCN上的查询结果错误(Oracle12c)

Question

在将数据库从Oracle 11迁移到Oracle 12c (12.2.0.1.0)的过程中，我们发现相同的查询在这两个实例上给出了不同的结果。

查询是：

VARIABLE myId NUMBER;
BEGIN :myId := 1325; END;
/

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

在Oracle 11上，我们有：

      RID ORA_ROWSCN
--------- ----------
     1325 1.3439E+13
     1325 1.3439E+13

在Oracle 12c上，我们有：

      RID ORA_ROWSCN
--------- ----------
     1325
     1325

和下面的查询

SELECT
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(1325, rid);

给予：

      RID ORA_ROWSCN
--------- ----------
     1325    2549788
     1325    2549788

(显然，ORA_ROWSCN列的不同值是预期的。)

MY_TABLE.RID是NUMBER(38) NOT NULL，上面有一个非唯一的索引。

我强烈怀疑错误的结果是优化器的意外结果，但是，由于我对DBs的经验非常有限，所以对于如何修复这种奇怪的行为，我没有任何线索。

这两个查询的执行计划如下：

Plan hash value: 709374914

---------------------------------------------------------------------------------------
| Id  | Operation           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                 |    52 |   676 |     2   (0)| 00:00:01 |
|   1 |  VIEW               | VW_ORE_55EECC8D |    52 |   676 |     2   (0)| 00:00:01 |
|   2 |   UNION-ALL         |                 |       |       |            |          |
|*  3 |    FILTER           |                 |       |       |            |          |
|*  4 |     INDEX RANGE SCAN| MY_TABLE_INDEX1 |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    FILTER           |                 |       |       |            |          |
|   6 |     INDEX FULL SCAN | MY_TABLE_INDEX1 |    51 |   663 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:MYID IS NOT NULL)
   4 - access("RID"=:MYID)
   5 - filter(:MYID IS NULL)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

和

Plan hash value: 460232730

-------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |     2 |    26 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE        |     2 |    26 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | MY_TABLE_INDEX1 |     2 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RID"=1325)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

我的问题是:如何在Oracle 12c上获得与到目前为止在Oracle 11上获得的结果相同的结果？

Answer 1

该计划清楚地显示了OR-expansion查询转换。这不是第一次因为各种各样的错误而产生错误的结果。

尝试按以下方式运行您的查询：

SELECT /*+ NO_EXPAND */
  rid, ora_rowscn
FROM
  my_table
WHERE
  rid = NVL(:myId, rid);

或在执行之前设置以下参数：

alter session set "_no_or_expansion"=true;
alter session set "_or_expand_nvl_predicate"=false;

然后运行您的查询。