oracle實驗記錄 (oracle單表選擇率與基數計算(2))

在查詢中使用區間謂詞選擇率和基數card計算

先開始,>=,<= 這幾種無邊界情況(所謂無邊界就是一側沒有邊界 )
SQL> create table t5 (a int);

表已建立。

SQL> declare
2 begin
3 for i in 1..10000 loop
4 insert into t5 values(i);
5 end loop;
6 commit;
7 end;
8 /

PL/SQL 過程已成功完成。

SQL> execute dbms_stats.gather_table_stats('SYS','T5');

PL/SQL 過程已成功完成。
SQL> select table_name,density,num_nulls,num_distinct from user_tab_col_statisti
cs where table_name='T5';

TABLE_NAME DENSITY NUM_NULLS NUM_DISTINCT
------------------------------ ---------- ---------- ------------
T5 .0001 0 10000
SQL> select num_rows from dba_tables where table_name='T5';

NUM_ROWS
----------
10000

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select count(*) from t5 where a>2;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   | 9999 | 29997 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">2)

SQL> select count(*) from t5 where a>=2;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   | 10000 | 30000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">=2)

SQL> select count(*) from t5 where a<2;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<2)

SQL> select count(*) from t5 where a<=2;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     2 |     6 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<=2)

>=，<=是閉區間， >

分析a>2 是一個無邊開區間(所謂無邊界就是一側沒有邊界，a>10 and a<100就是一個有邊界的兩邊開區間)
a>2選擇率=(high_value – limit) / (high_value – low_value)

SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
.99989999
card=num_rows*選擇率
SQL> select 10000*.99989999 from dual;

10000*.99989999
---------------
9998.9999～～～～～～～～～～～～～～～可以看到10gr2是向上舍入(ceil)

trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 9999 Computed: 9999.00 Non Adjusted: 9999.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142429
      Resp_io: 6.00 Resp_cpu: 2142429
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 9999.00 Bytes: 0

分析a>= 是一個無邊界，閉區間 ,閉區間的特點就是必須包含閉合值的行（2）
選擇率=(high_value – limit) / (high_value – low_value) + 1/num_distinct
SQL> select (10000-2)/(10000-1)+1/10000 from dual;

(10000-2)/(10000-1)+1/10000
---------------------------
.99999999

card=10000*.99999999=10000

SQL> select ceil(10000*.99999999) from dual;

CEIL(10000*.99999999)
---------------------
                10000
trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 10000 Computed: 10000.00 Non Adjusted: 10000.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142429
      Resp_io: 6.00 Resp_cpu: 2142429
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 10000.00 Bytes: 0

分析a<2無邊界開區間
選擇率=(limit – low_value) / (high_value – low_value)**********
card=num_rows*選擇率
SQL> select (2-1)/(10000-1) from dual
2 ;

(2-1)/(10000-1)
---------------
.00010001

SQL> select 10000*.00010001 from dual;

10000*.00010001
---------------
1.0001

trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142429
      Resp_io: 6.00 Resp_cpu: 2142429
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 1.00 Bytes: 0

分析a<=2 無邊界，閉區間需要將閉合值的行加進來
選擇率=(limit – low_value) / (high_value – low_value)+1/num_distinct

card=num_rows*選擇率
SQL> select (2-1)/(10000-1)+1/10000 from dual
2 ;

(2-1)/(10000-1)+1/10000
-----------------------
.00020001

SQL> select 10000*.00020001 from dual
2 ;

10000*.00020001
---------------
2.0001

trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 2 Computed: 2.00 Non Adjusted: 2.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142429
      Resp_io: 6.00 Resp_cpu: 2142429
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 2.00 Bytes: 0

*******************************
接下來看between and, a>x and a=x and a=x and a<=y ,a>x and a<=y 這幾種型別有邊界情況

SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select count(*) from t5 where a between 2 and 10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |    10 |    30 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<=10 AND "A">=2)

SQL> select count(*) from t5 where a>2 and a<10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     8 |    24 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<10 AND "A">2)

SQL> select count(*) from t5 where a>=2 and a<10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<10 AND "A">=2)

SQL> select count(*) from t5 where a>2 and a<=10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<=10 AND "A">2)

SQL> select count(*) from t5 where a>=2 and a<=10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |    10 |    30 |     6   (0)| 00:00:01 |~~~~~~~~~看到與 between and card一樣
---------------------------------------------------------------------------

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

2 - filter("A"<=10 AND "A">=2)

分析 a between 2 and 10 就等於>= and <=
選擇率=（hight_limit-low_limit）/(hight_value-low_value) +1/num_distinct+1/num_distinct(因為是2個閉區間所以2個閉合值行都要加進來)
card=num_rows*選擇率
SQL> select (10-2)/(10000-1)+1/10000+1/10000 from dual;

(10-2)/(10000-1)+1/10000+1/10000
--------------------------------
.00100008

SQL> select 10000*.00100008 from dual;

10000*.00100008
---------------
10.0008

between and 等同於〉=and<= 所以 a>=2 and a<=10 card也等於10 計算方法也是一樣的

trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 10 Computed: 10.00 Non Adjusted: 10.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142929
      Resp_io: 6.00 Resp_cpu: 2142929
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 10.00 Bytes: 0

分析a>2 and a<10 有邊界，兩邊都開區間
選擇率=（hight_limit-low_limit）/(hight_value-low_value)
card=num_rows*選擇率
SQL> select (10-2)/(10000-1) from dual;

(10-2)/(10000-1)
----------------
.00080008

SQL> select 10000*((10-2)/(10000-1)) from dual;

10000*((10-2)/(10000-1))
------------------------
              8.00080008
trace
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 8 Computed: 8.00 Non Adjusted: 8.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142879
      Resp_io: 6.00 Resp_cpu: 2142879
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 8.00 Bytes: 0

分析 a>=2 and a<10 ，a>2 and a<=10 這兩個都是有邊界，半開區間（有一閉合的，所以要加上閉合值的行(閉合值分別是 2和10)）
所以他們公式相同~ card相同

選擇率=（hight_limit-low_limit）/(hight_value-low_value)+1/num_distinct
card=num_rows*選擇率

SQL> select (10-2)/(10000-1)+1/10000 from dual;

(10-2)/(10000-1)+1/10000
------------------------
.00090008

SQL> select 10000*((10-2)/(10000-1)+1/10000) from dual;

10000*((10-2)/(10000-1)+1/10000)
--------------------------------
                      9.00080008
SINGLE TABLE ACCESS PATH
Column (#1): A(NUMBER)
    AvgLen: 4.00 NDV: 10000 Nulls: 0 Density: 1.0000e-004 Min: 1 Max: 10000
Table: T5 Alias: T5
    Card: Original: 10000 Rounded: 9 Computed: 9.00 Non Adjusted: 9.00
Access Path: TableScan
    Cost: 6.14 Resp: 6.14 Degree: 0
      Cost_io: 6.00 Cost_cpu: 2142929
      Resp_io: 6.00 Resp_cpu: 2142929
Best:: AccessPath: TableScan
         Cost: 6.14 Degree: 1 Resp: 6.14 Card: 9.00 Bytes: 0

針對bind 變數情況

SQL> variable a number
SQL> variable b number
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select count(*) from t5 where a>:a;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |   500 | 1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">TO_NUMBER(:A))

SQL> select count(*) from t5 where a>=:a;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |   500 | 1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">=TO_NUMBER(:A))

SQL> select count(*) from t5 where a<:a>

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |   500 | 1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"

SQL> select count(*) from t5 where a<=:a;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |   500 | 1500 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<=TO_NUMBER(:A))

SQL> select count(*) from t5 where a between :a and :b;

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

SQL> select count(*) from t5 where a>:a and a<:b>

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A) 3 - filter("A">TO_NUMBER(:A) AND "A"

SQL> select count(*) from t5 where a>=:a and a<=:b;

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B))
3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

SQL> select count(*) from t5 where a>:a and a<=:b;

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A) 3 - filter("A">TO_NUMBER(:A) AND "A"<=TO_NUMBER(:B))

SQL> select count(*) from t5 where a>=:a and a<:b>

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |    25 |    75 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A) 3 - filter("A">=TO_NUMBER(:A) AND "A"

分析以上可以看出規則很簡單

只是按是否具有邊界計算的
無邊界（,>=,<=）選擇率為5%
card=num_rows*選擇率
SQL> select 10000*.05 from dual
2 ;

10000*.05
----------
500
有邊界（between and,> and = and<= ,> and<= ,>= and選擇率=5%*5%
card=num_rows*選擇率
SQL> select 10000*.0025 from dual
2 ;

10000*.0025
-----------
25

其它情況

首先是超過邊界情況
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

會話已更改。

SQL> select count(*) from t5 where a>10001;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">10001)

分析超過邊界的時候oracle 按邊字面值來計算
選擇率=1/num_distinct
card=10000*1/10000=1

SQL> select count(*) from t5 where a>2 and a<=10002;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   | 9999 | 29997 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">2 AND "A"<=10002)

這個oracle檢測出半部分超出邊界
選擇率=（hight_limit-low_limit）/(hight_value-low_value)+1/num_distinct oracle並沒有用這個公式
SQL> select (10002-2)/(10000-1)+1/10000 from dual;

(10002-2)/(10000-1)+1/10000
---------------------------
1.00020001~~~~~~~~~~~~~~~~~~~~~~沒這樣計算返回的card當然也不對(10000*1.00020001)

而是隻用了左邊 a>2的來計算
(high_value – limit) / (high_value – low_value)

SQL> select 10000*((10000-2)/(10000-1)) from dual;

10000*((10000-2)/(10000-1))
---------------------------
9998.9999 CARD*選擇率

SQL> select count(*) from t5 where a>=2 and a<=10002;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   | 10000 | 30000 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">=2 AND "A"<=10002)

這個一樣只用了左邊右邊超過邊界沒算
a>=2
(high_value – limit) / (high_value – low_value)+1/num_distinct
SQL> select 10000*((10000-2)/(10000-1)+1/10000) from dual;

10000*((10000-2)/(10000-1)+1/10000)
-----------------------------------
9999.9999~~~~~~~~~~~~~~~~~card

SQL> select count(*) from t5 where a>=-3 and a<=10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |    10 |    30 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<=10 AND "A">=(-3))

oracle 發現左邊邊界太小
只計算了 a<=10

選擇率=(limit – low_value) / (high_value – low_value)+1/num_distinct

SQL> select 10000*((10-1)/(10000-1)+1/10000) from dual;

10000*((10-1)/(10000-1)+1/10000)
--------------------------------
10.0009001

SQL> select count(*) from t5 where a>=-3 and a<10;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     9 |    27 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A"<10 AND "A">=(-3))

同上邊一樣發現邊界不對，只用了
a<10
選擇率=(limit – low_value) / (high_value – low_value)
SQL> select 10000*(10-1)/(10000-1) from dual;

10000*(10-1)/(10000-1)
----------------------
9.00090009

SQL> select count(*) from t5 where a>10001 and a<=10002;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   |     1 |     3 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

2 - filter("A">10001 AND "A"<=10002)

當超出了邊界 2邊那麼 card=num_rows*1/num_distinct=1

另一種情況 or

SQL> select count(*) from t5 where a>3 or a<10 ;

執行計劃
----------------------------------------------------------
Plan hash value: 1231860717

---------------------------------------------------------------------------
| 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| T5   | 9998 | 29994 |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

or的選擇率公式

a>3+a<10-(a>3*a<10)
a>3選擇率=(high_value – limit) / (high_value – low_value)=.99979998
a<10選擇率=(limit – low_value) / (high_value – low_value)=.00020002
a>3*a<10=.00019998

SQL> select (10000-3)/(10000-1) from dual;

(10000-3)/(10000-1)
-------------------
.99979998

SQL> select (3-1)/(10000-1) from dual;

(3-1)/(10000-1)
---------------
.00020002

SQL> select .99979998*.00020002 from dual;

.99979998*.00020002
-------------------
.00019998

SQL> select .99979998+.00020002-.00019998 from dual;

.99979998+.00020002-.00019998
-----------------------------
.99980002

SQL> select 10000*.99980002 from dual;

10000*.99980002
---------------
9998.0002~~~~~~~~~~~~~~~~~~~card

3個or 是這樣的
sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)

如果要都是 bind
5% (column < :b1) +5% (column > :b2) -0.25% (column < :b1 and column > :b2) =9.75%

混合類~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~簡單看下下面型別
SQL> variable a number
SQL> set autotrace traceonly explain
SQL> select count(*) from t5 where a>2 and a<:a>

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |   500 | 1500 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(2 3 - filter("A"2)

SQL> select count(*) from t5 where a>100 and a<:a>

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |   495 | 1485 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(100 3 - filter("A"100)

SQL> select count(*) from t5 where a>:a and a<100;

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |     5 |    15 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A)<100)
3 - filter("A"<100 AND "A">TO_NUMBER(:A))

SQL> select count(*) from t5 where a>:a and a<=100;

執行計劃
----------------------------------------------------------
Plan hash value: 4287340780

----------------------------------------------------------------------------
| Id | Operation           | Name | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     3 |     6   (0)| 00:00:01 |
|   1 | SORT AGGREGATE     |      |     1 |     3 |            |          |
|* 2 |   FILTER            |      |       |       |            |          |
|* 3 |    TABLE ACCESS FULL| T5   |     5 |    15 |     6   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

2 - filter(TO_NUMBER(:A)<100)
3 - filter("A"<=100 AND "A">TO_NUMBER(:A))

上面的計算採用的是雙謂詞計算
選擇率=謂詞1 選擇率+謂詞2選擇率
例
a>2 and a<:a>A>2無邊界開區間選擇率 =(high_value – limit) / (high_value – low_value)=.99989999
SQL> select (10000-2)/(10000-1) from dual;

(10000-2)/(10000-1)
-------------------
.99989999
a<:a>所以a>2 and a<:a>SQL> select 0.05*.99989999 from dual;

0.05*.99989999
--------------
.049995

CARD=NUM_ROWS*選擇率
SQL> select 10000*.049995 from dual;

10000*.049995
-------------
499.95

a>:a and a<=100
A<=100是一個無邊界閉區間選擇率=(limit-low_value) / (high_value – low_value)+1/num_distinct=.01000099

SQL> select (100-1)/(10000-1)+1/10000 from dual;

(100-1)/(10000-1)+1/10000
-------------------------
.01000099

a>:a選擇率為5%

a>:a and a<=100 選擇率=.00050005
SQL> select 0.05*((100-1)/(10000-1)+1/10000) from dual;

0.05*((100-1)/(10000-1)+1/10000)
--------------------------------
.00050005
card=下面值

SQL> select 10000*(0.05*((100-1)/(10000-1)+1/10000)) from dual;

10000*(0.05*((100-1)/(10000-1)+1/10000))
----------------------------------------
5.00049505

SQL>

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-614518/，如需轉載，請註明出處，否則將追究法律責任。

oracle實驗記錄 (oracle單表選擇率與基數計算(2))

相關文章