Oracle 使用多次引用的sample() 的通用表表达式不会产生正确的结果。

 drop table lh_a; 
 create table lh_a as 
 (select level nro, cast(' ' as char(100)) dummy from dual connect by level < 1000000); 
 create unique index lh_a_ind on lh_a(nro) pctfree 0; 

 with 
 b as (select nro from lh_a sample (1)) 
 select * 
 from 
 b b1 
 left outer join b b2 on b2.nro = b1.nro; 

 create table b_materialized as select nro from lh_a sample (1); 
 select * from b_materialized b1 
 left outer join b_materialized b2 on b2.nro = b1.nro; 

SQL> create table lh_a as
  2   (select level nro, cast(' ' as char(100)) dummy from dual connect by level < 1000000);

Table created.

mcdonac@db122
SQL> create unique index lh_a_ind on lh_a(nro) pctfree 0;

Index created.

mcdonac@db122
SQL> 
mcdonac@db122
SQL> set autotrace traceonly stat
mcdonac@db122
SQL> 
mcdonac@db122
SQL> with
  2   b as (select nro from lh_a sample (1))
  3  select *
  4  from b b1
  5   left outer join b b2 on b2.nro = b1.nro;

10003 rows selected.


Statistics
----------------------------------------------------------
  10  recursive calls
  10  db block gets
      10709  consistent gets
      17263  physical reads
       1044  redo size
     200115  bytes sent via SQL*Net to client
       7934  bytes received via SQL*Net from client
 668  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
      10003  rows processed

mcdonac@db122
SQL> 
mcdonac@db122
SQL> create table b_materialized as select nro from lh_a sample (1);

Table created.

mcdonac@db122
SQL>   select * from b_materialized b1
  2   left outer join b_materialized b2 on b2.nro = b1.nro;

10097 rows selected.


Statistics
----------------------------------------------------------
   5  recursive calls
  11  db block gets
 727  consistent gets
  16  physical reads
       1048  redo size
     241929  bytes sent via SQL*Net to client
       8011  bytes received via SQL*Net from client
 675  SQL*Net roundtrips to/from client
   0  sorts (memory)
   0  sorts (disk)
      10097  rows processed

mcdonac@db122
SQL> 

SQL> select count(*) from lh_a sample (1);

  COUNT(*)
----------
      9993

mcdonac@db122
SQL> /

  COUNT(*)
----------
     10073

mcdonac@db122
SQL> /

  COUNT(*)
----------
     10103

mcdonac@db122
SQL> /

  COUNT(*)
----------
      9976