Oracle SQL: 当SYS_GUID()出现在内联视图中时，如何理解它的行为？

Question

以下是有问题的SQL示例；该SQL应该在任何Oracle DBMS上运行（我正在运行11.2.0.2.0）。

请注意结果集中UUID值的不同（一个为898，另一个为899），尽管它们都是在内联视图/with子句中构建的。在下面更深入地了解一下，您可以看到DBMS_RANDOM.RANDOM（）没有这种副作用。

SQL：

WITH data AS (SELECT SYS_GUID () uuid FROM DUAL)
    SELECT uuid, uuid
      FROM data

输出：

UUID                                      UUID_1
F8FCA4B4D8982B55E0440000BEA88F11      F8FCA4B4D8992B55E0440000BEA88F11

与 DBMS_RANDOM 相比，结果是相同的

SQL:

WITH data AS (SELECT DBMS_RANDOM.RANDOM() rand FROM DUAL)
SELECT rand, rand
  FROM data

输出：

RAND    RAND_1
92518726    92518726

更有趣的是，我可以通过调用DBMS_RANDOM.RANDOM来改变行为/稳定sys_guid：

WITH data AS (
        SELECT SYS_GUID () uuid, 
        DBMS_RANDOM.random () rand 
        FROM DUAL)
SELECT uuid a,
       uuid b,
       rand c,
       rand d
  FROM data

稳定 SYS_GUID 的 SQL Fiddle： http://sqlfiddle.com/#!4/d41d8/29409

展示奇怪的 SYS_GUID 行为的 SQL Fiddle： http://sqlfiddle.com/#!4/d41d8/29411

- Brian

---

你可能想要查看https://dev59.com/V1nUa4cB1Zd3GeqPaneP#6776767的前两个答案。由于Oracle在调用函数时有些不可预测（部分原因是由于它优化查询的方式），所以这个问题可能没有一个确定的答案。 - Allan

@Pablissimo 感谢您的关注。我正在尝试找回多年前阅读过的“Ask Tom”杂志文章。我认为sys_guid()可能在幕后实现与sysdate等函数不同（例如，sys_guid()中需要括号）。 - Brian

您可以查看不同的执行计划：SQl-Fiddle。SQL-Server的NEWID()函数具有类似的、不可预测的行为，这取决于所选择的执行计划。 - ypercubeᵀᴹ

进一步研究后发现，“DBMS_RANDOM”这个名称似乎有点神奇，可以给SYS_GUID()提供“修复”行为。我曾经用一个包含返回数字“4”的单个函数的包替换了系统DBMS_RANDOM包，并摧毁了本地安装，但修复行为仍然存在。但是，如果将同样的包命名为“DBMS_RANDOM2”，修复行为就会消失... - Pablissimo

这是你所指的AskTom帖子吗？（“我已经写了成千上万次，你不能依赖SQL会多少次、何时或是否调用你的函数。”） - user533832

Answer 1

文档提供了一个可能导致你看到差异的原因（重点是我的）：

注意：

由于SQL是一种声明性语言而不是命令式（或过程式）语言，即使函数是用PL/SQL编写的，它也无法知道SQL语句调用的函数运行了多少次。 如果您的应用程序要求函数被执行特定次数，请勿从SQL语句中调用该函数。使用游标代替。

例如，如果您的应用程序要求为每个选定的行调用函数，则打开游标，从游标选择行，并为每个行调用函数。这种技术保证了对函数的调用次数等于从游标提取的行数。

基本上，Oracle没有指定在SQL语句内部将调用函数多少次：它可能取决于版本、环境、访问路径等因素。

然而，可以通过以下方式限制查询重写，如嵌套子查询的展开章节所述：

子查询展开将子查询主体展开并合并到包含它的语句主体中，使优化器在评估访问路径和连接时将它们一起考虑。优化器可以展开大多数子查询，但有一些例外。这些例外包括分层子查询和包含ROWNUM伪列、一些集合运算符、嵌套聚合函数或对不是子查询的直接外部查询块进行相关引用的子查询。

如上所述，您可以使用ROWNUM伪列来防止Oracle展开子查询：

SQL> WITH data AS (SELECT SYS_GUID() uuid FROM DUAL WHERE ROWNUM >= 1)
  2  SELECT uuid, uuid FROM data;

UUID                             UUID
-------------------------------- --------------------------------
1ADF387E847F472494A869B033C2661A 1ADF387E847F472494A869B033C2661A

- Vincent Malgrat

---

我可以使用你提到的ROWNUM功能来自动递增ID值（主键）吗？如果可以，那么如果一行被删除会发生什么？SYS_GUID会自动更新它吗？ - kittu

@Satyadev 为什么不使用序列或新的IDENTITY列自增？ - Vincent Malgrat

这就是我在项目中最终使用的。谢谢。 - kittu

Answer 2

NO_MERGE提示可以“修复”它。防止Oracle重新编写内联视图。

WITH data AS (SELECT /*+ NO_MERGE */
                    SYS_GUID () uuid FROM DUAL)
SELECT uuid, uuid
  FROM data

来自文档:

NO_MERGE提示指示优化器不将外部查询和任何内联视图查询合并为一个查询。此提示使您对访问视图的方式具有更多影响力。

应用了NO_MERGE提示的SQL Fiddle:

我仍然在努力理解/表达查询是如何被重新编写的，以至于sys_guid()会被调用两次。也许这是一个错误;但我倾向于认为这是我自己的思想/代码中的错误。

- Brian

---

我正在处理这个问题。不幸的是，我没有安装Oracle...但我会在一天内找到解决办法 :) - Srini V

Answer 3

非常有趣。

我们可以使用Materialize提示来修复它。

WITH data AS (SELECT /*+materialize*/SYS_GUID () uuid FROM DUAL)
    SELECT uuid, uuid
      FROM data;

1   F9440E2613761EC8E0431206460A934C    F9440E2613761EC8E0431206460A934C

我认为，如果我们只是通过添加提示就能改变查询结果，那么这就是Oracle的一个bug。 也许我们需要向metalink咨询一下……

- eliatou

---

如果查询不是确定性的，那么它并不是一个 bug。 - ypercubeᵀᴹ

查询不是确定性的，但行为是！ - eliatou

另外，/*+MATERIALIZE*/ 提示虽然有用，但是没有文档记录 :) - Vincent Malgrat

我知道，但它节省了我很多时间！select * from V$SQL_HINT t where t.NAME='MATERIALIZE' - eliatou

我删除的答案中包含了 /*+ NO_MERGE */。这是一个已记录的提示。 - Brian