根据索引列在Oracle SQL中查找"接下来的25行"

我有一个大型表(~200M行),它在数字列Z上编制索引.键列K上还有一个索引.

K  Z
=  ========================================== 
1  0.6508784068583483336644518457703156855132
2  0.4078768075307567089075462518978907890789
3  0.5365440453204830852096396398565048002638
4  0.7573281573257782352853823856682368153782

我需要做的是找到"围绕"给定记录的25条记录.例如,从K = 3开始的"下一个"记录将是K = 1,然后是K = 4.

我有几个来源(最值得注意的是佛罗里达州立大学的一些人的这篇论文),我认为以下的SQL应该有效.不难想象沿着索引列以升序或降序扫描是有效的.

select * from (
  select *
  from T
  where Z >= [origin's Z value]
  order by Z asc
) where rownum <= 25;

理论上,这应该找到25个"下一个"行,类似的变体会找到25个"前一个"行.但是,这可能需要几分钟,解释计划始终包含全表扫描.对于我的目的,全表扫描实在太昂贵了,但我没做什么似乎促使查询优化器利用索引(当然,将上面的"> ="更改为等号,这表示该指数存在并且可操作).我尝试了几个提示无济于事(索引,index_asc在几个排列中).

我想做什么不可能？如果我试图在我有更多控制权的大型数据结构上执行此操作,我将在索引列的值和树上构建链接列表以找到正确的入口点.然后遍历列表将是非常便宜的(是的,我可能必须在磁盘上运行以找到我正在寻找的记录,但我肯定不必扫描整个表).

我将添加以防我的查询对我正在使用的数据库运行Oracle Database 11g企业版11.2.0.3.0 - 64位非常重要.

我构建了一个10K行的小测试用例.当我填充表格以使Z值已经被排序时,您提供的确切查询倾向于使用索引.但是当我用随机值填充它并刷新表统计信息时,它开始进行全表扫描,至少对于大于25的n值.因此优化器确定工作量的临界点将查找索引条目然后查找表中的相应行是否超过完成扫描的工作量.(当然,它的估计可能是错误的,但这是它必须继续下去的.)

我注意到你正在使用SELECT *,这意味着查询返回两列.这意味着必须访问实际的表行,因为这两个索引都不包含这两列.这可能会促使优化器更倾向于对更大的样本进行全表扫描.如果可以仅从索引中完成查询,则更有可能使用索引.

一种可能性是你根本不需要返回值K.如果是的话,我建议你换两次出现的SELECT *到SELECT z.在我的测试中,此更改导致执行全表扫描的查询使用索引扫描(而根本不访问表本身).

如果确实需要包含K在结果中,则可以尝试创建索引(Z, K).此索引可用于在不访问表的情况下满足查询.