甲骨文 10 : Incomprehensible behaviour on INSERT into a view?

Question

我们这里有一个奇怪的问题，我们无法向自己解释。

我们在 Oracle DB 版本 10.2.0.5.8 中有一个 View 。该 View 使用 INSTEAD OF 触发器。

这是触发器的代码:

CREATE OR REPLACE TRIGGER V1_T1_BIUD
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON V1_T1
  FOR EACH ROW
DECLARE
  AnyId   NUMBER;
BEGIN
  IF INSERTING THEN
    INSERT INTO Table T1 (
       F1, F2, F3, F4, F5
    ) VALUES (
       :new.F1, :new.F2, :new.F3, :new.F4, :new.F5
    );
  ELSIF UPDATING THEN
    UPDATE T1 SET F1 = :new.F1,
                  F2 = :new.F2,
                  F3 = :new.F3,
                  F4 = :new.F4,
                  F5 = :new.F5
    WHERE F1 = :old.F1;
  ELSIF DELETING THEN
    DELETE FROM T1
    WHERE F1 = :old.F1;
  END IF;
END;
/

这是一个示例 INSERT 语句:

INSERT INTO V_T1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
  SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION ALL 
  SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION ALL
  SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL           
) A
ORDER BY 1, 2, 3;
COMMIT;

注意选择末尾的 ORDER BY 子句。这个 INSERT 语句的结果是这样的:

F1 F2 F3  F4 F5
---------------
E  N  ABC I  I 
E  Y  QWE I  I 
I  Y  GHJ I  I  

如您所见，第 4 列和第 5 列错误地填充了所有其他数据行中最后一个数据行的值。

如果我们像这样更改 INSERT 语句:

INSERT INTO V_T1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
  SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION ALL 
  SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION ALL
  SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL           
) A
ORDER BY 1, 2, 3, 4, 5;
COMMIT;

结果是这样的:

F1 F2 F3  F4 F5
---------------
E  N  ABC E  E 
E  Y  QWE O  E 
I  Y  GHJ I  I

再次注意 ORDER BY 子句，它现在对所有行进行排序，而不是第一个插入语句中的前三行。

编辑:如果省略 ORDER BY 子句，结果也符合预期(例如，如示例 2 中所示)。

有人可以向我解释这种行为吗？

P.S. 关于评论:

我今天没有时间调查或提供有关此主题的更多信息。我将在我们的数据库上创建一个完整的示例，并在接下来的几天内在此处发布。感谢您的耐心等待!

Answer 1

这确实看起来像一个错误，但我在错误数据库中找不到明显的匹配项(一些看起来可能，如 5842445，但含糊不清或不太一致)。我只能通过触发器来实现(所以我假设你的插入是针对 T1 而不是 V1_T1 是转录错误)；并且仅当 F4和 F5是 CHAR不是 VARCHAR2 :

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 char(2), f5 char(2));

create view v1_t1 as select * from t1;

... 和 instead of完全按照问题所示触发。
:NEW根据 DBMS_OUTPUT，触发器内的值是错误的，但是我认为只有 Oracle 能够弄清楚列数据类型如何影响它。

它也仍然发生在 11.2.0.3 (Linux) 中。有趣的是，如果我更改 UNION ALL只是 UNION我得到的结果略有不同；在 10g 中，两列最终为空，在 11g 中它们有 x :

insert into v1_t1 (
  F1, F2, F3, F4, F5
)
SELECT A.V, A.S, A.F, A.T, A.Z
FROM (
    SELECT 'E' V, 'N' S, 'ABC' F, 'E' T, 'E' Z FROM DUAL UNION
    SELECT 'E',   'Y',   'QWE',   'O',   'E'   FROM DUAL UNION
    SELECT 'I',   'Y',   'GHJ',   'I',   'I'   FROM DUAL
) A
ORDER BY 1, 2, 3;

3 rows created.

select * from v1_t1;

F1 F2 F3  F4 F5
-- -- --- -- --
E  N  ABC x  x
E  Y  QWE x  x
I  Y  GHJ x  x

...这甚至更奇怪 - 看起来可能是对其他错误的修复对这个错误产生了轻微的影响。

所以不是真正的答案；您需要向 Oracle 提出服务请求，我很确定他们只会告诉您删除 order by因为它没有任何值(value)，正如您已经知道的那样。

对于蒂洛；没有任何计划 order by (11 克):

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |     9  (34)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   VIEW                   |       |     3 |    51 |     9  (34)| 00:00:01 |
|   3 |    SORT UNIQUE           |       |     3 |       |     9  (78)| 00:00:01 |
|   4 |     UNION-ALL            |       |       |       |            |          |
|   5 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   6 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |      FAST DUAL           |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

并与 order by 1,2,3 一起计划或 1,2,3,4,5 - 相同的计划哈希值 (11g):

----------------------------------------------------------------------------------
| Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |       |     3 |    51 |    10  (40)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | V1_T1 |       |       |            |          |
|   2 |   SORT ORDER BY          |       |     3 |    51 |    10  (40)| 00:00:01 |
|   3 |    VIEW                  |       |     3 |    51 |     9  (34)| 00:00:01 |
|   4 |     SORT UNIQUE          |       |     3 |       |     9  (78)| 00:00:01 |
|   5 |      UNION-ALL           |       |       |       |            |          |
|   6 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   7 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
|   8 |       FAST DUAL          |       |     1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------

而且我看到从其他表中选择的相同类型的损坏，但前提是子查询中的结果在排序之前是联合的；虽然后来我得到了空值而不是 x . (我想知道 x 是否来自 dual 本身，但 dummy 是大写的 X ，这显示了小写的 x )。

按照@Annjawn 的评论，将插入内容从 V1_T1 更改为直接插入 T1工作正常(即插入了正确的值)，并且奇怪的是具有相同的计划哈希，即使它显示的是表名而不是 Name 中的 View 柱子。与 UNION 一起工作或 UNION ALL 10gR2 和 11gR2 中也是如此。我猜这似乎是被工会弄糊涂的触发器。

进一步到数据类型点... View 必须具有 char列，表不一定，这并不奇怪，因为 View 上的触发器似乎是问题所在。如果我用 char 设置 table 列但将它们转换为 varchar2在 View 中，我没有看到问题:

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 char(2), f5 char(2));

create view v1_t1 as select f1, f2, f3, cast(f4 as varchar(2)) f4,
    cast(f5 as varchar(2)) f5
from t1;

但是如果我反过来做，它确实会出现问题:

create table t1 (f1 varchar2(2), f2 varchar2(2), f3 varchar2(3),
    f4 varchar(2), f5 varchar(2));

create view v1_t1 as select f1, f2, f3, cast(f4 as char(2)) f4,
    cast(f5 as char(2)) f5
from t1;