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.
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.I illustrate it using the
EMPLOYEES
table in the standardHR
schema, ordering bySALARY
. 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 thepattern
sub-clause.You can combine into a single query and a single pass over the table using analytic functions, generating two pseudocolumns in this case:
and then filtering using that query as an inline view (or CTE):
I've assumed your ordering is on
column1
, and pickedyour_table
as a table name as you didn't include that either, so change as appropriate. Depending on how you want to handle ties, you might want to use therank()
ordense_rank()
functions instead.From @mathguy's comment, this may well perform better: