oracle分区界限值过大,超出边界的基数估算问题

- cpu costing is off (consider enabling it)

SQL> SELECT COUNT(1) FROM T WHERE ID=2;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |   100 |   300 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=2)

Note

-----

- cpu costing is off (consider enabling it)

SQL> SELECT COUNT(1) FROM T WHERE ID=10;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |   100 |   300 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=10)

Note

-----

- cpu costing is off (consider enabling it)

可以看到和我们预期的结果一致。

但是如果我们使用ID=11或者ID=0进行查询，那么结果将会不同。

SQL> SELECT COUNT(1) FROM T WHERE ID=11;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |    89 |   267 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=11)

Note

-----

- cpu costing is off (consider enabling it)

SQL> SELECT COUNT(1) FROM T WHERE ID=0;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |    89 |   267 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=0)

Note

-----

- cpu costing is off (consider enabling it)

在超出边界的情况下，ORACLE采用直线下降的方法来计算选择度。

我们的范围间隔是10-1=9，ORACLE在最大值10的基础上，每增加1，选择度降低1/9。ORACLE在最小值1的基础上，每减少1，选择度降低1/9，直到选择率为0，此时的选择基数ORACLE设置为1，以后不会再改变。

所以如果我们查询ID=11时，那么选择度为：(1/10)*(1-1/9)=0.088888889

所以选择的基数应该为：ROUND(1000*0.088888889)=89

如果查询ID=17，那么选择度为：(1/10)*(1-7/9)=0.022222222

所以选择的基数应该为：ROUND(1000*0.022222222)=22

验证一下：

SQL> SELECT COUNT(1) FROM T WHERE ID=17;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |   22 |    66 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=17)

Note

-----

- cpu costing is off (consider enabling it)

当选择度降低为0时，对于我们的情况也就是ID=19的时候，那么ORACLE将选择基础设置为1，当在加大ID时候，选择基数不会再改变。SQL> SELECT COUNT(1) FROM T WHERE ID=19;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=19)

Note

-----

- cpu costing is off (consider enabling it)

SQL> SELECT COUNT(1) FROM T WHERE ID=20;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=20)

Note

-----

- cpu costing is off (consider enabling it)

SQL> SELECT COUNT(1) FROM T WHERE ID=201;

执行计划

----------------------------------------------------------

Plan hash value: 1842905362

-----------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost  |

-----------------------------------------------------------

|   0 | SELECT STATEMENT   |      |     1 |     3 |     3 |

|   1 |  SORT AGGREGATE    |      |     1 |     3 |       |

|*  2 |   TABLE ACCESS FULL| T    |     1 |     3 |     3 |

-----------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("ID"=201)

Note

-----

- cpu costing is off (consider enabling it)

对于超出边界的小于的情况，道理一样。