在对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';
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1152948967
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 5 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 18 | | | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| smsmg | 1 | 18 | 5 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | IDX_smsmg_SERVICE_ID | 1 | | 4 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("msg_to_dest_mdn"='861318888888')
3 - access("SERVICE_ID"='54')
说CBO是Oracle最为博大精深的技术一点也不为过，只是这技术越复杂越深奥出错的机会就越多了。
-The End-