oracle在什么时候使用索引

正常情况下，一条SQL语句使用索引，在的where谓语条件中要出索引的左边部分（where条件出现字段从建索引的字段的顺序左边字段开始，例如：create index ind on table(column1,column2,column3），只有where条件出现了下列谓语:column1、column1，column2、column1，column2，column3；才会使用索引。

创建表T

create table t as select decode(mod(rownum,2),0,'F',2,'M') flag,t.* from all_objects t;
commit;

创建索引
create index ind_t on t(flag,object_id);

获取统计数据
analyze table t compute statistics;
analyze index ind_t compute statistics;

情况一：快速全面扫描索引：因为需要查询的和where语句所关联的字段都在索引中，并且索引一般比表小得多。从而减少物理IO，提高查询性能，
SQL> set autot traceonly exp
SQL> select flag,object_id from t;
执行计划
----------------------------------------------------------
Plan hash value: 1148684643
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 70636 |   344K|    48   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| IND_T | 70636 |   344K|    48   (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL>

情况二：索引跳跃式扫描：如果索引的左边键值非常基数非常少，优化器会使用这种方式检索。

SQL> select flag,object_id from t where object_id=1231;
执行计划
----------------------------------------------------------
Plan hash value: 3688940926
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |     2   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | IND_T |     1 |     5 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_ID"=1231)
       filter("OBJECT_ID"=1231)

SQL> select * from t where object_id=100;
 
 select * from t where object_id<100;这个使用跳越索引

select * from t where object_id>100;这个走全表扫描

索引是否使用索引 oracle会根据获取数据块数占总行数大小（对于有一些表大于多块读的块个数的表）

修改索引左边的字段，让他的基数更高

alter table t modify flag varchar2(10);

update t set flag=to_char(mod(rownum,10000)) ;

收集统计信息

 exec dbms_stats.gather_table_stats(user,'T',cascade=>true);

select * from t where object_id=1231;  这样就可能不走跳跃索引

情况三：行数统计

select count(*) from t

可能不会考虑直接遍历索引，因为在B树索引中，存在null。

情况四：索引列上使用函数

select * from t where fun(index_columns)=223;

因为索引列上使用的函数，索引不能直接使用索引列直接创建的索引，应该使用这个函数所对应的函数索引，并且这个函数是deterministic类性

情况五：索引列显示或者隐式转换

select * from t where  index_column=5;

如果index_column是一个字符类型，这样相当如oracle调用了to_number(index_column)=5，从而无法使用索引

SQL> explain plan for select * from t where flag=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   495 | 51975 |  2075   (1)| 00:00:25 |
|*  1 |  TABLE ACCESS FULL| T    |   495 | 51975 |  2075   (1)| 00:00:25 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   1 - filter(TO_NUMBER("FLAG")=3)

已选择13行。

SQL> explain plan for select /*+ index(t ind_t)*/ * from t where flag=3;
已解释。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1099373541
--------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time|

--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   495 | 51975 |  2491   (1)| 00:00:30 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   495 | 51975 |  2491   (1)| 00:00:30 |
|*  2 |   INDEX FULL SCAN           | IND_T |   495 |       |  1998   (1)| 00:00:24 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER("FLAG")=3)
已选择14行。

SQL> explain plan for select * from t where flag='3';

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time
|   0 | SELECT STATEMENT            |       |   495 | 51975 |   497   (0)| 00:00:06 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |   495 | 51975 |   497   (0)| 00:00:06 |
|*  2 |   INDEX RANGE SCAN          | IND_T |   495 |       |     4   (0)| 00:00:01 |

在where谓语条件中，减少对数据列使用函数

情况六：根据统计表的大小和访问数据量来决定是否使用索引

根据统计信息，oracle会假设数据分布均匀，获取的信息不仅仅包含索引。

1、如果获取的数据占总行数的非常小比例，并且这张表非常大，这时会使用索引。

2、如果获取的数据占总行数的非常大比例，或者数据表非常小，这时会使用全表扫描。