为什么在使用dbms_random.value函数时需要在这个Oracle SQL查询中包含rownum字段

Question

我从一些表中随机抽取了一个样本，注意到根据我如何编写查询，它不起作用。显然，我并没有使用all_tab_columns，我只是提供了一个可以在普通实例上工作的示例(9.2.0.8(

为什么这样做？

select * from (
    select 
        floor(dbms_random.value(0,1000)) as randomval
    from 
        all_tab_columns where rownum < 10000
) where randomval > 200 and randomval < 300;

但这并没有返回任何结果。

select * from (
    select 
        floor(dbms_random.value(0,1000)) as randomval
    from 
        all_tab_columns 
) where randomval > 200 and randomval < 300;

rownum<10000在查询中完成？

编辑：澄清了什么不起作用。

编辑：添加了9.2.0.8版

Answer 1

我没有明确的答案，但我有一个理论。。。

我的猜测是，您的第二个查询正在优化为：

select * 
  from all_tab_columns 
 where floor(dbms_random.value(0,1000))> 200 
   and floor(dbms_random.value(0,1000)) < 300;

并且在内联视图中以某种方式具有CCD_ 1标准阻碍了优化。

这也可以解释为什么我们中的一些人(包括我(看不到你描述的问题，因为我们使用的是不同版本的Oracle，查询对我们的优化也不同

编辑

在谷歌上搜索了一下之后，我发现了这个AskTom问题，它似乎是相关的。Tom Kyte的回答以这句话结尾：

从SQL调用函数时，最好不要依赖于频率函数被调用，按什么顺序调用，或者其他什么方式调用。简而言之，假设没有什么请记住，SQL重写开始生效，我们将重写您的SQL一直。不要依赖的副作用

Answer 2

我认为@Peter有所了解，但需要更多的解释。

在Oracle中，函数可以是DETERMINISTIC，也可以不是。这意味着，当使用相同的参数调用函数时，该函数将返回相同的值。因此，sqrt()是确定性的；dbms_random()不是。其他数据库使用STABLE和VOLATILE这两个类别。

如果非要我猜测的话，Oracle引擎足够聪明，可以知道以下查询应该返回行：

select * from (
    select 
        dbms_random.value(0,1000) as randomval
    from 
        all_tab_columns 
) where randomval > 200 and randomval < 300;

然而，我怀疑它是因为floor()而被绊倒的。也就是说，编译器检测到一个"稳定"函数，所以它只查找以前的值。并且，它使用缓存的值，从而短路多次调用函数。

如果这是正确的，那么上面的查询将返回行。如果是这样的话，那么我会把数据库的这个"功能"称为bug——不过，可能有一位数据库优化工程师会为这个非常有用的功能辩护。

Answer 3

这两个查询都在Oracle 11g R2中工作。ROWNUM是Oracle中的伪列，如果您感兴趣，可以阅读它。