Home » SQL & PL/SQL » SQL & PL/SQL » Query to get the last 100 rows in a table
Query to get the last 100 rows in a table [message #297489] Fri, 01 February 2008 01:40 Go to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Hi experts,

What would be the optimised Query to get the last 100 rows in a table with one of the column as a autoincrement feild.

Thanks

Re: Query to get the last 100 rows in a table [message #297490 is a reply to message #297489] Fri, 01 February 2008 01:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You sort the by the sequence in descending order and you take the first 100 rows.

And this a SQL Newbie question and not a PL/SQL expert one.

Regards
Michel

[Updated on: Fri, 01 February 2008 01:42]

Report message to a moderator

Re: Query to get the last 100 rows in a table [message #297493 is a reply to message #297490] Fri, 01 February 2008 02:04 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Hi Michel,

I have millions of records in my table so just was thinking sorting would take long time as the data/table grows.

Am i right..?

Thanks a lot for your response
rballal
Re: Query to get the last 100 rows in a table [message #297494 is a reply to message #297489] Fri, 01 February 2008 02:25 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
I don't think so.The provided approach is adaquate.
Re: Query to get the last 100 rows in a table [message #297497 is a reply to message #297493] Fri, 01 February 2008 02:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The identity column should be indexed, Oracle optimizer understands what you want to do and only gets the 100 rows from index from the top end.
SQL> create table t as 
  2  select object_id, object_name from dba_objects where object_id is not null;

Table created.

SQL> alter table t add constraint t_pk primary key (object_id);

Table altered.

SQL> set autotrace traceonly explain
SQL> select * 
  2  from (select object_id from t order by object_id desc) 
  3  where rownum<=100
  4  /

Execution Plan
----------------------------------------------------------
Plan hash value: 330171726

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |   100 |  1300 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |      |       |       |            |          |
|   2 |   VIEW                       |      | 36149 |   458K|     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN DESCENDING| T_PK | 36149 |   458K|     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<=100)

Regards
Michel

[Updated on: Fri, 01 February 2008 02:44]

Report message to a moderator

Re: Query to get the last 100 rows in a table [message #297742 is a reply to message #297497] Sun, 03 February 2008 04:15 Go to previous messageGo to next message
rballal
Messages: 12
Registered: February 2008
Junior Member
Hi

I have 4 million records in the table and when i tried executing this query it took really long time.

I set "set autotrace traceonly explain" to see the exeution plan and time as u have done below but the Time feild does not show up.How do i enable this?

Thanks,
rb
Re: Query to get the last 100 rows in a table [message #297747 is a reply to message #297742] Sun, 03 February 2008 05:05 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do i enable this?

You don't have anything to do, the availability of this depends on your version.

Post your explain plan (in a formatted way as I did).
Do you have index?
Does Oracle use it?
Do you use workarea policy manual or auto?
What is the size of your sort area?
What is your Oracle version?
...
Performances depend on all these.

Regards
Michel
Previous Topic: How to convert data values (merged)
Next Topic: database triggers
Goto Forum:
  


Current Time: Sat Dec 10 01:32:46 CST 2016

Total time taken to generate the page: 0.09572 seconds