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

最新推荐文章于 2024-07-01 02:23:37 发布

夜归人_

最新推荐文章于 2024-07-01 02:23:37 发布

阅读量387

点赞数

分类专栏： MSQL优化文章标签：无直方图选择率 10053

本文链接：https://blog.csdn.net/night_person_/article/details/46711049

版权

MSQL优化专栏收录该内容

6 篇文章 1 订阅

订阅专栏

案例一无直方图 range 内 = 的选择率

选择率=1/NDV×非空率

非空率=(表行数-NUM_NULLS)/表行数

col column_name format a10;
col NUM_DISTINCT format 999;
col low_value format a10;
col high_value format a10;
col num_nulls format 999;
col HISTOGRAM format a10;

drop table test;
create table test(n1 number, c1 varchar2(20));
begin 
   for i in  1..100 loop
   if mod(i,9)=0 then
      insert into test values(null,null);
   else
      insert into test(n1,c1) values(i,i);
   end if;
   end loop;
   commit;
 end;
 /

exec dbms_stats.gather_table_stats(null,'test');
select column_name,num_distinct,low_value,high_value,num_nulls,HISTOGRAM  from user_tab_columns where table_name='TEST' and COLUMN_NAME='N1';

COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE NUM_NULLS HISTOGRAM
---------- ------------ ---------- ---------- --------- ----------
N1                   89 C102       C202              11 NONE

--以下SQL转换oracle内部结构看最大最小值
SQL> var b1 number;                                                                                                          
SQL> exec dbms_stats.convert_raw_value(hextoraw('&1'),:b1);                                                                  
Enter value for 1: C102                                                                                                      
PL/SQL procedure successfully completed.
SQL> print  b1;                                                                                                              
        B1
----------
         1

SQL> exec dbms_stats.convert_raw_value(hextoraw('&1'),:b1);                                                                  
Enter value for 1: C202                                                                                                     
PL/SQL procedure successfully completed.
SQL> print b1;                                                                                                               
        B1
----------
        100

COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE NUM_NULLS HISTOGRAM
---------- ------------ ---------- ---------- --------- ----------
N1                   89 C102(1)      C202(100)             11 NONE

		
表里边的总数：
SQL> select num_rows from user_tables where table_name='TEST';                                                               

  NUM_ROWS
----------
       100
	   

select * from test where n1=80;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

选择率=1/NDV×非空率
非空率=(表行数-NUM_NULLS)/表行数

选择率= 1/89*(100-11)/100 = 0.01
基数：总行数*选择率=100×0.01=1

结论 基数和Rows都一样

案例二无直方图 range 外 = 的选择率

selectivity(range内[=])/(2High-low-high)=x/(2high-low-绑定直)

select * from  test where N1=102;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     5 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

selectivity(range内=)/(2High-low-high)=x/(2high-low-绑定直)
x=0.01*(2*100-1-102)/(2*100-1-100)=.00979798
基数=.00979798*100=.979798 =1  和rows一样

如果 给定的值 >2high-low  或者 <2low-high   则给一个非常小的值

准备数据：

drop table t1;
create table t1(n1 number, c1 varchar2(20));
begin 
   for i in  1..2134 loop
   if mod(i,9)=0 then
      insert into t1 values(null,null);
   else
      insert into t1(n1,c1) values(trunc(dbms_random.value(1,98),0),dbms_random.string('U',20));
   end if;
   end loop
   commit;
 end;
 /

 
 收集数据
 exec dbms_stats.gather_table_stats(null,'t1');

 
 //查看数据分布
SQL> select column_name,num_distinct,low_value,high_value,num_nulls,HISTOGRAM  from user_tab_columns where table_name='T1' and COLUMN_NAME='N1';  
COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE NUM_NULLS HISTOGRAM
---------- ------------ ---------- ---------- --------- ----------
N1                   97 C102(1)       C162(97)             237 NONE

//总行数 
 SQL> select num_rows from user_tables where table_name='T1';                                                                 

  NUM_ROWS
----------
      2134

案例三无直方图 range 内 > 的选择率

Selectivity=((high_value-当前绑定值)/(high_value-low_value))*非空率

select * from t1 where n1>60;
非空率=(2134-237)/2134 = .888940956
Selectivity=(97-60)/(97-1)*.888940956 = .34261266
基数：总行数*选择率=2134*.34261266 =  731.135416
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   731 | 15351 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   731 | 15351 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

案例四无直方图 range 内 < 的选择率

Selectivity=((当前绑定值-low_value)/(high_value-low_value))*非空率

 select * from  t1 where n1<20;
非空率=(2134-237)/2134 = .888940956
Selectivity=(20-1)/(97-1)*.888940956 =  .175936231
基数：总行数*选择率=2134*.175936231 =   375.447917
 --------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   375 |  7875 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   375 |  7875 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

案例五无直方图 >= (in-Range) 选择率

Selectivity=((high_value-当前绑定值)/(high_value-low_value)+1/ndv)*非空率

select * from  t1 where n1<=20; 
非空率=(2134-237)/2134 = .888940956
Selectivity=((20-1)/(97-1)+1/97)*.888940956 =  .185100571
基数：总行数*选择率=2134*.185100571 =   395.004619
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   395 |  8295 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   395 |  8295 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------

实例六 between (in-Range) 选择率

Selectivity=((valu1-value2)/(high_value-low_value)+2*1/ndv)*非空率

select * from  t1 where n1 between 20 and 50;
非空率=(2134-237)/2134 = .888940956
Selectivity=((50-20)/(97-1)+2*1/97)*.888940956 =  .296122728
基数：总行数*选择率=2134*.296122728 = 631.925902
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   632 | 13272 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |   632 | 13272 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

案例七 > (out-of-Range) 选择率

转换成 = (out-of-range) 来处理

= (out-of-range) 来处理的公式
selectivity/(2High-low-high)=x/(2high-low-绑定直)
其中 selectivity=1/div*非空率

explain plan for select * from t1 where n1>150;    这两句的结果是一样的
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   189 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     9 |   189 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

explain plan for select * from t1 where n1=150;    这两句的结果是一样的
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     9 |   189 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     9 |   189 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

=  (out-of-range) 来处理 的公式  
selectivity/(2High-low-high)=x/(2high-low-绑定直)
其中 selectivity=1/div*非空率
1/97×(2134-237)/2134/(2*97-1-97)*(2*97-1-150)  =.004104861
基数=.004104861*2134= 8.75977337
特殊情况 如果  绑定值 远远大于 97(最大值) 则 rows =1

实例八 < (out-of-Range) 选择率

< (out-of-Range) 选择率

<span style="background-color: rgb(153, 255, 255);">a.   涵盖整个表 , n1< b , b>max_value
</span>explain plan for select * from t1 where n1<300;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1897 | 39837 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  1897 | 39837 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------
返回： all_rows-nuu_rows = 2134-237 =  1897 

b.   比 最小值还小   转换成  out-of-range =  的情况
 
<font style=""><span style=""><span style="background-color: rgb(102, 204, 204);"><span style="color:#99ffff;"> x/（绑定值 -(2low-high))= selectivity/(low-(2low-high))</span>
</span></span></font> x=selectivity/(low-(2low-high))*(绑定值 -(2low-high))
  =(1/97)*(2134-237)/2134/(1-(2*1-97))*(-1-(2*1-97))=0.00897341600724001
cardinality=  0.00897341600724001*2134=19.1492697594502
explain plan for select * from t1 where n1<-1;
select * from table(dbms_xplan.display);
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    19 |   399 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    19 |   399 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

<span style="background-color: rgb(102, 204, 204);">c. 比  2low -high 还小  则会给一个非常小的值  
</span>select * from t1 where n1<-200;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    21 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    21 |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------

案例九扩张

between 扩展   60 到 200    一边超出了 其实 下边这两句等效
select * from t1 where n1 >=60;
select * from t1  where n1 between 60 and 200;



NOT A  选择率
1-A 的选择率


COL is NULL
num_nulls/num_rows

col is not null
1- COL is NULL

补充 10053

alter session set evnet '10053 trace name context forever , level 1'
select * from t1 where n1=80;
alter session set evnet '10053 trace name context off'

打开10053
搜： Best::
 

nestloop 的内层循环的a-row 是 外层数乘出来的