浅析Oracle Selectivity

选择率=满足条件返回记录行/基表总记录行


1.基本选择率基数







常规单表单谓词（Selectivity）=
                                                 (# rows satisfying a condition)/(Total # of rows)

选择率Selectivity=基本选择率*校正后选择率
                       =（num_predicate_keys）1/num_distinct_keys    *     (num_rows-null/num_rows)

Card基数=Selectivity*Num_Rows

-----------------------------------------------------------------
--ORACLE 优化器
选择率Selectivity=1/num_distinct_keys 

oracle优化器选择率适用于pk场景,对于重复值较多，数据分布不均衡场景，优化器计算"失真"


如:t表包含1200行的表(100 null值),包含12个月的数据,查询其中1月的数据
select * from t where month=12;
      其选择率
                    = 1/12 * (1200-100)/1200=1/12*11/12=7.64%
       基数= 7.64%*1200=92
                                                                           

注意:
where c1 >10
   and c1<20
 



2.选择率变换：
如果id列重复值很多公式变换为：

选择率Selectivity=基本选择率*校正后选择率
                       =（num_keys）*1/num_distinct_keys    *         (num_rows-null/num_rows)

Card基数=选择率Selectivity*Num_Rows


3.测试验证
--null不计入索引成本

1)获取表列信息
OWNER      TABLE_NAME       COLUMN_NAME      NUM_DISTINCT  NUM_NULLS   NUM_ROWS    DENSITY LOW_VALUE  HIGH_VALUE HISTOGRAM       LAST_ANALY
---------- ---------------- ---------------- ------------ ---------- ---------- ---------- ---------- ---------- --------------- ----------
CBO        T_CBO            ID                      99993          3     100000 .000010001 C106       C30B       NONE            2020-06-17
                                                                                                                                  11:36:25

表总数100000行,null=3,distinct=99993,重复值:5
2)获取查询重复key及数量

SQL> select id,count(*) from t_cbo group by id  having count(*) >=2 order by count(*) desc;

        ID   COUNT(*)
---------- ----------
         5          5
                    3
3)id=5

select * from t_cbo where id=5

Selectivity=5*(1/100)*(100-3)/100
              =.0485
Cards=round(Selectivity*num_rows)=5

4）10053

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T_CBO  Alias: T_CBO
    #Rows: 100000  #Blks:  244  AvgRowLen:  5.00
Index Stats::
  Index: IDX_ID_CBO  Col#: 1
    LVLS: 1  #LB: 222  #DK: 99993  LB/K: 1.00  DB/K: 1.00  CLUF: 152.00
Access path analysis for T_CBO
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for T_CBO[T_CBO] 
  Table: T_CBO  Alias: T_CBO
    Card: Original: 100000.000000  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  Access Path: TableScan
    Cost:  68.46  Resp: 68.46  Degree: 0
      Cost_io: 68.00  Cost_cpu: 21737631
      Resp_io: 68.00  Resp_cpu: 21737631
  Access Path: index (index (FFS))
    Index: IDX_ID_CBO
    resc_io: 62.00  resc_cpu: 18580450
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  62.39  Resp: 62.39  Degree: 1
      Cost_io: 62.00  Cost_cpu: 18580450
      Resp_io: 62.00  Resp_cpu: 18580450
  Access Path: index (AllEqRange)
    Index: IDX_ID_CBO
    resc_io: 1.00  resc_cpu: 8371
    ix_sel: 0.000010  ix_sel_with_filters: 0.000010 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IDX_ID_CBO
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 1.00  Bytes: 0

***************************************

OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:  T_CBO[T_CBO]#0
***********************
Best so far:  Table#: 0  cost: 1.0002  card: 1.0000  bytes: 5
...........
Final cost for query block SEL$1 (#0) - All Rows Plan:
  Best join order: 1
  Cost: 1.0002  Degree: 1  Card: 1.0000  Bytes: 5
  Resc: 1.0002  Resc_io: 1.0000  Resc_cpu: 8371
  Resp: 1.0002  Resp_io: 1.0000  Resc_cpu: 8371

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation         | Name      | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |           |       |       |     1 |           |
| 1   |  INDEX RANGE SCAN | IDX_ID_CBO|     1 |     5 |     1 |  00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
1 - access("ID"=5)

5)Cards计算
表selectity=5*1/100000*(100000-3)/100000
card=selec

索引selectivity=1+222*(5*1/(100000-3))+152*表selectivity
                    ~=1