ORACLE SAMPLE block

浏览数：31 / 时间：2015年06月12日

最近发现一个有意思的语法，采样扫描，这种语法适用的场合是那种非常大的表，半天查不出来就可以用，或是一些非重要的统计功能，只想知道一个大概。按采样扫描的维度不一样，有两种语法：

sample 按记录采样

sample block 按数据块采样

如果不知道什么是数据块那你很可能听不懂。

SQL> drop table test purge;

SQL> create table test as select * from dba_objects;

sample([0.000001,100])

SQL> select count(*) from test;
COUNT(*)
----------
74105
SQL> select count(*) from test sample(10);
COUNT(*)
----------
7434
SQL> select count(*) from test sample(20);
COUNT(*)
----------
14869
SQL> select count(*) from test sample(50);
COUNT(*)
----------
37210
SQL> select count(*) from test sample(80);
COUNT(*)
----------
59505
SQL> select count(*) from test sample(99);
COUNT(*)
----------

73303

sample block([0.000001,100])

SQL> select count(*) from test sample block(10);
COUNT(*)
----------
8830
SQL> select count(*) from test sample block(20);
COUNT(*)
----------
10456
SQL> select count(*) from test sample block(50);
COUNT(*)
----------
47723
SQL> select count(*) from test sample block(80);
COUNT(*)
----------
62941
SQL> select count(*) from test sample block(99);
COUNT(*)
----------

73232

官方文档：

sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.
BLOCK
BLOCK instructs Oracle to perform random block sampling instead of random row sampling.
sample_percent
sample_percent is a number specifying the percentage of the total row or block count to be included in the sample. The value must be in the range .000001 to (but not including) 100.
Restrictions on Sampling During Queries
You can specify SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.
When you specify SAMPLE, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.
--------------------------------------------------------------------------------
Caution:
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
--------------------------------------------------------------------------------