[索引相关] 不使用索引的情况 - 1

-- btree索引上，谓词中没有使用索引的最前列(有可能使用跳跃式索引或不使用索引)
---当gender列上只有很少几个不同值时，使用跳跃索引

go@DEMO> create table t
as select decode(mod(rownum,2),0,'M','F') gender,all_objects.*
from all_objects
/

Table created.

go@DEMO> create index t_idx on t (gender,object_id);

Index created.

go@DEMO> begin
dbms_stats.gather_table_stats
(user,'T',cascade=>true);
end;
/

PL/SQL procedure successfully completed.

go@DEMO> set autotrace traceonly explain
go@DEMO> select * from t where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 2053318169

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    96 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |    96 |     4   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | T_IDX |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=42)
       filter("OBJECT_ID"=42)

go@DEMO> update t
  2  set gender=chr(mod(rownum,256));

44492 rows updated.

go@DEMO> commit;

Commit complete.

go@DEMO> begin
dbms_stats.gather_table_stats
(user,'T',cascade=>true);
end;
/

PL/SQL procedure successfully completed.

go@DEMO> select * from t where object_id=42;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    95 |   145   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    95 |   145   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=42)