How To Select Last N Records From A Table

How To Select Last N Records From A Table


  • 26 Feb, 2012

    select * from (select rownum a, CLASS_CODE,CLASS_DESC from clm)where a > ( select (max(rownum)-10) from clm)Here N = 10The following query has a Problem of performance in the execution of the following query where the table ter.ter_master have 22231 records. So the results are obtained after hours.Cursor rem_master(brepno VARCHAR2) ISselect a.* from ter.ter_master awhere NOT a.repno in (select repno from ermast) and(brepno = 'ALL' or a.repno > brepno)Order by a.repnoWhat are steps required tuning this query to improve its performance?-Have an index on TER_MASTER.REPNO and one on ERMAST.REPNO-Be sure to get familiar with EXPLAIN PLAN. This can help you determine the execution path that Oracle takes. If you are using Cost Based Optimizer mode, then be sure that your statistics on TER_MASTER are up-to-date. -Also, you can change your SQL to:SELECT a.*FROM ter.ter_master aWHERE NOT EXISTS (SELECT b.repno FROM ermast bWHERE a.repno=b.repno) AND(a.brepno = 'ALL' or a.repno > a.brepno)ORDER BY a.repno;

    Comments Received:
    Please give your suggestions and feedback:

2009-2016 downloadmela.com. All rights reserved.