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 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
You can combine into a single query and a single pass over the table using analytic functions, generating two pseudocolumns in this case:
select column1, column2, column3,
row_number() over (order by column1 desc) rn_desc,
row_number() over (order by column1 asc) rn_asc
from your_table;
and then filtering using that query as an inline view (or CTE):
select column1, column2, column3
from (
select column1, column2, column3,
row_number() over (order by column1 desc) as rn_desc,
row_number() over (order by column1 asc) as rn_asc
from your_table
)
where rn_desc <=5
or rn_asc <= 5;
I've assumed your ordering is on column1, and picked your_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 the rank() or dense_rank() functions instead.
From @mathguy's comment, this may well perform better:
select column1, column2, column3
from (
select column1, column2, column3,
row_number() over (order by column1 desc) as rn,
count(*) over () as cnt
from your_table
)
where rn <=5
or cnt - rn < 5;
