为什么dbms_random.value在图形查询(连接方式)中返回相同的值

Question

在Oracle 11.2.0.4.0上，当我运行以下查询时，每行都会得到不同的结果：

select r.n from (
select trunc(dbms_random.value(1, 100)) n from dual
) r
connect by level < 100; -- returns random values

但是，一旦我在联接或子查询中使用了获得的随机值，那么每一行都会从dbms_random.value:中获得相同的值

select r.n, (select r.n from dual) from (
select trunc(dbms_random.value(1, 100)) n from dual
) r
connect by level < 100; -- returns the same value each time

是否可以让第二个查询为每一行返回随机值？

更新

我的例子可能过于简化了，下面是我要做的：

with reservations(val) as (
select 1 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 8 from dual
)
select * from (
select rnd.val, CONNECT_BY_ISLEAF leaf from (
select trunc(dbms_random.value(1, 10)) val from dual
) rnd
left outer join reservations res on res.val = rnd.val
connect by res.val is not null
)
where leaf = 1;

但预订量可能从1到1.000.000000(甚至更多(。有时，该查询返回正确(如果它立即选择了一个没有保留的随机值(，或者由于它总是尝试使用相同的dbms_random.value值而出现内存不足错误。

Answer 1

你的评论"…我想避免并发问题"让我思考。

你为什么不试着插入一个随机数，注意重复的违规行为，然后重试直到成功？即使是一个非常聪明的查找可用数字的解决方案，也可能在两个单独的会话中得出相同的新数字。因此，只有插入并提交的预订号码才是安全的。

Answer 2

您可以在子查询中移动connect-by子句：

select r.n, (select r.n from dual) from (
select trunc(dbms_random.value(1, 100)) n from dual
connect by level < 100
) r;
N (SELECTR.NFROMDUAL)
---------- -------------------
90                  90
69                  69
15                  15
53                  53
8                   8
3                   3
...

我试图做的是生成一个随机数序列，并在一些表中找到第一个我没有记录的随机数

你可能会做一些类似的事情：

select r.n
from (
select trunc(dbms_random.value(1, 100)) n from dual
connect by level < 100
) r
where not exists (
select id from your_table where id = r.n
)
and rownum = 1;

但它会在检查任何一个之前生成所有100个随机值，这有点浪费；由于你可能在这100个中找不到差距(这100个中可能有重复(，你要么需要更大的范围，这也很昂贵，尽管不需要那么多随机调用：

select min(r.n) over (order by dbms_random.value) as n
from (
select level n from dual
connect by level < 100 -- or entire range of possible values
) r
where not exists (
select id from your_table where id = r.n
)
and rownum = 1;

或者重复一次检查，直到找到匹配项。

另一种方法是有一个所有可能的ID的查找表，其中有一列指示它们是否已使用或可用，可能有位图索引；然后使用它来找到第一个(或任何随机的(自由值。但是，您也必须维护该表，并在使用和发布主表中的ID时进行原子更新，这意味着使事情变得更加复杂并串行化访问——尽管如果您不想使用序列，您可能无论如何都无法避免这种情况。你可能会使用物质化的视图来简化事情。

如果你有相对较少的间隙(并且你真的想重复使用这些间隙(，那么你可能只在指定的范围内搜索间隙，如果没有间隙，就回到序列器。假设您当前使用的值仅在1到1000之间，缺少一些值；你可以在1-100的范围内寻找一个自由值，如果没有，那么使用一个序列来获得1001，而不是总是在差距搜索中包括你的整个可能的值范围。这也将填补空白，而不是扩大使用范围，这可能有用，也可能不有用。(我不确定"我不需要这些数字是连续的"是否意味着它们应该而不是是连续的，或者这无关紧要(。

除非您特别需要填补空白，并且分配的值不连续，否则我只会使用序列并忽略空白。

Answer 3

我设法通过以下查询获得了正确的结果，但我不确定这种方法是否真的可取：

with
reservations(val) as (
select 1 from dual union all
select 3 from dual union all
select 4 from dual union all
select 5 from dual union all
select 8 from dual
),
rand(v) as (
select trunc(dbms_random.value(1, 10)) from dual
),
next_res(v, ok) as (
select v, case when exists (select 1 from reservations r where r.val = rand.v) then 0 else 1 end from rand
),
recursive(i, v, ok) AS (
select 0, 0, 0 from dual
union all
select i + 1, next_res.v, next_res.ok from recursive, next_res where i < 100 /*maxtries*/ and recursive.ok = 0
)
select v from recursive where ok = 1;