代码之家 › 专栏 › 技术社区 › Xavier Dury

为什么dbms_random.value在图形查询(connect by)中返回相同的值?

random oracle sql

Xavier Dury · 技术社区 · 8 年前

在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到1000.000.000.000(以及更多)。有时该查询返回正确(如果它立即选择了一个没有保留的随机值),或者给出内存不足错误,因为它总是使用相同的值 dbms_随机值 .

3 回复 | 直到 8 年前

wolÏi 8 年前

你的评论“…我想避免并发问题”让我想到。

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

Alex Poole 8 年前

可以在子查询中移动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个值中可能有重复的值),所以您要么需要一个更大的范围,这也很昂贵,尽管不需要这么多的随机调用S:

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,而不是始终在间隙搜索中包含整个可能的值范围。这也将填补空白,而不是扩大使用范围,这可能有用,也可能不有用。(我不确定“我不需要那些数字是连续的”是否意味着它们应该不是连续的,或者没关系)。

除非您特别需要业务来填补空白,并且要使分配的值不连续,否则,我只需要使用一个序列并忽略空白。

Xavier Dury 8 年前

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

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;