SQL> create table test(id int);
表已创建。
SQL> insert into test select 1 from table1;
已创建1186623行。
SQL> insert into test select 2 from table2;
已创建70行。
SQL> commit;
提交完成。
SQL> create index i_test_1 on test(id);
索引已创建。
SQL> analyze table test compute statistics;
表已分析。
SQL> set autot trace
SQL> set timing on
SQL> select * from test where id=2;
已选择70行。
已用时间: 00: 00: 00.03
执行计划
----------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 593K| 1158K| 534 (9)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| TEST | 593K| 1158K| 534 (9)| 00:00:07 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1811 consistent gets
0 physical reads
0 redo size
1290 bytes sent via SQL*Net to client
444 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70 rows processed
未走索引,统计信息没有反应真实情况
SQL> select /*+ rule */ * from test where id=2;
已选择70行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3245211066
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | INDEX RANGE SCAN| I_TEST_1 |
-------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=2)
Note
-----
- rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1290 bytes sent via SQL*Net to client
444 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70 rows processed
可以看出,走索引是很快的,这个字段是倾斜的,收集下直方图就能正确的执行计划
SQL> analyze table test compute statistics for table for all indexes for all indexed columns;
表已分析。
SQL> select * from test where id=2;
已选择70行。
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 3245211066
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 70 | 140 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| I_TEST_1 | 70 | 140 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=2)
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
1290 bytes sent via SQL*Net to client
444 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
70 rows processed
收集直方图后走了正确的执行计划