oracle快速预估行数——sample

   声明:本站部分内容来自互联网,如若本站内容侵犯了原著者的合法权益,可联系我们进行处理。

14

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.

  1. sample子句查询部分数据而不是整个数据
  2. sample(percent)查询随机行,sample block(percent)查询的随机块
  3. 0.000001 <=percent<100
  4. 预估值不一定等于实际值
  5. 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相当于标记了访问方式