Re: Last 100 rows

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/04/24
Message-ID: <8e1ke6$gv9$1@nnrp1.deja.com>#1/1

In article <8e17bu$i0u$1_at_nn-os105.ocn.ad.jp>,   "sang" <laoxiu100_at_hotmail.com> wrote:
> Hi ,all
>
> I want to get my last 100 rows data from my huge table ordered by
 date.
> and i want to get the last row too.
> I know i can Select * from hugetable and move to the last, but it
 will take
> much
> time for it.
>
> Has anyone a quick way?
>
> Thanks in advance
>

Use pl/sql with a descending sort on the data:

DDC2> set serveroutput on size 8192
DDC2> declare
  2 v_sequence_name all_sequences.sequence_name%type ;   3 v_sequence_owner all_sequences.sequence_owner%type ;   4 --
  5 cursor c_exp is
  6 select sequence_name

  7      from all_sequences
  8     order by sequence_name desc, sequence_owner ; <== notice desc

  9 --
 10 begin
 11 open c_exp ;
 12 for c_1 in 1..100 loop <== fixed loop since always 100  13 fetch c_exp into v_sequence_name ;  14 dbms_output.put_line(v_sequence_name) ;  15 end loop ;
 16 close c_exp ;
 17 end ;
 18 /
WO_ORDER_SEQ
WO_MRP_SEQ
WIP_TRANS_SEQ
WIP_REQ_NO
....

The main drawback is that you have to read and sort the data which takes time. If you are 8i you could build a function index on the date column where the function had the effect of ordering the data in reverse order [decreasing value]. Then you could retrieve via the function index. For ver 8 and before you have to add a new column to the table and populate it via a trigger. Then you can index this new column and retrive data via the index using rownum to limit the rows returned.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon Apr 24 2000 - 00:00:00 CDT