表Test包含两个字段,分别是LINK_ID(NUMBER(10))和RECORD_TIME(DATE),表记录个数为400万,LINK_ID的有6种取值。SQL查询语句
select * from test t WHERE T.LINK_ID=342
在不加任何索引的情况下,执行计划为
212551 rows selected.Elapsed: 00:00:02.51Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2236 Card=192290 B
ytes=4230380) 1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2236 Card=1922
90 Bytes=4230380)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23982 consistent gets
8950 physical reads
0 redo size
2858021 bytes sent via SQL*Net to client
156382 bytes received via SQL*Net from client
14172 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
212551 rows processed给字段link_id加上bitmap索引后,select * from test t WHERE T.LINK_ID=342的执行计划为:
212551 rows selected.Elapsed: 00:00:03.26Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2167 Card=192290 B
ytes=4230380) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2167
Card=192290 Bytes=4230380) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINK_INDEX' (INDEX (BI
TMAP))
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
17343 consistent gets
2966 physical reads
0 redo size
5399263 bytes sent via SQL*Net to client
156382 bytes received via SQL*Net from client
14172 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
212551 rows processed加索引后,性能不仅没有改善,反而恶化了,这是什么原因呢,如果要提高该查询语句的性能,有没有其它办法。
select * from test t WHERE T.LINK_ID=342
在不加任何索引的情况下,执行计划为
212551 rows selected.Elapsed: 00:00:02.51Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2236 Card=192290 B
ytes=4230380) 1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2236 Card=1922
90 Bytes=4230380)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
23982 consistent gets
8950 physical reads
0 redo size
2858021 bytes sent via SQL*Net to client
156382 bytes received via SQL*Net from client
14172 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
212551 rows processed给字段link_id加上bitmap索引后,select * from test t WHERE T.LINK_ID=342的执行计划为:
212551 rows selected.Elapsed: 00:00:03.26Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2167 Card=192290 B
ytes=4230380) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2167
Card=192290 Bytes=4230380) 2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (SINGLE VALUE) OF 'LINK_INDEX' (INDEX (BI
TMAP))
Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
17343 consistent gets
2966 physical reads
0 redo size
5399263 bytes sent via SQL*Net to client
156382 bytes received via SQL*Net from client
14172 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
212551 rows processed加索引后,性能不仅没有改善,反而恶化了,这是什么原因呢,如果要提高该查询语句的性能,有没有其它办法。
联系方式QQ:370140387
QQ群: 85837884(注明:数据库)
电子邮件:[email protected]
网站: http://www.inthirties.com
10046得到的执行计划跟autotrace得到的执行计划,原理有不同吗?
错语的index指什么情况?
event trace log得到的信息更为详细一些。包含autotrace得到的执行计划。所以是有不同的错误的index,指的是建立的不是很有意义的index。也就是sql tuning里经常说道的,并不是加index就能提高性能的意思。
http://www.inthirties.com/?p=425==================================================================================== 如果你需要帮助或想和我一起学习的请联系
联系方式QQ:370140387
QQ群: 85837884(注明:数据库)
电子邮件:[email protected]
网站: http://www.inthirties.com