計算查詢條件是or區間時候的selectivity和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