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
统计信息正确后，走了索引