有表表APPLICATION_DATA_BAK 记录条数为500万,字段段link_id的有6种取值,且不能为空。现有查询语句
Select * from APPLICATION_DATA_BAK WHERE LINK_ID=342
不加任何索引的执行计划为
238683 rows selected.
Elapsed: 00:00:15.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
195 recursive calls
0 db block gets
77193 consistent gets
61349 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
238683 rows processed
对LINK_ID加bitmap索引时的执行计划为:
238683 rows selected.
Elapsed: 00:00:14.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77143 consistent gets
61226 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed加索引后发现性能并没有改善,同时查询计划中也并没有使用索引,因此我强制使用指定的索引,起sql语句为select /*+ index(APPLICATION_DATA_BAK, LINKID_INDEX)*/ * FROM APPLICATION_DATA_BAK WHERE LINK_ID=342; 其执行计划为:
238683 rows selected.
Elapsed: 00:00:13.34
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16816 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK' (T
ABLE) (Cost=16816 Card=239728 Bytes=13904224) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (INDEX (
BITMAP))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23028 consistent gets
7218 physical reads
0 redo size
17347210 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed
性能几乎没有改善,请问为了提高该查询语句的性能,该怎么加索引呢,谢谢!
Select * from APPLICATION_DATA_BAK WHERE LINK_ID=342
不加任何索引的执行计划为
238683 rows selected.
Elapsed: 00:00:15.11
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
195 recursive calls
0 db block gets
77193 consistent gets
61349 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
238683 rows processed
对LINK_ID加bitmap索引时的执行计划为:
238683 rows selected.
Elapsed: 00:00:14.62
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=13624 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (FULL) OF 'APPLICATION_DATA_BAK' (TABLE) (Cos
t=13624 Card=239728 Bytes=13904224)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
77143 consistent gets
61226 physical reads
0 redo size
6482097 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed加索引后发现性能并没有改善,同时查询计划中也并没有使用索引,因此我强制使用指定的索引,起sql语句为select /*+ index(APPLICATION_DATA_BAK, LINKID_INDEX)*/ * FROM APPLICATION_DATA_BAK WHERE LINK_ID=342; 其执行计划为:
238683 rows selected.
Elapsed: 00:00:13.34
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16816 Card=239728
Bytes=13904224) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'APPLICATION_DATA_BAK' (T
ABLE) (Cost=16816 Card=239728 Bytes=13904224) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINKID_INDEX' (INDEX (
BITMAP))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23028 consistent gets
7218 physical reads
0 redo size
17347210 bytes sent via SQL*Net to client
175544 bytes received via SQL*Net from client
15914 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
238683 rows processed
性能几乎没有改善,请问为了提高该查询语句的性能,该怎么加索引呢,谢谢!
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货