| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Retrieve N record from a large table...
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
 |
 |