oracle 统计数据存放于:
user_tables, user_tab_columns, user_tab_cols(including virtual columns), user_tab_histograms.
计算selectivity / cardnality 非常重要。
提供user_tab_columns.low_value和user_tab_columns.high_value的转换函数。 字符串做范围查找时,需要根据编码转换成数字,提供此函数cbo_char_value。
[@more@]自定义函数:
VALUE_TO_DATE
VALUE_TO_NUMBER
VALUE_TO_VARCHAR2
cbo_char_value
SQL> desc t1
?? ????? ??
----------------------------------------- -------- ----------------------------
ID NUMBER
SMALL_VC VARCHAR2(4000)
PADDING VARCHAR2(50)
SQL> select num_rows, blocks, empty_blocks from user_tables where table_name='T1
';
NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
100000 1049 0
SQL> select column_name, num_distinct, density, num_nulls, low_value, high_value
2 from user_tab_columns
3 where table_name='T1';
Col NUM_DISTINCT DENSITY NUM_NULLS
------------ ------------ ---------- ----------
LOW_VALUE
----------------------------------------------------------------
HIGH_VALUE
----------------------------------------------------------------
ID 100000 .00001 0
C102
C30B
SMALL_VC 100000 .00001 0
61616161756A75646D616B64
7A7A7A777479666B646E756D
PADDING 1 1 0
7820202020202020202020202020202020202020202020202020202020202020
7820202020202020202020202020202020202020202020202020202020202020
SQL> select value_to_varchar2(low_value), value_to_varchar2(high_value)
2 from user_tab_columns
3 where table_name='T1' and column_name='SMALL_VC';
VALUE_TO_VARCHAR2(LOW_VALUE)
--------------------------------------------------------------------------------
VALUE_TO_VARCHAR2(HIGH_VALUE)
--------------------------------------------------------------------------------
aaaaujudmakd
zzzwtyfkdnum
SQL> l
1 select 1/100000+
2 (cbo_char_value('wp')-cbo_char_value('wo'))/
3 (cbo_char_value('zzzwtyfkdnum')-cbo_char_value('aaaaujudmakd'))
4* from dual
SQL> /
1/100000+(CBO_CHAR_VALUE('WP')
------------------------------
.00016564
CARDINALITY = .00016564 * 100000(NUM_ROWS)= 16.564 = 17 (和execution plan 计算结果相同
)
SQL> select
2 *
3 from
4 t1
5 where small_vc like 'wo%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=102 Card=17 Bytes=
1156)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=102 Card=17 Bytes=1156)
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/87149/viewspace-907657/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/87149/viewspace-907657/