超出边界的基数估算问题

在10GR2中，对于超出边界的数值的选择度问题，ORACLE改变了算法。

注意：以下测试在没有直方图的情况下。

 

SQL> CREATE TABLE T(ID INT);

 

表已创建。

 

SQL> INSERT INTO T
  2  SELECT TRUNC(DBMS_RANDOM.VALUE(1,11))
  3  FROM ALL_OBJECTS
  4  WHERE ROWNUM<=1000;

 

已创建1000行。

 

SQL> COMMIT;

 

提交完成。

 

SQL> SELECT DISTINCT ID FROM T;

 

        ID
----------
         7
         6
         5
         8
         3
         2
         1
         9
         4
        10

 

已选择10行。

 

SQL> SELECT COUNT(1) FROM T;

 

  COUNT(1)
----------
       1000

 

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'T',method_opt=>'FOR ALL COLUMNS SIZE 1');

 

PL/SQL 过程已成功完成。

 

SQL> COL COLUMN_NAME FORMAT A20
SQL> COL TABLE_NAME FORMAT A20

SQL> SELECT TABLE_NAME,NUM_ROWS
  2  FROM USER_TABLES
  3  WHERE TABLE_NAME='T';

 

TABLE_NAME             NUM_ROWS
-------------------- ----------
T                           1000

 

SQL> SELECT COLUMN_NAME,NUM_DISTINCT,DENSITY
  2  FROM USER_TAB_COL_STATISTICS
  3  WHERE TABLE_NAME='T';

 

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
ID                             10         .1

 

此时的密度为0.1,也就是选择度为10%。

 

ORACLE通过视图USER_TAB_HISTOGRAMS来查找列的边界值。

 

SQL> COL COLUMN_NAME FORMAT A10

 

SQL> SELECT COLUMN_NAME,ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='T';

 

COLUMN_NAM ENDPOINT_VALUE
---------- --------------
ID                      1
ID                     10

 

已选择2行。

如果我们的查询落到了1-10之间，没超出边界，那么选择的基数cardinality=NUM_ROWS*DENSITY=1000*10%=100。

 

SQL> SET AUTOT TRACEONLY EXP

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

 

执行计划
----------------------------------------------------------
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"=1)

Note
-----

   - 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)

 

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