Oracle DETERMINISTIC函数在CONNECT BY LEVEL查询返回异常值原因

这个异常返回是Oracle对DETERMINISTIC确定性函数的缓存优化，和CONNECT BY层级查询的执行逻辑冲突导致的优化器边界问题。

• 首先明确DETERMINISTIC关键字的作用：Oracle默认声明为DETERMINISTIC的函数满足「入参完全一致时，任何调用场景、任何调用时机下返回结果都恒定不变」的前提，因此会对这类函数的调用结果做缓存，后续遇到相同入参时直接读取缓存值，不会重复执行函数逻辑，以此提升执行效率。
• 未加DETERMINISTIC的函数不会触发这类缓存优化，CONNECT BY生成每一行时都会实时计算当前行的LEVEL伪列值作为入参执行函数，因此能正确返回1到1000的连续序列。
• 加了DETERMINISTIC之后，优化器会对CONNECT BY查询做批量处理优化：你测试所用的Oracle 18环境下，批量大小为100行。每个批次处理时，仅会在批次起始位置计算一次LEVEL值，调用函数得到结果后存入缓存，直接把这个缓存值复用到整个批次的所有行，不会在批次内逐行重新计算LEVEL、重新执行函数。

你观察到的返回值规律正好对应这个批量逻辑：

• 第1个批次起始点计算得到LEVEL=1，返回值1被缓存，复用100行，因此前100行全是1
• 第2个批次起始点的LEVEL计算出现偏移，得到值101，返回值101被缓存复用100行
• 后续每个批次的起始LEVEL都延续这个偏移量，最终就出现了1、101、202……909各重复100次的异常结果。

这个问题的本质是优化器没有正确识别LEVEL这类和行上下文强绑定的伪列的特性：伪列的值会随当前处理的行动态变化，不满足确定性函数“相同入参返回恒定结果”的缓存前提，优化器错误对这类场景应用了确定性函数缓存，最终导致结果不符合预期。
如果需要在CONNECT BY查询中调用传入LEVEL、ROWNUM、CONNECT_BY_ISLEAF这类上下文相关伪列的自定义函数，不要给函数加DETERMINISTIC声明，也可以通过添加优化器hint禁用对应的函数缓存优化，避免结果异常。

内容的提问来源于stack exchange，提问作者User1974