ORACLE SAMPLE Block

Recently found an interesting syntax, sampling scan, the syntax of the application is the kind of very large table, half a day can not be found to use, or some non-important statistical functions, just want to know a ballpark. The dimension scanned by sampling is different, there are two kinds of syntax:

Sample is sampled by record

Sample block is sampled by data block

If you don't know what a data block is, you probably don't understand it.

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 (a);
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

Official documents:

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 all row or block count to being included in the sample. The value must is in the range. 000001 to (and not including).
restrictions on sampling During Queries
You can specify the SAMPLE only in a query, that selects from a single table. Joins is not supported. However, can achieve the same results by using a CREATE TABLE ... As SELECT query to materialize a sample of a underlying table and then rewrite the original query to refer to the newly c reated 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 a supported with this clause.
--------------------------------------------------------------------------------
Caution:
The use of statistically incorrect assumptions if using this feature can leads to incorrect or undesirable results.
--------------------------------------------------------------------------------