number/date/varchar2类型字段做range scan 的selectivity计算

oracle按照standard formula计算cost，当对于number/date类型字段做range scan, oracle都可以得到比较准确的selectivity. （之所以说是比较准确而非正确，因为虽然我们的数字、日期实际是离散的，但oracle optimizer认为是连续的，所以我们想查询2002-12-30－2003-01-05在2000-2004年（共1827天）中的selectivity，期望是7/1827，但oracle并不会这样计算）。

[@more@]

若以number类型存储年月日yyyymmdd 计算结果为：standard fomula:

selectivity=(required range) / (high value - low value) + 2/num_distinct

=(20030105-20021230)/(20041231-20000101) + 2/1827

= 8875 / 41130 +2/1827

=0.215779 + 0.001095

=0.216874

若以 char/varchar2类型存储年月日yyyymmdd ，并以此字段做range scan. oracle 也需要把字符串转为numeric数字. 再按照fomula计算selectivity.

字符串转成数字的方法以function实现。

rem
rem Script: template.sql
rem Author: Jonathan Lewis
rem Dated: Sep 2004
rem Purpose: Example for "Cost Based Oracle"
rem
rem Last tested:
rem 10.1.0.4
rem 9.2.0.6
rem 8.1.7.4
rem
rem Create a function that takes an input string
rem and returns the numeric value that the optimizer
rem would use to represent it.
rem
rem Inputs:
rem The string
rem The size limit if the string is to be
rem treated as a char() rather than varchar2()
rem
rem Special note:
rem This function will not work for multi-byte character sets.
rem

create or replace function cbo_char_value (
i_instring in varchar2,
i_charsize in number default 0
) return number
as
m_size number;
m_vc varchar2(15);
m_n number := 0;
begin
if i_charsize = 0 then
m_size := length(i_instring) ;
else
m_size := i_charsize ;
end if;

m_vc := rpad(rpad(i_instring,m_size,' '),15,chr(0));

for i in 1..15 loop

dbms_output.put(ascii(substr(m_vc,i,1)));
dbms_output.put(chr(9));
dbms_output.put_Line(
to_char(
power(256,15-i) * ascii(substr(m_vc,i,1)),
'999,999,999,999,999,999,999,999,999,999,999,999'
)
);
m_n := m_n + power(256,15-i) * ascii(substr(m_vc,i,1));
end loop;
return m_n;
end;
/

rem
rem Example of use:
rem

select
round(
1827 * (
2/1827 +
(cbo_char_value('20030105') - cbo_char_value('20021230')) /
(cbo_char_value('20041231') - cbo_char_value('20000101'))
),2
) cardinality
from
dual
;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/87149/viewspace-907288/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/87149/viewspace-907288/