Background information

Oracle will use the Sample syntax for sampling query, such as: select * from AAA sample block(name).

New solution

PolarDB supports sampling queries using the TABLESAMPLE system (parameter) and TABLESAMPLE bernoulli (parameter) methods.

BERNOULLI and the parameters passed in by the SYSTEM sampling method indicate the percentage of the sampling table, which ranges from 0 to 100. The BERNOULLI method scans the entire table and selects or ignores rows with a specified probability. The SYSTEM method performs block layer sampling, and each block has a specified opportunity to be selected. All rows in the selected block are returned. When a small sampling percentage is specified, the SYSTEM method is much faster than the BERNOULLI method, but the former method may return Table sampling with poor randomness due to clustering effect.


canno=> create table a(id int);

canno=> insert into a select generate_series(1,1000000);
INSERT 0 1000000

canno=> select count(1) from a tablesample system(1);
(1 row)

canno=> select count(1) from a tablesample bernoulli(1);
(1 row)