一个查询语句physical reads为0的情况下，用索引检索26万笔记录，需要10秒钟！请大家

显示全部楼层 · 2005-2-28 12:57:00

情况：一张表26万条记录，建立了索引进行优化。
执行完存储过程后，查看Statistics如下：
---------------------------------------------------------------------------------------
name..........................................last..................total
physical reads............................0......................0
physical writes............................0......................0
table scans (short tables)...........15....................116
table scans (long tables).............0.....................9
table scan rows gotten...............1086................2401
table scan blocks gotten............1150................3230
table fetch by rowid....................5859................197457
sorts (memory)............................11.....................89
sorts (disk)..................................0.......................3
sorts (rows).................................6656.................113150
session logical reads..................9728.................120897
CPU used by this session...........74.....................805
---------------------------------------------------------------------------------------
执行时间为12.345秒！
实在想不通physical reads为零的情况下，怎么还需要花费那么多的时间来查询？
如何能降低table fetch by rowid 和sorts的次数呢？

千问 · 2005-2-28 12:57:00

3次disk sort

千问 · 2005-2-28 12:57:00

做个10046 , level 8的trace event

千问 · 2005-2-28 12:57:00

执行计划。