选择TOP N和BOTTOM N [英] Select TOP N and BOTTOM N

问题描述

尝试获取前n行，后n行.尽管它给了我结果，但是却需要很多时间.我相信它会扫描表两次.

Trying to fetch top n bottom n rows. Though it gives me result but, it takes lot of time. I believe it scans table twice.

Code used:
WITH TI AS
(SELECT * FROM
(SELECT
Column1,
Column2,
Colmn3
FROM TABLE
ORDER BY DESC
)
WHERE ROWNUM<=5),
T2 AS
(SELECT * FROM
(SELECT
Column1,
Column2,
Colmn3
FROM TABLE
ORDER BY ASC
)
WHERE ROWNUM<=5)

SELECT * FROM T1
UNION ALL
SELECT * FROM T2

我该如何以更快的方式获取呢? 考虑到表会定期更新.

How can i fetch this in more faster way?? Considering that tables are updated regularly.

推荐答案

解决此问题的最佳方法部分取决于您的Oracle版本.这是使用版本12.1中添加的match_recognize子句的非常简单(并且，我怀疑是非常有效的)解决方案.

The best way to solve this problem depends in part on your Oracle version. Here is a very simple (and, I suspect, very efficient) solution using the match_recognize clause, added in version 12.1.

我使用标准HR架构中的EMPLOYEES表来说明它，并按SALARY进行排序.这里唯一的技巧是选择顶部和底部的五行，而忽略它们之间的所有内容. (忽略")是{- ... -}运算符在pattern子句中所做的.

I illustrate it using the EMPLOYEES table in the standard HR schema, ordering by SALARY. The only trick here is to select the top and bottom five rows, and to ignore everything in between; that (the "ignoring") is what the {- ... -} operator does in the pattern sub-clause.

select employee_id, first_name, last_name, salary
from   hr.employees
match_recognize(
  order by salary desc
  all rows per match
  pattern ( a{5} {- a* -} a{5} )
  define a as 0 = 0             -- For reasons known only to Oracle, DEFINE is required.
);

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24000
        101 Neena                Kochhar                        17000
        102 Lex                  De Haan                        17000
        145 John                 Russell                        14000
        146 Karen                Partners                       13500
        135 Ki                   Gee                             2400
        127 James                Landry                          2400
        136 Hazel                Philtanker                      2200
        128 Steven               Markle                          2200
        132 TJ                   Olson                           2100

这篇关于选择TOP N和BOTTOM N的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！