Get last 200 records from table in oracle [message #664064] |
Tue, 04 July 2017 07:23 |
|
asliyanage
Messages: 60 Registered: January 2017
|
Member |
|
|
There is a table which contain 10 ,000,000 records. I need to get last 200 records that logging after 15 Dec 2015 , 0000 hours.
Login Date is a column in that table which contain logging time. So how to write a query for this.
|
|
|
|
|
|
|
|
|
|
|
Re: Get last 200 records from table in oracle [message #664080 is a reply to message #664075] |
Tue, 04 July 2017 08:10 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Rownum is worked out before the order by is applied, so that won't work. If you used row_number instead it would, but there's a simpler solution:
select *
from (
select a.*
from my_table a
order by order_field_name desc)
where rownum <= 200
|
|
|
|
|
|
|
|
|
|
Re: Get last 200 records from table in oracle [message #664223 is a reply to message #664222] |
Fri, 07 July 2017 03:22 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I don't know the reason, just Tom Kyte gave me this answer years ago: "if you really want to use rownum :)".
I also noticed that Oracle uses this function behind the scene for the new SQL features like TOP so this leads me to the conclusion they will investigate in this function improvements and let rownum aside (just like LONG vs LOB).
Without speaking about the far more things you can do with an analytical function you can't with rownum like getting the last 10 rows per group so no reason to use rownum for the single case it works and row_number for all the other ones above all when you read the artificial construction you have to do to make it works (ordering an inline view).
[Updated on: Fri, 07 July 2017 03:28] Report message to a moderator
|
|
|
Re: Get last 200 records from table in oracle [message #664230 is a reply to message #664223] |
Fri, 07 July 2017 10:16 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
rownum is very restricted. For example the following will never work
where rownum between 10 and 20
however using the row_number analytic and assigning the alias rn (for example)
where rn between 10 and 20
is totally legal and very fast
|
|
|
Re: Get last 200 records from table in oracle [message #664231 is a reply to message #664230] |
Fri, 07 July 2017 11:15 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Yes and this what Oracle uses in 12c:
SCOTT> select * from emp order by empno;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 800 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 1250 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/1987 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 23/05/1987 00:00:00 1100 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 950 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 3000 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 1300 10
14 rows selected.
SCOTT> select * from emp order by empno offset 5 rows fetch next 5 rows only;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 2850 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 2450 10
7788 SCOTT ANALYST 7566 19/04/1987 00:00:00 3000 20
7839 KING PRESIDENT 17/11/1981 00:00:00 5000 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 1500 0 30
5 rows selected.
And the EXPLAIN PLAN gives:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3291446077
---------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 4 (25)|
|* 1 | VIEW | | 14 | 4 (25)|
|* 2 | WINDOW SORT PUSHED RANK| | 14 | 4 (25)|
| 3 | TABLE ACCESS FULL | EMP | 14 | 3 (0)|
---------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN
(5>=0) THEN 5 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownu
mber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."EMPNO")<=CASE WHEN
(5>=0) THEN 5 ELSE 0 END +5)
|
|
|