CBO一个错误的索引选择会带来的后果

在对20亿记录大表的查询中，发现Oracle的执行计划选择并不稳定，当然这是CBO的正常行为，然而当选择不同时，结果是巨大的。

在以下查询中，使用指定的索引，查询快速得出结果，但是这依赖于Hints的强制指定：

SQL> select /*+  index(smsmg IDX_smsmg_DEST_MDN)  */ count(*)

2 from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';

COUNT(*)

----------

1

Elapsed: 00:00:00.00

Execution Plan

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

Plan hash value: 1659057974

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

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

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

|   0 | SELECT STATEMENT                    |                          |     1 |    18 |    98   (0)| 00:00:02 |       |       |

|   1 |  SORT AGGREGATE                     |                          |     1 |    18 |            |          |       |       |

|*  2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg                    |     1 |    18 |    98   (0)| 00:00:02 | ROWID | ROWID |

|*  3 |    INDEX RANGE SCAN                 | IDX_smsmg_msg_to_des_mdn |   106 |       |     4   (0)| 00:00:01 |       |       |

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

Predicate Information (identified by operation id):

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

2 - filter("SERVICE_ID"='54')

3 - access("msg_to_dest_mdn"='861318888888')

Statistics

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

0  recursive calls

0  db block gets

82  consistent gets

0  physical reads

0  redo size

515  bytes sent via SQL*Net to client

469  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

而如果不加Hints，查询是一时无法得出结果的：

SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';

select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54'

*

ERROR at line 1:

ORA-01013: user requested cancel of current operation

Elapsed: 00:04:27.88

其执行计划显示，这一缺省的执行方式导致了错误的索引选择：

SQL> set autotrace trace explain

SQL> select count(*) from smsmg where msg_to_dest_mdn='861318888888' and service_id='54';

返回栏目页：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/