MSQL优化基础(height_blanced直方图选择率)

原创 2015年07月07日 16:06:50

NDV>255, 并且数据分布不均衡,则倾向搜集 height_blanced直方图

 

准备数据

drop table t1;
create table t1(n1 number);
declare
min1 number;
max1 number;
begin
	for i in 1..600 loop
		min1 := trunc(dbms_random.value(1,200),0);
		max1 := trunc(dbms_random.value(min1,200),0);
		for j in min1..max1 loop
			insert into t1 values(i);
		end loop;
		commit;
	end loop;
	
	for i in 1..200 loop
		insert into t1 values(40);
		insert into t1 values(80);
	end loop;
end;
/

begin
	for i in 1..800 loop
		insert into t1 values(40);
		insert into t1 values(81);
	end loop;
	commit;
end;
/


begin
	for i in 1..200 loop
		insert into t1 values(39);
	end loop;
end;
/

exec dbms_stats.gather_table_stats(null,'T1',method_opt=>'for all columns size 254');

select num_rows from user_tables where table_name='T1';
  NUM_ROWS
----------
     31696  
select num_distinct,num_nulls, low_value,high_value,HISTOGRAM from user_tab_columns where table_name='T1'
NUM_DISTINCT  NUM_NULLS LOW_VALUE       HIGH_VALUE      HISTOGRAM
------------ ---------- --------------- --------------- ---------------
         536          0 C102            C207            HEIGHT BALANCED


 

 

案例1 : in-range =   var 经常出现的值

选择率 = (val所在的buckets数/bucket总数)*非空率

select endpoint_value,endpoint_number,( endpoint_number - lag(endpoint_number,1) over(order by endpoint_number))  buckets from user_histograms where table_name='T1'
ENDPOINT_VALUE ENDPOINT_NUMBER    BUCKETS
-------------- --------------- ----------
...
 40              27                10
...


select * from t1 where n1 = 40;  --1129 rows selected.
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1248 |  3744 |    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1248 |  3744 |    14   (8)| 00:00:01 |
--------------------------------------------------------------------------

选择率 = (val所在的buckets数/bucket总数)*非空率 = 10/254 =.039370079
cardinality = .039370079*31696 =1247.87402  

和rows一样


案例1 : in-range =   var 经常出现的值案例2 : in-range =   var 不出现的值

密度 = (bucket总数 - 所有的常见值的bucket数)/bucket总数/(NDV - 常见值的个数)

选择率 = 密度 * 非空率

select endpoint_value,endpoint_number,( endpoint_number - lag(endpoint_number,1) over(order by endpoint_number))  buckets from user_histograms where table_name='T1'
ENDPOINT_VALUE ENDPOINT_NUMBER    BUCKETS
-------------- --------------- ----------
...
 40              27                10
...

594             250          1
597             252          2
599             253          1
...



select * from t1 where n1=598 (596,595 之类的 出来都是 157 )
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    14 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |   157 |   471 |    14   (8)| 00:00:01 |
--------------------------------------------------------------------------

select sum(buckets),count(*) from(
select endpoint_value,endpoint_number,( endpoint_number - lag(endpoint_number,1) over(order by endpoint_number))  buckets from user_histograms where table_name='T1'
) where buckets > 1;
SUM(BUCKETS)   COUNT(*)
------------ ----------
          51         19

密度 =  (254 - 51)/254/(536 - 19) =.001545866
选择率=密度 *1 
cardinality=.001545866*31696=48.9977687   和 rows不一样。
测试环境 10.2 


 

 案例3 : in-range  <

选择率=(Count(Buckets) / Bucket总数)*非空率

select * from t1 where n1<256
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 14475 | 43425 |    14   (8)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 14475 | 43425 |    14   (8)| 00:00:01 |
--------------------------------------------------------------------------

select sum(buckets),count(*) from(
select endpoint_value,endpoint_number,( endpoint_number - lag(endpoint_number,1) over(order by endpoint_number))  buckets from user_histograms where table_name='T1'
) where endpoint_value<=256;

SUM(BUCKETS)   COUNT(*)
------------ ----------
         116         96
		 
selectivity= (Count(Buckets) / Bucket总数)*非空率 =116/254 = .456692913
cardinality=.456692913*31696=14475.3386
和rows一样

 

相关文章推荐

MSQL优化基础(frequence直方图选择率)

123123

MSQL优化基础(无直方图选择率)

案例一 无直方图  range 内 = 的选择率 选择率=1/NDV×非空率 非空率=(表行数-NUM_NULLS)/表行数 col column_name format a10; col NUM_D...

(1)oracle单表选择率(selectivity)——计算执行计划的基数

CBO优化器是基于对当前经过特定测试的数据集中预期的行比率估计来计算基数的。此处的行数之比是一个数值,称为选择率(selectivity)。得到选择率之后,将其与输入行数进行简单相乘既可得到基数。 ...

MSQL优化基础(正确取到执行计划)

1.前台 pl/sql F5 , explain plan for 方式得到的计划,如果有绑定变量时候不准, 而且是预估值不是实际情况的 2.对于执行过的SQL:     如果我们想获取该语句的实际执...

SQL优化【基础06】 - 统计信息(直方图)

前言:从10开始默认优化器就是CBO模型,RBO已经靠边站了,CBO

msql优化

一、MyISAM优缺点 优点: 1.能够快速查询唯一键 2.支持全文索引 3.选择count(*)的速度很快 4.磁盘空间占用小 缺点: 1.表级别锁定,如果写入操作的总时间占到5%以上...

msql优化--索引优化

关于数据库优化指标主要为:响应时间,扫描行数,返回行数。对于响应时间,主要体现在服务器级别,服务器性能,网络响应速度等;索引主要为了减少扫描行数;根据limit来限制返回行数。       ...

图像处理理论(一)——直方图、二值化、滤波基础

直方图、二值化、滤波基础
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:MSQL优化基础(height_blanced直方图选择率)
举报原因:
原因补充:

(最多只允许输入30个字)