梦见好多的军人:Oracle查找第n个最大值

Oracle查找第n个最大值

在国内外许多Oracle DBA站点上，有很多查找表中第n个最大值的小技巧，这些语句确实相当巧妙，它们都独辟蹊径地用到了Oracle表中的一个伪字段“level”。但是在很多情况下，这个语句变成了 美丽的陷阱。如果你想知道为什么，并怎样绕过它，请听我道来！
研究了几天，我发现一种衍生算法，即找出第n个和第m个最大值的差（如下）：
列表一：
select max(salary) from empdetails
   where level = &n
   connect by prior salary>salary
   start with salary = ( select max(salary) from empdetails);
此后又发现如下算法，利用Oracle的伪字段level，从最大值开始，按值递减顺序，把记录逐步分层，最后找出第n个最大值和第m个最大值之间的差值。
列表二：
select a.nmax_salary – b.mmax_salary from
  (select max(salary) from empdetails
     where level = &n
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails)
   ) a ,
  (select max(salary) mmax_salary from empdetails
     where level = &m
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails)
   ) b ;
我为此算法的新奇赞叹了很久，值到一天我想利用这个方法时，才发现这个方法并不如想象中那么美妙。
我要从60多个不同值中找出第n个最大值时，输入此语句后，发现如同石沉大海、杳无音讯，屏幕上只有“Statement is running”的提示。为了了解Oracle是怎样分层的，我输入如下语句：
列表三：
select lpad(‘ ‘,level)||salary from empdetails
     connect by prior salary > salary
     start with salary = (select (max(salary)) from empdetails) ;
发现屏幕在不断输出结果，足足等了十多分钟，还在不停地滚动，我意识到这算法存在某些问题。
试着分析一下列表三的结果，发现这个语句的回送记录数与库中不同数值的个数是指数增长，其公式为 RecordsRe = 2Rdeferent_value – 1 ，要在60多个不同值中用该算法找出第n个最大值，不知要花多长时间，即使机器不停计算几百年也不会得到结果。
还有其它方法吗？当然有，比如定义个游标，自己作一个计数器，用一段script来完成。假设empdetails表以salary建了一个索引salary_I1，可以用下列语句来完成：
列表四：
select salary from
  (select /* + Index(empdetails,salary_I1) */ Rownum, Row_num, salary
      from empdetails）where Row_num = &1;
在列表四中，用到了Oracle表中的另一个伪字段rownum，还用到了Oracle的优化提示（hint）。要注意Index提示是必须的，如果没有它，rownum是个伪字段，它不能与整数作相等的运算。Oracle一般按照记录插入的顺序来返回rownum，并不排序，如果用到此hint就可按salary字段排序返回。
列表四找到了按salary字段排序后的第n个记录的salary值。若表中有多个记录有相同的salary 值，而我们只要找到第 n 个最大 salary 值，而不是第 n 个记录，可以用列表5 的语句来实现。
列表五：
select salary from
  (select rownum aa, salary from
     (select distinct salary from empdetails)
   )
   where aa = ( select count (distinct salary ) + 1 - &n from empdetails);
至此，不会再出现列表一和列表二语句运行时的情况了，找第n个最大值的目的终于达到了。