sql - 为什么确定性函数在 CONNECT BY LEVEL 查询中返回意外数字？

Question

测试 #1:

我有一个用户定义的函数和一个 CONNECT BY LEVEL 查询:

with function custom_function(p_id in number) return number 
    is
begin
    return p_id;
end;

select 
    custom_function(level)
from 
    dual
connect by level <= 1000

ID
--
 1
 2
 3
 4
 5
 6
 7
 8
 9
10

...
10 rows of 1000

该非确定性函数按预期工作。它返回序列号，类似于未使用该函数。

---

测试 #2:

此测试与第一个相同，只是函数是确定性的:

with function custom_function(p_id in number) return number 
    deterministic is  --this is what I changed
begin
    return p_id;
end;

select 
    custom_function(level) id
from 
    dual
connect by level <= 1000

ID
--
 1
 1
 1
 1
 1
 1
 1
 1
 1
 1
...

10 rows of 1000

确定性函数没有按预期工作。与测试 #1 不同，它不返回序列号。如果我们查看完整的结果集，它会返回:

• 1 x 100
• 101 x 100
• 202 x 100
• 303 x 100
• 404 x 100
• 505 x 100
• 606 x 100
• 707 x 100
• 808 x 100
• 909 x 100

db<>fiddle

---

为什么 DETERMINISTIC 函数在 CONNECT BY LEVEL 查询中返回意外数字？

Answer 1

根据神谕 docs当数据库遇到确定性函数时，它会尽可能尝试使用先前计算的结果，而不是重新执行该函数。在您的情况下，没有使用先前计算结果的范围。

使属于这些类别的函数具有确定性是一种良好的编程习惯:

->WHERE、ORDER BY 或 GROUP BY 子句中使用的函数

->SQL 类型的 MAP 或 ORDER 方法的函数

->帮助确定行是否出现在结果集中或出现在结果集中的位置的函数

附带说明一下，这似乎是 18C 中的错误，因为相同的查询在 19c 和 21c 中运行良好，结果正确。