Oracle的bug？SELECT语句没有重复行，但从SELECT中插入数据却有重复行。

Question

我正在处理一个Oracle实例时遇到一些奇怪的行为。这是在Itanium上的11gR1版本，没有RAC，也没有其他花哨的东西。最终我是在数据仓库场景中从一个Oracle实例移动数据到另一个实例。

我有一个运行在DB链接上的半复杂视图；4个大表的内部连接和5个中等大小表的左连接。

问题在于：当我在SQL Developer（或SQL*Plus）中测试视图时，它似乎很好，没有任何重复。然而，当我实际使用视图将数据插入表中时，我会得到大量的重复项。

编辑：- 数据正在插入一个空表中。查询中的所有表都在数据库链接上。查询中唯一传递的是一个日期（例如INSERT INTO target SELECT * FROM view WHERE view.datecol = dQueryDate） -

我尝试在选择语句中添加一个ROW_NUMBER()函数，按PK对其进行分区。所有行都标记为1。然而，同样的语句作为插入运行时会生成与之前相同的重复项，现在方便地编号。每个键的重复行数不同。有些记录存在4次，有些只存在一次。

我发现这种行为非常令人困惑。 :) 这让我想起了与Teradata一起工作的情况，其中有SET表（仅唯一行）和MULTISET表（允许重复行），但Oracle没有这样的功能。

返回客户端的选择行应与插入到另一个位置的行完全相同。我无法想象这种情况会有合法的理由，但也许我正在遭受想象力的失败。 ;)

我想知道是否还有其他人经历过这种情况，或者这是该平台上的一个错误。

解决方案

感谢@Gary，通过使用“EXPLAIN PLAN FOR {my query}；”和“SELECT * FROM TABLE(dbms_xplan.display);”，我能够找到问题的根源。实际用于INSERT的解释与SELECT非常不同。

对于SELECT，大多数计划操作都是'TABLE ACCESS BY INDEX ROWID'和'INDEX UNIQUE SCAN'。'Predicate Information'块包含查询中的所有连接和过滤器。最后它说“Note - fully remote statement”。

对于INSERT，没有引用索引。'Predicate Information'块只有三行，一个新的'Remote SQL'块显示9个小SQL语句。

数据库将我的查询拆分为9个子查询，然后尝试在本地进行连接。通过运行较小的选择，我已经找到了重复项的来源。

我认为这是Oracle编译器在远程链接方面的一个错误。当重写SQL时，它会导致逻辑上的缺陷。基本上，编译器没有正确地应用WHERE子句。我刚刚进行了测试，并给出了一个包含5个键的IN列表以返回。SELECT将返回5行。INSERT将77,000多行放入目标中，并完全忽略了IN列表。

{仍在寻找一种强制正确行为的方法，我可能不得不要求在远程数据库上创建视图，尽管从开发角度来看这并不理想。我在成功后会编辑此内容...}

- Joe Harris

---

我甚至没有读过你的问题，但我敢打赌：“SELECT语句并没有出现问题”。 - Pete

@Pete 你有多少？你能提供什么逻辑解释，来说明在客户端发出select和在插入中使用它会得到不同的结果？ - Joe Harris

你能发布一下你所选择的视图吗？可能是Oracle在运行时不适当地重写了查询。 - Doug Porter

@Dougman 有趣，我怎么知道是否是这种情况？明天第一件事我会修改查询并发布编辑的。 - Joe Harris

您是在使用相同的数据库账号测试查询和视图吗？我想知道是否对于两个不同的账号，数据库链接和/或表/视图别名指向了不同的位置。 - Pop

Answer 1

似乎是Oracle的Bug，我们找到了以下解决方法： 如果你希望你的"insert into select ..."与你的"select ..."一样工作，你可以将你的select语句封装在一个子查询中。

例如：

select x,y,z from table1, table2, where ...

--> 不重复

insert into example_table
select x,y,z from table1, table2, where ...

--> 重复错误

insert into example_table
select * from (
       select x,y,z from table1, table2, where ...
)

--> 不重复

问候

- Laurent

---

太棒了。谢天谢地我不再参与这个项目了。 :) - Joe Harris

这个解决方案对我不起作用。但是在内部选择中使用 select distinct ... 是有效的。 - Meam

Answer 2

需要注意的一点是，通常情况下SELECT的优化器计划会更偏向于使用FIRST_ROWS计划，以便尽早将行返回给调用者，但INSERT...SELECT则更倾向于使用ALL_ROWS计划，因为它必须传递完整的数据集。 可以使用DBMS_XPLAN.DISPLAY_CURSOR（使用V$SQL中的sql_id）来检查查询计划。

我有一个运行在数据库链接上的半复杂视图；4个大型表的内部连接和5个中等大小表的左连接。 ... 查询中的所有表都在数据库链接上

同样，这可能是一个潜在的问题。如果SELECT中的所有表都在数据库链接的另一端，则整个查询将被发送到远程数据库并返回结果集。一旦加入INSERT，本地数据库更可能接管查询并从子表中获取所有数据。但这可能取决于视图是在本地数据库还是远程数据库中定义的。在后一种情况下，就本地优化器而言，只有一个远程对象，并且它从该对象获取数据，而远程数据库将执行连接操作。

如果直接访问远程数据库并在其中一个表上执行INSERT，会发生什么？

- Gary Myers

---

@Gary 不错的指针。我通过使用“EXPLAIN PLAN FOR {我的查询}；”和“SELECT * FROM TABLE(dbms_xplan.display);”来解决了这个问题。实际用于INSERT的解释与SELECT非常不同。{添加到原始问题的注释} - Joe Harris

Answer 3

这是Oracle在处理DB链接上的连接时出现的错误。我有一个更简单的情况，不涉及INSERT与SELECT。如果我远程运行查询，我会得到重复的行，但如果我本地运行它，我就不会得到。唯一的区别是远程查询中表后面附加的“@...”。我正在使用Oracle SQL Developer 3.0从10.2数据库查询9i数据库。

这比那个阻止您在具有超过1000个总列的表之间进行连接的Oracle错误更愚蠢，当查询ERP系统时非常容易做到这一点。而且，错误消息与表具有太多列无关。

这几乎和另一个Oracle数据库错误一样愚蠢，该错误禁止使用ANSI语法查询包含LOB定位器的表。只有Oracle语法有效！

- Multiverse

Answer 4

仔细检查你的JOIN。可能在各个表中没有重复项，但是未明确指定的连接可能会导致意外的CROSS JOIN，从而使你的结果集由于多重性而具有重复项，并且在插入时，这违反了目标表中的唯一性约束。

在这种情况下，我会将查询嵌套在视图或CTE中，并尝试直接从SELECT中检测重复项：

WITH resultset AS (
    -- blah, blah
)
SELECT a, b, c, COUNT(*)
FROM resultset
GROUP BY a, b, c
HAVING COUNT(*) > 1

- Cade Roux

Answer 5

我有几个选项。

1. 你在目标表中已经看到了重复项吗？

2. 如果在你的SELECT中引用了你正在插入的表格（？），那么INSERT将与你组合的SELECT互动。

   Insert ... Select ... From ...

以这种方式（笛卡尔乘积？）创建重复项

- Charles Bretana

---

第二点似乎意味着SELECT可以“看到”INSERT正在插入的记录，在Oracle的读一致性模型下不是这种情况。 - David Aldridge

感谢您的回复。不幸的是，在这种情况下，两者都不适用。选择操作将进入一个空表中。查询中的所有表都在数据库链接上。传递到查询中的唯一参数是日期（例如：INSERT INTO target SELECT * FROM view WHERE view.datecol = dQueryDate）。 - Joe Harris

Answer 6

我不禁想到，也许你正在经历与表格相关的其他副作用。是否有任何触发器可能正在操作数据？

- Adam Hawkes

---

嗯，好问题。我没有访问另一侧的DDL，但我会找出来的。插入表中肯定没有触发器。 - Joe Harris

Answer 7

你是如何确定原始表中没有重复项的？

正如其他人所指出的那样，这似乎是解释这种奇怪行为的最简单方法。

- Thorsten

---

原始的表格中有非常强的键。我看到的是这些键的副本，但只有在插入到另一个表格时才会出现重复。 - Joe Harris

每个源表都有一个目标表吗？还是你在中间“调整”数据？如果中间有视图，那么它的定义是什么？ - Thorsten

Answer 8

我建议您制定一个查询计划，并查找其中的笛卡尔积连接。这可能表明缺少条件导致了重复行。

- WW.

Answer 9

正如@Pop已经建议的那样，如果您在SQLPlus中使用不同的登录名来运行插入操作，则可能会出现此行为。(也就是说，如果另一个登录名具有相同名称的表/视图/同义词)

- hamishmcn