How to find the last 100k rows from 10000K table in oracle?

Question

when i am using this query it is taking more than 5 mins please give me some other suggestion

 SELECT * FROM 
( SELECT  id,name,rownum AS RN$$_RowNumber FROM MILLION_1) INNER_TABLE where
 RN$$_RowNumber  > (V_total_count - V_no_of_rows)
 ORDER BY RN$$_RowNumber DESC;

Answer 1

Try the offset clause.

I have a table with about 16M records in it, if i just want the last 100,000 rows, I ORDER them via the ORDER BY clause, and then I use the OFFSET clause, which basically says, read this many rows first, before you return any data.

select *
  from SHERI; -- 15,691,544  Rows

select *
  from SHERI
 order by COLUMN4 asc
offset 15591444 rows; -- my math was bad, should have offset 15591544 rows to get just the last 100,000 

The FETCH FIRST and OFFSET clauses are new for 12c (docs)

If we look at the plan under this query, we can see how the database makes it work:

PLAN_TABLE_OUTPUT                                                                   
SQL_ID  7wd4ra8pfu1vb, child number 0                                               
-------------------------------------                                               
select *   from SHERI  order by COLUMN4 asc offset 15591444 rows                    

Plan hash value: 3535161482                                                         

----------------------------------------------                                      
| Id  | Operation           | Name  | E-Rows |                                      
----------------------------------------------                                      
|   0 | SELECT STATEMENT    |       |        |                                      
|*  1 |  VIEW               |       |     15M|                                      
|   2 |   WINDOW SORT       |       |     15M|                                      
|   3 |    TABLE ACCESS FULL| SHERI |     15M|                                      
----------------------------------------------                                      

Predicate Information (identified by operation id):                                 
---------------------------------------------------                                 

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber">15591444)               

Note                                                                                
-----                                                                               
   - Warning: basic plan statistics not available. These are only collected when:   
       * hint 'gather_plan_statistics' is used for the statement or                 
       * parameter 'statistics_level' is set to 'ALL', at session or system level   

'window sort' basically translates to, an analytic function

Answer 2

There are some very thorough answers at this similar question, but I'll try to make them specific to your case.

First, when you say "last 100k rows", what do you mean? It looks like you just want to pull the last 100k rows from an unsorted query, but that doesn't make a lot of sense. If you want the 100k most recent rows, Oracle doesn't guarantee that they'll be at the end of your unsorted query. So you want to order by something which will have the most recent ones at the end.

Also, part of the reason your query is slow is that you're sorting/filtering on the rownum pseudo-column, which can't be indexed. Sorting on a column that has an index would drastically speed this up. So I'd guess you want to order by the id column, which is probably a unique/primary key.

So this is the old (11g and earlier) way to do this.

select id, name
from (select id, name
      from MILLION_1
      order by id desc)
where rownum < 100000;

If you're on 12c or later, there's a newer way to do it.

select id, name
from MILLION_1
order by id desc
fetch first 100000 rows only;