oracle没有自动调用索引,oracle 为什么没有使用索引的一种情况

存在以下索引

SQL> SELECT INDEX_NAME,COLUMN_NAME FROM USER_IND_COLUMNS WHERE INDEX_NAME='IDX_GBDS_CLIENT_GAME_EVENT';

INDEX_NAME                     COLUMN_NAME

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

IDX_GBDS_CLIENT_GAME_EVENT     CLIENT_ID

IDX_GBDS_CLIENT_GAME_EVENT     GAME_CODE_ID

IDX_GBDS_CLIENT_GAME_EVENT     EVENT_DATE

IDX_GBDS_CLIENT_GAME_EVENT     BET_TYPE

1.下面这样写不会用上索引

SQL> select * from tb_game_bet_detail_snapshot where game_code_id=50000;

no rows selected

Execution Plan

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

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=1050 Bytes=68

250)

1    0   TABLE ACCESS (FULL) OF 'TB_GAME_BET_DETAIL_SNAPSHOT' (Cost

=7 Card=1050 Bytes=68250)

Statistics

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

0  recursive calls

0  db block gets

126  consistent gets

0  physical reads

0  redo size

778  bytes sent via SQL*Net to client

364  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed

2.把谓词全部写上 用上快速索引扫描 一致性读也降下来

SQL> select client_id,game_code_id,event_date,bet_type from tb_game_bet_detail_s

napshot where game_code_id=50000;

no rows selected

Execution Plan

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

0      SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1050 Bytes=23

100)

1    0   INDEX (FAST FULL SCAN) OF 'IDX_GBDS_CLIENT_GAME_EVENT' (UN

IQUE) (Cost=5 Card=1050 Bytes=23100)

Statistics

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

0  recursive calls

0  db block gets

35  consistent gets

0  physical reads

0  redo size

353  bytes sent via SQL*Net to client

364  bytes received via SQL*Net from client

1  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

0  rows processed

SQL>