Sorting for the whole data in Angular Material Grid [duplicate]
Solution 1:
Something like this should work: From Frans Bouma's Blog
SELECT * FROM
(
SELECT a.*, rownum r__
FROM
(
SELECT * FROM ORDERS WHERE CustomerID LIKE 'A%'
ORDER BY OrderDate DESC, ShippingDate DESC
) a
WHERE rownum < ((pageNumber * pageSize) + 1 )
)
WHERE r__ >= (((pageNumber-1) * pageSize) + 1)
Solution 2:
Ask Tom on pagination and very, very useful analytic functions.
This is excerpt from that page:
select * from (
select /*+ first_rows(25) */
object_id,object_name,
row_number() over
(order by object_id) rn
from all_objects
)
where rn between :n and :m
order by rn;
Solution 3:
In the interest of completeness, for people looking for a more modern solution, in Oracle 12c there are some new features including better paging and top handling.
Paging
The paging looks like this:
SELECT *
FROM user
ORDER BY first_name
OFFSET 5 ROWS FETCH NEXT 10 ROWS ONLY;
Top N Records
Getting the top records looks like this:
SELECT *
FROM user
ORDER BY first_name
FETCH FIRST 5 ROWS ONLY
Notice how both the above query examples have ORDER BY
clauses. The new commands respect these and are run on the sorted data.
I couldn't find a good Oracle reference page for FETCH
or OFFSET
but this page has a great overview of these new features.
Performance
As @wweicker points out in the comments below, performance is an issue with the new syntax in 12c. I didn't have a copy of 18c to test if Oracle has since improved it.
Interestingly enough, my actual results were returned slightly quicker the first time I ran the queries on my table (113 million+ rows) for the new method:
- New method: 0.013 seconds.
- Old method: 0.107 seconds.
However, as @wweicker mentioned, the explain plan looks much worse for the new method:
- New method cost: 300,110
- Old method cost: 30
The new syntax caused a full scan of the index on my column, which was the entire cost. Chances are, things get much worse when limiting on unindexed data.
Let's have a look when including a single unindexed column on the previous dataset:
- New method time/cost: 189.55 seconds/998,908
- Old method time/cost: 1.973 seconds/256
Summary: use with caution until Oracle improves this handling. If you have an index to work with, perhaps you can get away with using the new method.
Hopefully I'll have a copy of 18c to play with soon and can update
Solution 4:
Just want to summarize the answers and comments. There are a number of ways doing a pagination.
Prior to oracle 12c there were no OFFSET/FETCH functionality, so take a look at whitepaper as the @jasonk suggested. It's the most complete article I found about different methods with detailed explanation of advantages and disadvantages. It would take a significant amount of time to copy-paste them here, so I won't do it.
There is also a good article from jooq creators explaining some common caveats with oracle and other databases pagination. jooq's blogpost
Good news, since oracle 12c we have a new OFFSET/FETCH functionality. OracleMagazine 12c new features. Please refer to "Top-N Queries and Pagination"
You may check your oracle version by issuing the following statement
SELECT * FROM V$VERSION