SQL优化之不走索引_走rule很快，CBO很慢

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

收集直方图后走了正确的执行计划