CBO 统计值存放和字符串的selectivity 计算-优快云博客

本文介绍Oracle数据库中统计信息的存储位置及如何使用特定函数进行数据转换，包括字符串范围查找时的编码转换，并通过实例展示了如何计算选择性和基数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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/