Oracle收集统计信息的一些思考( 二 ) _生活百科

三、采样算法以下是个人的一些娱乐性思考

等比放大，即(采样得到distinct值 / 采样行数) x 总行数。举个例子，假设表有1000行数据，只采样100行， A列有95个不同的值，即count(distinct A) / count(A) = 95% ，那么等比放大很容易推导出1000行数据，有950个A的不同值。但是如果这100行中B列只有2个不同的值，即count(distinct B) / count(B) = 2% ，那么对于1000行的表来讲， B的不同值是不是等于2% * 1000呢？很有可能不是，说不定全表就这两个不同值，例如性别。所以通过等比放大得到的distinct值就不准。这种算法有明显的缺陷。
按增长率估算，即将采样得到的前5%作为一个基数，采样得到的后5%作为一个增长率。（假设采样比例是10%）还是举个例子，假设表有1000行数据，只采样100行，采样的前50行， C列有40个不同值。采样的后50行， C列又多了30个不同值，即总共有70个不同值。那么后面的90%都会保持这个增长速度。则总体的C列不同值为40 + 30 * ((100-5)/5) = 610 。再来看一种情况，假设采样的前50行， D列有2个不同值。采样的后50行， D列多了0个不同值，即不同值总数保持不变。那么后面的90%都会保持这个增长速度。则总体的D列不同值仍为2 。这种方式似乎比等比采样更加合乎实际情况一点。接下来就用python去实现这个算法，看看与oracle的估算差别有多大。以下为python代码。

import randomimport cx_Oracledef func(ins):SAMPLE_PERCENT = 10# 采样比例%sample_size = int(len(ins) * SAMPLE_PERCENT / 100)# 对数据进行采样sample = random.sample(ins, sample_size)head_half_sample = sample[0:int(len(sample)/2)]# 采样数据的前一半head_half_sample_distinct = len(set(head_half_sample))# 采样数据的前一半的distinct值full_sample_distinct = len(set(sample))# 采样数据的全量distinct值tail_half_inc = full_sample_distinct - head_half_sample_distinct# 采样数据的distinct增量estimate_distinct = round(head_half_sample_distinct + tail_half_inc * (100 - SAMPLE_PERCENT/2) / (SAMPLE_PERCENT/2))return estimate_distinctdef test(colname):DATABASE_URL = 'xxxxx'conn = cx_Oracle.connect(DATABASE_URL)curs = conn.cursor()sql = 'select {} from t1'.format(colname)curs.execute(sql)tmpdata = https://www.huyubaike.com/biancheng/[]for i in curs.fetchall():tmpdata.append(i[0])res = func(tmpdata)curs.close()conn.close()return resfor i in ['OBJECT_NAME', 'SUBOBJECT_NAME', 'TIMESTAMP', 'LAST_DDL_TIME', 'CREATED', 'NAMESPACE', 'OBJECT_TYPE','OWNER', 'TEMPORARY', 'DUPLICATED', 'STATUS', 'SHARDED', 'GENERATED', 'SECONDARY', 'SHARING', 'EDITIONABLE','ORACLE_MAINTAINED', 'APPLICATION', 'DEFAULT_COLLATION', 'DATA_OBJECT_ID', 'OBJECT_ID']:print(i, '估算的distinct->', test(i))

运行结果

文章插图
再来跟之前的一个表格进行对比，按增长率的方式估算的distinct值看上去也能接受。

COLUMN_NAME实际的distinct数据库估算的distinctpython估算的distinct------------------------------ -------------------------------------------------------OBJECT_NAME645521030074865SUBOBJECT_NAME10153851210TIMESTAMP258512405641LAST_DDL_TIME249012575536CREATED231212094983NAMESPACE211589OBJECT_TYPE453992OWNER8071176TEMPORARY222DUPLICATED111STATUS222SHARDED111GENERATED222SECONDARY111SHARING444EDITIONABLE223ORACLE_MAINTAINED222APPLICATION111DEFAULT_COLLATION112DATA_OBJECT_ID777857810077752OBJECT_ID145212146100145178

限于时间，测试到此结束。后面有时间再学点统计相关的知识。