Oracle SQL Tuning_选择率与基数公式02_单表选择率_没有直方图_在Range之外(out of range)

基本概念

Predicate:  查询中所有的或部分的where子句
Range:      表列中最大值和最小值的范围
NumRows:    表的行数(DBA_TABLES.NUM_ROWS)
Card:       Cardinality 满足一个谓词的行数
Sel:        满足需求的行数/可用的总行数
Orig Card:  在用谓词过滤前的行数
Comp Card:  满足谓词的行数
Join Card:  Join操作返回的行数
Join Sel:   Join Card/笛卡尔join返回的行数
RS:         执行计划中对表或索引等操作所输出的行数
NDV:        列上Ｄistinct值的个数(dba_tab_columns.num_distinct)
NNulls:     列上NULL值的个数(dba_tab_columns.num_nulls)
A4Nulls:    NULLS调整
BucketSize: 一个直方图bucket里的行数
Density:    密度dba_tab_columns.density

1.Equality predicate (“col = val”) and val is > high_value and value is < (2 * high_value - low_value)

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = (1 / NDV) * A4Nulls * (1- ((val - high_value) / (high_value - low_value)))
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (1)

explain plan for select object_id from t1 where object_id = '100000';
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     5 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=100000)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = (1 / NDV) * A4Nulls * (1- ((val - high_value) / (high_value - low_value)))
    = (1 / 86290) * 1 * (1- ((100000 - 87488) / (87488 - 2)))
    = 9.9314E-06
Comp_Card = round(86290 * 9.9314E-06) = 1

2.Equality predicate (“col = val”) and val is > (2 * low_value - high_value) and value is < low_value

A4Nulls = (Orig_Card - NNulls) / Orig_Card
Sel = (1 / NDV) * A4Nulls * (1 - ((low_value - val) / (high_value - low_value)))
Comp_Card = Orig_Card * Sel

查看执行计划  Rows (1)

explain plan for select object_id from t1 where object_id = '1';
select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     5 |   278   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     5 |   278   (1)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=1)

查询统计信息

set feed on
col table_name for a30
col column_name for a30
set line 300
set pages 300
select a.table_name,b.column_name,a.num_rows,b.num_distinct,b.num_nulls,b.density,b.low_value,b.high_value,b.histogram 
from user_tables a,user_tab_columns b
where a.table_name=b.table_name
and a.table_name='T1' and b.coLumn_name=upper('&colname');
Enter value for colname: OBJECT_ID

TABLE_NAME        COLUMN_NAME         NUM_ROWS NUM_DISTINCT  NUM_NULLS    DENSITY LOW_VALUE            HIGH_VALUE          HISTOGRAM
----------------- ----------------- ---------- ------------ ---------- ---------- -------------------- ------------------- ---------------
T1                OBJECT_ID              86290        86290          0 .000011589 C103                 C3094B59            NONE

LOW_VALUE(最小值) 和 HIGH_VALUE(最大值) 值是RAW类型，可以用 utl_raw 包的 cast_to_number 函数进行类型转换。

SQL> select utl_raw.cast_to_number('C103') LOW_VALUE, utl_raw.cast_to_number('C3094B59') HIGH_VALUE from dual;

 LOW_VALUE HIGH_VALUE
---------- ----------
         2      87488

选择率和基数的计算过程

A4Nulls = (Orig_Card - NNulls) / Orig_Card
        = (NUM_ROWS - NUM_NULLS) / NUM_ROWS
        = (86290 - 0) / 86290
        = 1
Sel = (1 / NDV) * A4Nulls * (1 - ((low_value - val) / (high_value - low_value)))
    = (1 / 86290) * 1 * (1- ((2 - 1) / (87488 - 2)))
    = .000011589
Comp_Card = round(86290 * .000011589) = 1