SQL优化之不走索引_统计信息不对

SQL> create table test as select * from dba_objects where 1=0;

表已创建。

SQL> analyze table test compute statistics;

表已分析。

SQL> insert into test select * from dba_objects;

已创建52935行。

SQL> commit;
提交完成。

SQL> create index i_test_1 on test(object_id);
索引已创建。

SQL> select * from test where object_id=10000;
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   177 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |   177 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_ID"=10000)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        754  consistent gets
          0  physical reads
          0  redo size
       1221  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

重新分析

SQL> analyze table test compute statistics;

表已分析。

SQL> select * from test where object_id=10000;
执行计划
----------------------------------------------------------
Plan hash value: 3275607494
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    86 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |     1 |    86 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | I_TEST_1 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OBJECT_ID"=10000)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1221  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

统计信息正确后，走了索引