oracle cbo选择率,CBO是如何计算range谓词的选择率 - ORACLE - dbaplus社群：围绕Data、Blockchain、AiOps的企业级专业社群。技术大咖、原创干货，每天精...

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

这个案例里ID列的最小值是1,最大值是1000。

NUM_ROWS:1000

LOW_VALUE:1

HIGH_VALUE:1000

NUM_NULLS:33

HISTOGRAM: NONE

根据上面的公式，可以算出选择率：

谓词:ID>=700

A4NULLS=(1000-33)/1000=0.967

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

Cardinality = 1000*.291358358 = 291.358358

最后得出的cardinality是291

四舍五入，最小值是1

用执行计划来验证一下：

数字可以简单地加减，但是字符串就没有办法了。

字符串是没有办法进行加、减运算的，Oracle会把它转换成一个内部的数字 ，这个转换算法在SQLT中公布出来。

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;

RETURN TO_CHAR(ROUND(temp_n, -21));

EXCEPTION

WHEN OTHERS THEN

RETURN p_value;

END get_internal_value;

这是用pl/sql实现的转换字符串为数字的算法。CBO在算的时候，一定不是用pl/sql是用C

例如，

Sample,

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

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

GET_INTERNAL_VALUE('AAAAA')

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

338822822454670000000000000000000000

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

这个算法，不具有唯一性，经常会不同的字符串得到相同的internal_value， 如果因为这个问题导致了数据分布倾斜，Oracle会为这个列收集直方图，并用endpoint_actaul_value来校准

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'));

这是sample的代码

从上面的执行计划中可以看出，T1表通过谓词t1.cdate between '2015-06-23 00:00:00' and '2015-09-21 23:59:59'的过滤得到910行数据，而CBO估算它1行。

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

由于这个估算偏差导致错误地使用了T1作为nested loop的驱动表。

我们看一下'2015-06-23 00:00:00'，'2015-09-21 23:59:59'这两个字符串，化成的internal value是什么。

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

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

由于这两个值是相同的，所以这个条件，就相当于=了。所以计算出的cardinality就非常低。

解决方案：

1. 收集直方图；

2. 将varchar2型改成date型。

收集直方图后，执行计划变为：

另外，dba_histograms.endpoint_actaul_value这个列就是干这个用的。校准 internal value重复的情况。