sql - Oracle SQL : Understanding the behavior of SYS_GUID() when present in an inline view?

Question

这里是有问题的 SQL 示例； SQL 应该在任何 Oracle DBMS 上运行(我正在运行 11.2.0.2.0)。

请注意结果集中的 UUID 值有何不同(一个有 898，另一个有 899)，尽管是从内联 View /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

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

Answer 1

documentation gives a reason至于为什么您可能会看到差异(强调我的):

Caution:

Because SQL is a declarative language, rather than an imperative (or procedural) one, you cannot know how many times a function invoked by a SQL statement will run—even if the function is written in PL/SQL, an imperative language. If your application requires that a function be executed a certain number of times, do not invoke that function from a SQL statement. Use a cursor instead.

For example, if your application requires that a function be called for each selected row, then open a cursor, select rows from the cursor, and call the function for each row. This technique guarantees that the number of calls to the function is the number of rows fetched from the cursor.

基本上，Oracle 并没有指定一个函数在一条 sql 语句中被调用多少次:它可能取决于版本、环境、访问路径等因素。

但是，有一些方法可以限制查询重写，如章节 Unnesting of Nested Subqueries 中所述。 :

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

如上所述，您可以使用 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