Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Retrieve N record from a large table...

Re: Retrieve N record from a large table...

From: Filip Hanik <fhanik_at_digitalworkforce.net>
Date: Wed, 1 Sep 1999 10:39:54 -0700
Message-ID: <_wdz3.388$0p2.17569@typhoon-sf.snfc21.pbi.net>


I agree with Ed,

relational databases have their best capabilities of searching through indexes.
you should try to retrieve the row from an index, you can even map an index to a row number, but that would require some work and you would have to worry about consistency problems since the DB doesn't really store the ROWNUM and if a row gets inserted then it messes everything up.

here's an earlier posting about using ROWNUM

>Hi anybody,
>
>I want to read the rows inside a generic table T
>between j and k (with j<k).
>For this operation I'm using the select statement:
>
>SELECT * FROM T WHERE rownum <=k
>MINUS
>SELECT * FROM T WHERE rownum <=j;
>
>My question is: there is a faster way to
>perform this operation?
>

yes and no. yes there is -- but it'll give you the same rows but in a different
order then you get them now. below are 2 queries. the minus forces a binary
sort on the first set, the lack of a minus skips the sort. the third query is a
further optimization the second query...

>Thanks in advance
>SbaizP.
>
>
>

SQL> select * from x where rownum <= 1000   2 minus
  3 select * from x where rownum <= 990;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
WEB$CCOULSON                         1888 01-SEP-97
WEB$CKANDERS                         1891 01-SEP-97
WEB$EWISEMAN                         1893 01-SEP-97
WEB$JBARKER                          1890 01-SEP-97
WEB$JHOFFMAN                         1887 01-SEP-97
WEB$NRICCIO                          1892 01-SEP-97
WEB$PSOEHL                           1889 01-SEP-97
WEB$RKAMINER                         1886 01-SEP-97
WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97
WEB$TUNTRECH                         1894 01-SEP-97

10 rows selected.

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 MINUS

   2    1     SORT (UNIQUE)
   3    2       COUNT (STOPKEY)
   4    3         TABLE ACCESS (FULL) OF 'X'
   5    1     SORT (UNIQUE)
   6    5       COUNT (STOPKEY)
   7    6         TABLE ACCESS (FULL) OF 'X'



SQL> select * from ( select rownum rnum, x.* from x )   2 where rnum between 991 and 1000;

      RNUM USERNAME                          USER_ID CREATED
---------- ------------------------------ ---------- ---------
       991 WEB$RKAMINER                         1886 01-SEP-97
       992 WEB$JHOFFMAN                         1887 01-SEP-97
       993 WEB$CCOULSON                         1888 01-SEP-97
       994 WEB$PSOEHL                           1889 01-SEP-97
       995 WEB$JBARKER                          1890 01-SEP-97
       996 WEB$CKANDERS                         1891 01-SEP-97
       997 WEB$NRICCIO                          1892 01-SEP-97
       998 WEB$EWISEMAN                         1893 01-SEP-97
       999 WEB$TUNTRECH                         1894 01-SEP-97
      1000 WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97

10 rows selected.

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW

   2    1     COUNT
   3    2       TABLE ACCESS (FULL) OF 'X'


  1 select * from ( select rownum rnum, x.* from x where rownum < 1001 )   2* where rnum between 991 and 1000
SQL> /

      RNUM USERNAME                          USER_ID CREATED
---------- ------------------------------ ---------- ---------
       991 WEB$RKAMINER                         1886 01-SEP-97
       992 WEB$JHOFFMAN                         1887 01-SEP-97
       993 WEB$CCOULSON                         1888 01-SEP-97
       994 WEB$PSOEHL                           1889 01-SEP-97
       995 WEB$JBARKER                          1890 01-SEP-97
       996 WEB$CKANDERS                         1891 01-SEP-97
       997 WEB$NRICCIO                          1892 01-SEP-97
       998 WEB$EWISEMAN                         1893 01-SEP-97
       999 WEB$TUNTRECH                         1894 01-SEP-97
      1000 WEB$TEST_AS_RAJ_AUG4                 1895 01-SEP-97

10 rows selected.

Execution Plan


   0 SELECT STATEMENT Optimizer=CHOOSE    1 0 VIEW

   2    1     COUNT (STOPKEY)
   3    2       TABLE ACCESS (FULL) OF 'X'

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Ed Prochak <prochak_at_my-deja.com> wrote in message news:7qjn29$s4t$1_at_nnrp1.deja.com...
>
>
> The real question in this situation (and I'll ask it this time) ia
> always: WHY?
>
> If you insist on using a relational database as a sequntial file, you
> can expect the performance hits. If you must do this, you might be
> better off dumping the table to a file and using seek() in a C program
> to fetch each one in turn.
>
> ROWNUM is assigned by the DB as the record is fetched. (so even oracle
> doesn't know which row is number 4973 until it has read the 4972 rows
> before it.) ROWID has no real order (ie rowid is practically UNrelated
> to the fetch order) If you insist on doing it from that SQL statement,
> you are stuck. (there's nothing in the ROWID that tells you you are at
> record 1, let alone record 4793).
>
>
> In article <37CE191D.EC82612C_at_comp.polyu.edu.hk>,
> Jimmy <c6635500_at_comp.polyu.edu.hk> wrote:
> > Hello all,
> >
> > I have a table AAA with over 50000 rows. Assume the table
> structure
> > of table AAA is as follows:
> >
> > a number;
> > b varchar2(100);
> > c varchar2(100);
> >
> > Now I want to retireve the N record of this table by using the
> > following SQL statement in SQL*Plus:
> >
> > select whole_str from (select a || b || c whole_str, rownum rowno
> > from AAA) where rowno = &N;
> >
> > It returns a string with A + B + C with record number N in table
> > AAA. However, the time to do this query, as I expected, takes so long
> to
> > run.
> > (in fact, in my case, the table AAA may have over 30 columns)
> >
> > How can I rewrite this SQL statement such that it can improve the
> > query time? Can I get the same results by using rowid? (I think using
> > rowid is the fastest way)
> >
> > Thanks,
> > Jimmy
> >
> >
>
> --
> Ed Prochak
> Magic Interface, Ltd.
> ORACLE services
> 440-498-3700 <<<NOTE new number
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Wed Sep 01 1999 - 12:39:54 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US