Oracle SQL:如果在嵌入式视图中显示SYS_GUID()的行为，该如何理解? [英] Oracle SQL: Understanding the behavior of SYS_GUID() when present in an inline view?
问题描述
这里是有问题的示例SQL； SQL应该在任何Oracle DBMS上运行(我正在运行11.2.0.2.0).
Here is the example SQL in question; The SQL should run on any Oracle DBMS (I'm running 11.2.0.2.0).
请注意，尽管是从内联view/with子句中构建的，但结果集中的UUID值有何不同(一个为898，另一个为899).在下面的更多内容中，您将看到DBMS_RANDOM.RANDOM()如何没有这种副作用.
Note how the UUID values are different (one has 898 the other has 899) in the resultset despite being built from within the inline view/with clause. Further below you can see how DBMS_RANDOM.RANDOM() does not have this side effect.
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:
Even more interesting is I can change the behavior / stabilize sys_guid by including calls to DBMS_RANDOM.RANDOM:
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 That Stabilizes SYS_GUID: http://sqlfiddle.com/#!4/d41d8/29409
SQL小提琴，显示了奇怪的SYS_GUID行为: http://sqlfiddle.com/#!4/d41d8/29411
SQL Fiddle That shows the odd SYS_GUID behavior: http://sqlfiddle.com/#!4/d41d8/29411
推荐答案
文档给出了一个原因，说明为什么您可能会看到差异(强调我的意思):
The documentation gives a reason as to why you may see a discrepancy (emphasis mine):
警告:
由于SQL是一种声明性语言，而不是命令性(或过程性)语言，因此您不知道SQL语句调用的函数将运行多少次-即使该函数是用编写的PL/SQL，一种命令式语言. 如果您的应用程序要求某个函数执行一定次数，请不要从SQL语句调用该函数.请改用光标.
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语句中调用一个函数的次数:它可能取决于版本，环境，访问路径以及其他因素.
Basically, Oracle doesn't specify how many times a function will be called inside a sql statement: it may be dependent upon the release, the environment, the access path among other factors.
但是，有一些方法可以限制查询重写，如一章中所述子查询的数量:
However, there are ways to limit query rewrite as explained in the chapter Unnesting of Nested Subqueries:
子查询取消嵌套，将其嵌套并合并到包含该子查询的语句主体中，以便优化器在评估访问路径和联接时将它们一起考虑.优化器可以嵌套大多数子查询，有一些例外情况.这些例外包括层次结构子查询和包含ROWNUM伪列的子查询，集合运算符之一，嵌套的聚合函数或对不是子查询的直接外部查询块的查询块的相关引用.
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 伪-column以防止Oracle取消嵌套子查询:
As explained above, you can use ROWNUM pseudo-column to prevent Oracle from unnesting a subquery:
SQL> WITH data AS (SELECT SYS_GUID() uuid FROM DUAL WHERE ROWNUM >= 1)
2 SELECT uuid, uuid FROM data;
UUID UUID
-------------------------------- --------------------------------
1ADF387E847F472494A869B033C2661A 1ADF387E847F472494A869B033C2661A
这篇关于Oracle SQL:如果在嵌入式视图中显示SYS_GUID()的行为，该如何理解?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
