CBO是如何计算range谓词的选择率

0
0
0
1. 云栖社区>
2. 博客>
3. 正文

## CBO是如何计算range谓词的选择率

10月22日，由北极中亦安图陈宏义老师在“DBA+上海群”进行了一次关于“CBO是如何计算range谓词的选择率”的线上主题分享。小编特别整理出其中精华内容，供大家学习交流。

COL >= val谓词的选择率（无直方图）

((high_value - val)/(high_value - low_value)) * A4NULLS

A4NULLS为非空率

A4NULLS = (NUM_ROWS - NUM_NULLS)/NUM_ROWS

drop table t1;

create table t1 (id number);

declare

vid number;

begin

for i in 1..1000 loop

if mod(i,30) = 0 then

vid := null;

else

vid :=i;

end if;

insert into t1 values(vid);

end loop;

end;

exec dbms_stats.gather_table_stats(null,'T1');

col high_value for a20

col low_value for a20

select num_rows from user_tables where table_name = 'T1';

NUM_ROWS

----------

1000

select high_value,low_value,num_nulls,histogram from user_tab_columns where table_name='T1' and column_name='ID';

HIGH_VALUE LOW_VALUE NUM_NULLS HISTOGRAM

-------------------- ------------------------------ ---------- ---------------

C20B C102 33 NONE

drop table t1;

create table t1 (id number);

declare

vid number;

begin

for i in 1..1000 loop

if mod(i,30) = 0 then

vid := null;

else

vid :=i;

end if;

insert into t1 values(vid);

end loop;

end;

exec dbms_stats.gather_table_stats(null,'T1');

col high_value for a20

col low_value for a20

select num_rows from user_tables where table_name = 'T1';

NUM_ROWS

----------

1000

select high_value,low_value,num_nulls,histogram from user_tab_columns where table_name='T1' and column_name='ID';

HIGH_VALUE LOW_VALUE NUM_NULLS HISTOGRAM

-------------------- ------------------------------ ---------- ---------------

C20B C102 33 NONE

HIGH_VALUE , LOW_VALUE 可以用dbms_stats.conver_raw_value转换成可以读懂的数字，字符，日期。

NUM_ROWS:1000

LOW_VALUE:1

HIGH_VALUE:1000

NUM_NULLS:33

HISTOGRAM: NONE

A4NULLS=(1000-33)/1000=0.967

Selectivity=(1000-700+1)/(1000-1)*0.967=.291358358

Cardinality = 1000*.291358358 = 291.358358

SQLT中的函数名为：get_internal_value

Create or replace FUNCTION get_internal_value (p_value IN VARCHAR2)

RETURN VARCHAR2

IS

temp_n NUMBER := 0;

BEGIN

FOR i IN 1..15

LOOP

temp_n := temp_n + POWER(256, 15 - i) * ASCII(SUBSTR(RPAD(p_value, 15, CHR(0)), i, 1));

END LOOP;

EXCEPTION

WHEN OTHERS THEN

RETURN p_value;

END get_internal_value;

Sample,

------------------------

SQL> select get_internal_value('AAAAA') from dual;

GET_INTERNAL_VALUE('AAAAA')

--------------------------------------------------------------------------------

338822822454670000000000000000000000

CBO在算>,< between这类谓词时用的就是这个数字。

drop table t1;

drop table t2;

create table t1(id number primary key,cdate varchar2(20));

create table t2(id number primary key,cdate varchar2(20));

create index t1_cdate on t1(cdate);

declare

s date;

begin

s := to_date('2015-03-23 00:00:00','yyyy-mm-dd hh24:mi:ss');

for i in 1..10000 loop

insert into t1 values(i,to_char(s,'yyyy-mm-dd hh24:mi:ss'));

insert into t2 values(i,to_char(s,'yyyy-mm-dd hh24:mi:ss'));

s := s+0.1;

end loop;

commit;

end;

/

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

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

alter session set statistics_level=all;

select * from t1,t2 where t1.id = t2.id and

t1.cdate between '2015-06-23 00:00:00'

and '2015-09-21 23:59:59'

and t2.cdate between '2015-09-14 14:24:00' and '2015-09-15 14:24:00';

select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

T2表通谓词t2.cdate between '2015-09-14 14:24:00' and '2015-09-15 14:24:00'的过滤，得到11行数据，而CBO却估算成910行.

'2015-06-23 00:00:00' = 260592297225015000000000000000000000

'2015-09-21 23:59:59' = 260592297225015000000000000000000000

1. 收集直方图；

2. 将varchar2型改成date型。

+ 关注