计算查询条件是or区间时候的selectivity和Cardinality

最新推荐文章于 2019-09-21 10:00:00 发布

转载最新推荐文章于 2019-09-21 10:00:00 发布 · 118 阅读

本文详细解析了SQL查询中使用OR运算符时，Oracle如何计算选择率（Cardinality），并通过实例展示了选择率计算的过程及结果验证。

-------------------创建测试表
create table t1(
v1,
n1,
n2
)
as
select
to_char(mod(rownum,20)),
rownum,
mod(rownum,20)
from
all_objects
where
rownum <= 3000
;

------------------收集统计信息
begin
  dbms_stats.gather_table_stats(ownname       => 'scott',
                              tabname       => 't1',
                              no_invalidate => FALSE,
                              estimate_percent => 100,
                              method_opt    => 'for all  columns size 1',
                              cascade       => true);
end;
/

----------------查询，观察Cardinality的值
SQL> select count(*) from t1 where n2>2 or n2<=2;

  COUNT(*)
----------
   3000

执行计划
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 |    3 |    6 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE |    |    1 |    3 |          |       |
|*  2 | TABLE ACCESS FULL| T1 |  2733 |  8199 |    6 (0)| 00:00:01 |
---------------------------------------------------------------------------
Cardinality是2733，看看怎么算出来的。
首先我们肯定知道ORALCE估算的是不对的，正确的应该是3000才对。
看看ORACLE的算法。

The selectivity of (predicate1 OR predicate2)
= selectivity of (predicate1) + selectivity of (predicate2) - selectivity of (predicate1 AND predicate2)

其中：
selectivity of (predicate1) =(high_value – limit) / (high_value – low_value)=(19-2)/（19-0)=0.894736842
selectivity of (predicate2) = (high_value – limit) / (high_value – low_value) + 1/num_distinct=(2-0)/(19-0)+1/20=0.155263158
注意第二个谓词由于是个闭区间，因此需要增加1/num_distinct部分，而第一部分是个开区间，就不需要了

因此
The selectivity of (predicate1 OR predicate2)
=(0.894736842+0.155263158)-(0.894736842*0.155263158)
=0.911080332

Cardinality=num_rows*selectivity=2733

SQL> select 3000*0.911080332 from dual;

3000*0.911080332
----------------
      2733.241

跟oracle计算出来的是吻合的

如果采用是绑定变量，每个谓词的选择率固定为5%。
The selectivity of (predicate1 OR predicate2)
= selectivity of (predicate1) + selectivity of (predicate2) - selectivity of (predicate1 AND predicate2)
=5%+5%-5%*5%=9.75%

验证下：
SQL> explain plan for
  2  select count(*) from t1 where n2>:1 or n2<=:2;

已解释。

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 |    3 |    7  (15)| 00:00:01 |
| 1 |  SORT AGGREGATE |    |    1 |    3 |          |       |
|*  2 | TABLE ACCESS FULL| T1 | 293 | 879 |    7  (15)| 00:00:01 |
---------------------------------------------------------------------------

Cardinality=num_rows*selectivity=3000*0.0975=292.5~293
吻合
SQL> select 3000*0.0975 from dual;

3000*0.0975
-----------
   292.5