10g的时候，CBO计算表返回多少数据，最简单的情况就是各个列的DENSITY的乘积，不会用到索引的信息。如果相关的2列之间有依赖关系，从统计信息上是无法反应的。

11g新增了一个组合列统计的功能的功能，今天在测试时，发现组合索引的统计信息，其实也用于了CBO计算表的card

测试的表，没有收集直方图

如下是没有创建ID4，ID5上组合索引的情况

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 2261054565

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |              |    10 |   720 |  1005   (0)| 00:00:13 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST         |    10 |   720 |  1005   (0)| 00:00:13 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID4 |  1000 |       |     5   (0)| 00:00:01 |

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

可以看到Table Access 的Card = 10 ,这是通过 表行数 * ID4选择率 * ID5选择率 = 1000000 * .001000 * .010000 = 10

创建一个组合索引

CREATE INDEX idx_test_id45 ON TEST(id4,id5)

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3852160031

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |  1000 | 72000 |  1005   (0)| 00:00:13 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |  1000 | 72000 |  1005   (0)| 00:00:13 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID45 |  1000 |       |     5   (0)| 00:00:01 |

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

可以看到Table Access 的Card = 1000，这是通过表行数 * 组合索引的选择率 = 1000000 * (1/1000) = 1000

还可以看到，就算不用索引，索引的DISTINCT值，也已经进入了基数计算

SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |  1000 | 72000 |  2165   (1)| 00:00:26 |

|*  1 |  TABLE ACCESS FULL| TEST |  1000 | 72000 |  2165   (1)| 00:00:26 |

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

开始怀疑创建索引，默认可能收集了复合列统计信息，可是查询了dba_stat_extensions以及其下的基表，都没有发现什么有价值的信息。

尝试修改索引的统计信息：为了避免DBMS_STATS可能递归修改其他的数据，我选择了直接UPDATE表

SQL> UPDATE ind$ SET distkey=2000 WHERE obj#=80692;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system flush shared_pool;

System altered.

SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |   500 | 36000 |  2165   (1)| 00:00:26 |

|*  1 |  TABLE ACCESS FULL| TEST |   500 | 36000 |  2165   (1)| 00:00:26 |

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

可以看到，返回的Card确实降低为了以前的1/2

如果除去索引外，还有其他谓词，也可以使用组合索引的统计

SQL> EXPLAIN PLAN FOR SELECT /*+full(A)*/ * FROM ctais2.TEST A WHERE id4=5 AND id5=5 AND id3=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |   250 | 18000 |  2165   (1)| 00:00:26 |

|*  1 |  TABLE ACCESS FULL| TEST |   250 | 18000 |  2165   (1)| 00:00:26 |

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

250 = 1000000 * (1/2000) * 0.5 = 表行数 * 组合索引的选择率 * ID3选择率

什么时候无法使用索引的统计信息来计算表的返回CARD

1.组合索引列非等值查询

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5<5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3852160031

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |    51 |  3672 |    54   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |    51 |  3672 |    54   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID45 |    51 |       |     3   (0)| 00:00:01 |

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

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4<5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 2874578131

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |    50 |  3600 |    65   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |    50 |  3600 |    65   (0)| 00:00:01 |

|*  2 |   INDEX SKIP SCAN           | IDX_TEST_ID45 |    50 |       |    14   (0)| 00:00:01 |

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

删除索引后：

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5<5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 2261054565

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

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |              |    51 |  3672 |  1005   (0)| 00:00:13 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| TEST         |    51 |  3672 |  1005   (0)| 00:00:13 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID4 |  1000 |       |     5   (0)| 00:00:01 |

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

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4<5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      |    50 |  3600 |  2165   (1)| 00:00:26 |

|*  1 |  TABLE ACCESS FULL| TEST |    50 |  3600 |  2165   (1)| 00:00:26 |

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

可以看到Rows没有任何变化。

这时候CARD的计算为：

id4=5 AND id5<5 => 1000000 * 0.001 * (5-0)/(99-0) = 50.51 = 51

id4<5 AND id5=5 => 1000000 * (5-0)/(999-0) * 0.01 = 50.05 = 50

2.组合索引的列，收集了直方图

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3852160031

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |    10 |   720 |    13   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |    10 |   720 |    13   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID45 |    10 |       |     3   (0)| 00:00:01 |

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

SQL> EXEC dbms_stats.delete_column_stats('ctais2','TEST','ID5',col_stat_type => 'HISTOGRAM');

PL/SQL procedure successfully completed.

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3852160031

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |    10 |   720 |    13   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |    10 |   720 |    13   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID45 |    10 |       |     3   (0)| 00:00:01 |

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

SQL> EXEC dbms_stats.delete_column_stats('ctais2','TEST','ID4',col_stat_type => 'HISTOGRAM');

PL/SQL procedure successfully completed.

2列上的直方图都删除后

SQL> EXPLAIN PLAN FOR SELECT * FROM ctais2.TEST A WHERE id4=5 AND id5=5;

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3852160031

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |  1000 | 72000 |  1005   (0)| 00:00:13 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST          |  1000 | 72000 |  1005   (0)| 00:00:13 |

|*  2 |   INDEX RANGE SCAN          | IDX_TEST_ID45 |  1000 |       |     5   (0)| 00:00:01 |

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