为什么确定性函数返回意想不到的数字在CONNECT BY LEVEL查询?

Question

Test #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

那个确定性函数没有像预期的那样工作。与test #1不同，它不返回序号。如果我们查看完整的结果集，它返回:

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

db<在小提琴>

---

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

Answer 1

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

使这些类别的函数具有DETERMINISTIC是良好的编程实践:

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

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

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

另一方面，这似乎是18C中的一个错误，因为相同的查询在19c和21c中工作良好，结果正确。