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

    Firstly, I would thank all the replies for my posting. I use Thomas's suggestion and gain good performance. Now the time to retrieve the record is shorter than before, nearly can reduce the query time by 5 times.

    In fact, my situation is not on SQL*Plus only. I am developing an Oracle Forms application. I want to develop a Form which can handle all the tables in Oracle database. That is, using the same Form can do the query from all Oracle tables, no need to develop each Form for each table in Oracle. So I create a block item A (20 records displayed) and block item B (more than 100 records displayed). The block item A is stored the column name, the item B is stored the column value.

    This Form is only for data retrieval, but it must contain a button to move Up and Down a record each time. In order to work properly, I must use the above SQL statement to move up and down a record. However, the application doesn't matter the reocrd retrieved order. The order of the records retrieved is different each time, but it is OK. All is ensure that the button can move Up and Down a record on each run. Moreover, another assumption is, during the Forms running, no delete/update/insert is doing the same tables at the same time.

    So I develop this application with the above SQL statement. I am not sure whether it is the best way to do this, but now it's work. Anyone do the similiar thing in Oracle Forms application? I will appreciate it if anyone could give comment on that idea.

Thanks,
Jimmy

Filip Hanik wrote:

> 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 Thu Sep 02 1999 - 23:41:23 CDT