等值选择率，及其延伸

  1):等值选择率

 

【创建实验表】

DROP TABLE SS PURGE;

CREATE TABLE SS(ID) AS SELECT mod(LEVEL,80) FROM dual CONNECT BY LEVEL<=10000;

 

--收集统计信息
BEGIN
  dbms_stats.gather_table_stats(ownname          => USER,
                                tabname          => 'ss',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size 1');
END;
/

--查看统计信息
SELECT UTC.num_distinct,
       utl_raw.cast_to_number(utc.low_value) AS lval,
       utl_raw.cast_to_number(UTC.high_value) AS hval,
       utc.density,
       utc.num_nulls,
       utc.HISTOGRAM
FROM   user_tab_col_statistics UTC
WHERE  utc.table_name = 'SS';

--80 0 79 0.0125 0

 

--评估基数
DELETE FROM plan_table;
EXPLAIN PLAN FOR SELECT ID FROM SS WHERE ss.Id=1;

SELECT CARDINALITY FROM plan_table WHERE ID=0;
--125 = num_rows * (1/80)

 

--空值引入
UPDATE SS SET ID=NULL WHERE ID BETWEEN 20 AND 30;
COMMIT;

 

--重新收集统计信息

 

--查看统计信息
69 0 79 0.0144927536231884 1375 NONE

 

--评估基数
125

 

SELECT 10000 * (1 / 69 * ((10000 - 1375) / 10000)) FROM dual;
--125

 

  【结论】
 
  等值选择率的计算公式为：
( 1 / num_distinct ) * ( (num_rows - num_nulls) / num_rows )。

 

  下面我们研究下类似等值操作的in和not in操作的选择率计算公式。

 

______________________________________________________________________

 

  2):IN-list选择率

 

【普通基数评估】

 

DELETE FROM plan_table;
EXPLAIN PLAN FOR SELECT ID FROM SS WHERE ss.Id in(1,2,3);

SELECT CARDINALITY FROM plan_table WHERE ID=0;
--375

 

SELECT 10000 * (1 / 69 * ((10000 - 1375) / 10000)) *3 FROM dual;

 

  其实in操作的选择率计算公式还是以等值选择率计算公式为基础，即:

 

( 1 / num_distinct ) * ( (num_rows - num_nulls) / num_rows ) * IN_list_cnt。

 

  但是这里值得注意一点的就是，如果in中包含null，则cbo会将null和普通常量一样
对待，这种做法肯定是错误的。

【NOT IN选择率】

 

DELETE FROM plan_table;
EXPLAIN PLAN FOR SELECT ID FROM SS WHERE ss.Id NOT in(1,2,3);

SELECT CARDINALITY FROM plan_table WHERE ID=0;
6141

 

 

oracle处理not in时是改写为如下等效谓词：

 

WHERE ss.id<>1 AND ss.id<>2 AND ss.id<>3

 

而我们知道对于and关系操作的选择率为：S1*S2*S3...
其中S代表选择率，而1,2,3...分别代表各谓词条件。

 

所以我们只要知道<>操作的选择率计算方法就可以推算
出not IN操作的选择率计算方式：

 

DELETE FROM plan_table;
EXPLAIN PLAN FOR SELECT ID FROM SS WHERE ss.Id<>1;

SELECT CARDINALITY FROM plan_table WHERE ID=0;
8500

 

SELECT (1 - ((1 / 69) * ((10000 - 1375) / 10000)) - 1375 / 10000) * 10000
FROM   dual;
--8500

 

BEGIN
  dbms_stats.set_table_stats(ownname => USER,
                             tabname => 'ss',
                             numrows => 22556688);
  dbms_stats.set_column_stats(ownname => USER,
                              tabname => 'ss',
                              colname => 'id',
                              nullcnt => 589977);
END;
/

DELETE FROM plan_table;
EXPLAIN PLAN FOR SELECT ID FROM SS WHERE ss.Id<>1;

SELECT CARDINALITY FROM plan_table WHERE ID=0;
--21648353

 

SELECT (1 - ((1 / 69) * ((22556688 - 589977) / 22556688)) -
       589977 / 22556688)*22556688
FROM   dual;
--21648352.8695652

 

所以我们得出<>操作的选择率计算公式为：

(1 - ((1 / num_distinct) * ((num_rows - num_nulls) / num_rows)) - num_nulls / num_rows)，
字面解释就是1减去普通等值选择率，再减去空值选择率（in操作就没not IN明智）。

 

而得到NOT IN操作的选择率公式即为：

POWER((1 - ((1 / num_distinct) * ((num_rows - num_nulls) / num_rows)) - num_nulls / num_rows) , in_list_cnt)。