sample clause的概念
sample_clause
The sample_clause lets you instruct the database to select from a random sample of data from the table, rather than from the entire table.
BLOCK
BLOCK instructs the database to attempt to perform random block sampling instead of random row sampling.
Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.
For sample_percent, specify the percentage of the total row or block count to be included in the sample. The value must be in the range .000001to, but not including, 100. This percentage indicates the probability of each row, or each cluster of rows in the case of block sampling, being selected as part of the sample. It does not mean that the database will retrieve exactly sample_percent of the rows of table.
The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results.
SEED seed_value Specify this clause to instruct the database to attempt to return the same sample from one execution to the next. The seed_value must be an integer between 0 and 4294967295. If you omit this clause, then the resulting sample will change from one execution to the next.
- sample子句查询部分数据而不是整个数据
- sample(percent)查询随机行,sample block(percent)查询的随机块
- 0.000001 <=percent<100
- 预估值不一定等于实际值
- seed相当于一个标记,比如seed为1,那么下次执行seed(1)时还是以第一次seed(1)的方式预估。如果不加seed,那么每次执行的值几乎都不一样。
sample测试
SQL> select count(*) from ash_tmp ;COUNT(*)----------4905SQL> select count(*)*10 from ash_tmp sample (10);COUNT(*)*10-----------4890SQL> select count(*)*2 from ash_tmp sample (50);COUNT(*)*2----------4840--并不是sample越高越准确,只是相对来说percent越高越准确SQL> /COUNT(*)*2----------4996--每次执行值都不同SQL> select count(*) from ash_tmp sample (99.999);COUNT(*)----------4905SQL> select count(*) from ash_tmp sample (100);select count(*) from ash_tmp sample (100)*ERROR at line 1:ORA-30562: SAMPLE percentage must be in the range [0.000001,100]
sample block测试
SQL> select count(*)*10 from ash_tmp sample block(10);COUNT(*)*10-----------0SQL> /COUNT(*)*10-----------0SQL> /COUNT(*)*10-----------12980COUNT(*)*10-----------22870--percent为10时,与实际差距较大,多次读到空块SQL> select count(*)*2 from ash_tmp sample block(50);
COUNT(*)*2----------5508SQL> /COUNT(*)*2----------4454
SQL> /COUNT(*)*2----------1882
percent为50时较准确,但是也有偏差较大的时候
seed测试
SQL> select count(*)*2 from ash_tmp sample block(50) seed(1);COUNT(*)*2----------4792SQL> /COUNT(*)*2----------4792SQL> select count(*)*2 from ash_tmp sample block(50) seed(2);COUNT(*)*2----------6318SQL> select count(*)*2 from ash_tmp sample block(50) seed(1);COUNT(*)*2----------4792SQL> select count(*)*10 from ash_tmp sample (10) seed(99);COUNT(*)*10-----------4600SQL> select count(*)*10 from ash_tmp sample (10) seed(100);COUNT(*)*10-----------4680SQL> select count(*)*10 from ash_tmp sample (10) seed(99);COUNT(*)*10-----------4600SQL> select count(*)*10 from ash_tmp sample (10) seed(100);COUNT(*)*10-----------4680
--seed相当于标记了访问方式










