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
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; 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A"<=TO_NUMBER(:A)) SQL> select count(*) from t5 where a between :a and :b; 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B)) SQL> select count(*) from t5 where a>:a and a<:b>
執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A) SQL> select count(*) from t5 where a>=:a and a<=:b; 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A)<=TO_NUMBER(:B)) SQL> select count(*) from t5 where a>:a and a<=:b; 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A) 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A) 只是按 是否具有邊界計算的 10000*.05 10000*.0025 其它情況 首先是超過邊界情況 會話已更改。 SQL> select count(*) from t5 where a>10001; 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A">10001) 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A">2 AND "A"<=10002) 這個oracle檢測出 半部分超出邊界 (10002-2)/(10000-1)+1/10000 而是隻用了左邊 a>2的來計算 SQL> select 10000*((10000-2)/(10000-1)) from dual; 10000*((10000-2)/(10000-1)) SQL> select count(*) from t5 where a>=2 and a<=10002; 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A">=2 AND "A"<=10002) 這個一樣 只用了左邊 右邊超過邊界沒算 10000*((10000-2)/(10000-1)+1/10000) 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A"<=10 AND "A">=(-3)) oracle 發現左邊 邊界太小 選擇率=(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) 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A"<10 AND "A">=(-3)) 同上邊一樣 發現邊界不對，只用了 10000*(10-1)/(10000-1) SQL> select count(*) from t5 where a>10001 and a<=10002; 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter("A">10001 AND "A"<=10002) 當超出了 邊界 2邊 那麼 card=num_rows*1/num_distinct=1 SQL> select count(*) from t5 where a>3 or a<10 ; 執行計劃 --------------------------------------------------------------------------- Predicate Information (identified by operation id): or的 選擇率公式 a>3+a<10-(a>3*a<10) SQL> select (10000-3)/(10000-1) from dual; (10000-3)/(10000-1) SQL> select (3-1)/(10000-1) from dual; (3-1)/(10000-1) SQL> select .99979998*.00020002 from dual; .99979998*.00020002 SQL> select .99979998+.00020002-.00019998 from dual; .99979998+.00020002-.00019998 SQL> select 10000*.99980002 from dual; 10000*.99980002 3個or 是這樣的 如果要都是 bind 混合類~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~簡單看下下面型別 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(2 SQL> select count(*) from t5 where a>100 and a<:a>
執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(100 SQL> select count(*) from t5 where a>:a and a<100; 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A)<100) SQL> select count(*) from t5 where a>:a and a<=100; 執行計劃 ---------------------------------------------------------------------------- Predicate Information (identified by operation id): 2 - filter(TO_NUMBER(:A)<100) 上面的 計算採用的是 雙謂詞計算 (10000-2)/(10000-1) 0.05*.99989999 CARD=NUM_ROWS*選擇率 10000*.049995 SQL> select (100-1)/(10000-1)+1/10000 from dual; (100-1)/(10000-1)+1/10000 a>:a選擇率為5% a>:a and a<=100 選擇率=.00050005 0.05*((100-1)/(10000-1)+1/10000) SQL> select 10000*(0.05*((100-1)/(10000-1)+1/10000)) from dual; 10000*(0.05*((100-1)/(10000-1)+1/10000)) SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-614518/，如需轉載，請註明出處，否則將追究法律責任。
----------------------------------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
----------------------------------------------------------
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 |
----------------------------------------------------------------------------
---------------------------------------------------
3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(: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 |
----------------------------------------------------------------------------
---------------------------------------------------
----------------------------------------------------------
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 |
----------------------------------------------------------------------------
---------------------------------------------------
3 - filter("A">=TO_NUMBER(:A) AND "A"<=TO_NUMBER(: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 |
----------------------------------------------------------------------------
---------------------------------------------------
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 |
----------------------------------------------------------------------------
---------------------------------------------------
分析以上可以看出 規則很簡單
無邊界（,>=,<=） 選擇率為5%
card=num_rows*選擇率
SQL> select 10000*.05 from dual
2 ;
----------
500
有邊界 （between and,> and = and<= ,> and<= ,>= and選擇率=5%*5%
card=num_rows*選擇率
SQL> select 10000*.0025 from dual
2 ;
-----------
25
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';
----------------------------------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
分析超過邊界的時候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 |
---------------------------------------------------------------------------
---------------------------------------------------
選擇率=（hight_limit-low_limit）/(hight_value-low_value)+1/num_distinct oracle並沒有用這個公式
SQL> select (10002-2)/(10000-1)+1/10000 from dual;
---------------------------
1.00020001~~~~~~~~~~~~~~~~~~~~~~沒這樣計算 返回的card當然也不對(10000*1.00020001)
(high_value – limit) / (high_value – low_value)
---------------------------
9998.9999 CARD*選擇率
----------------------------------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
a>=2
(high_value – limit) / (high_value – low_value)+1/num_distinct
SQL> select 10000*((10000-2)/(10000-1)+1/10000) from dual;
-----------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
只計算了 a<=10
--------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
a<10
選擇率=(limit – low_value) / (high_value – low_value)
SQL> select 10000*(10-1)/(10000-1) from dual;
----------------------
9.00090009
----------------------------------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
另一種情況 or
----------------------------------------------------------
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 |
---------------------------------------------------------------------------
---------------------------------------------------
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
-------------------
.99979998
---------------
.00020002
-------------------
.00019998
-----------------------------
.99980002
---------------
9998.0002~~~~~~~~~~~~~~~~~~~card
sel(A) + sel(B) + sel(C) – Sel(A)sel(B) – Sel(B)sel(C) – sel(C)sel(A) + Sel(A)Sel(B)Sel(C)
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 |
----------------------------------------------------------------------------
---------------------------------------------------
----------------------------------------------------------
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 |
----------------------------------------------------------------------------
---------------------------------------------------
----------------------------------------------------------
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 |
----------------------------------------------------------------------------
---------------------------------------------------
3 - filter("A"<100 AND "A">TO_NUMBER(: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 | 5 | 15 | 6 (0)| 00:00:01 |
----------------------------------------------------------------------------
---------------------------------------------------
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;
-------------------
.99989999
a<:a>所以a>2 and a<:a>SQL> select 0.05*.99989999 from dual;
--------------
.049995
SQL> select 10000*.049995 from dual;
-------------
499.95
a>:a and a<=100
A<=100是一個無邊界 閉區間 選擇率=(limit-low_value) / (high_value – low_value)+1/num_distinct=.01000099
-------------------------
.01000099
SQL> select 0.05*((100-1)/(10000-1)+1/10000) from dual;
--------------------------------
.00050005
card=下面值
----------------------------------------
5.00049505
相關文章
- oracle實驗記錄 (oracle單表選擇率與基數計算(1))Oracle
- oracle實驗記錄 (oracle單表選擇率與基數計算(3))Oracle
- oracle實驗記錄 （選擇率）Oracle
- oracle實驗記錄 （FTS的cost與基數計算）Oracle
- oracle實驗記錄 （連線選擇率,範圍與null）OracleNull
- oracle實驗記錄 （分割槽表,掃描基數的計算）Oracle
- oracle計算表的記錄數Oracle
- oracle實驗記錄 （oracle consistent gets 計算）Oracle
- 【cbo計算公式】單表選擇率(二)公式
- oracle實驗記錄 （全表掃描COST計算方法）Oracle
- oracle實驗記錄 （計算hash join cost）Oracle
- oracle實驗記錄（logfile基礎操作2）Oracle
- oracle實驗記錄 （storage儲存引數(2)）Oracle
- oracle實驗記錄 (oracle 10G dataguard(2)引數部分)Oracle
- oracle實驗記錄 （基礎,truncate與delete區別實驗）Oracledelete
- oracle實驗記錄 (快取記憶體命中率與成本)Oracle快取記憶體
- oracle實驗記錄 (oracle b*tree index訪問Cost計算(1))OracleIndex
- oracle實驗記錄 （oracle 分析shared pool(2)）Oracle
- oracle實驗記錄 (oracle 詳細分析redo（2）)Oracle
- oracle實驗記錄（INDEX fast full scan 的成本計算）OracleIndexAST
- oracle實驗記錄 （dump undo2）Oracle
- oracle實驗記錄Rman duplicate database 2OracleDatabase
- oracle實驗記錄 （關於表實際大小）Oracle
- oracle實驗記錄 （線上重定義表）Oracle
- oracle實驗記錄 (oracle reset parameter)Oracle
- 選擇率(selectivity)與基數(cardinality)
- oracle實驗記錄 （database_properties與表空間屬性）OracleDatabase
- oracle實驗記錄 （logfile基礎操作）Oracle
- oracle實驗記錄 （flashback）Oracle
- oracle實驗記錄 （OMF）Oracle
- oracle實驗記錄 （NET）Oracle
- oracle實驗記錄 （audit）Oracle
- oracle實驗記錄 （子游標與解析）Oracle
- oracle實驗記錄 (oracle 資料字典)Oracle
- oracle實驗記錄 (oracle 關於instance引數設定)Oracle
- CBO中基數（cardinality）、可選擇率（selectivity）的計算公式公式
- oracle實驗記錄 （cursor_sharing(2)SIMILAR）OracleMILA
- oracle實驗記錄（手動dupliacate database(2)）OracleDatabase