存在以下索引
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>