oracle SQL sample 随机抽样查询

创建测试表

SQL> create table t1 (x int);

Table created.

插入10000行数据
SQL> begin
  2  for i in 1..10000 loop
  3  insert into t1 values (i);
  4  end loop;
  5  end;
  6  /


PL/SQL procedure successfully completed.

收集表的统计信息
SQL> analyze table t1 compute statistics;


Table analyzed.

查看表中高水位以下的块的数量
SQL> select blocks from user_tables where table_name='T1';


    BLOCKS
----------
        20


SQL> select count(*) from t1;


  COUNT(*)
----------
     10000

随机抽取表中10%的数据
SQL> select count(*) from t1 sample (10);


  COUNT(*)
----------
       965


SQL> select count(*) from t1 sample (10);


  COUNT(*)
----------
      1024


SQL> select count(*) from t1 sample (10);


  COUNT(*)
----------
       981


SQL> select count(*) from t1 sample (10);


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

       949

每次取样差不多为1000行左右