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

A copy of this was sent to Jimmy <c6635500_at_comp.polyu.edu.hk> (if that email address didn't require changing) On Thu, 02 Sep 1999 21:41:23 -0700, you wrote:

> 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.
>

what I did once upon a long time ago what this:

created a dummy view with N columns in it. something like:

create view dummy as select substr( 'x', 1, 2000 ) c1, substr( 'x', 1, 2000 ) c2, .... from dual;

I built a default block on it. I made sure the default block referenced the view dummy NOT schema.dummy.

At runtime, I would ask the user for the table name. I would query the data dictionary for the list of columns. I would use that to build a view (say they said "emp") like:

 create view dummy as select ename c1, to_char(empno) c2, ...., to_char(null), c10, to_char(null) c11, ..... from emp;

I would use forms_ddl to create the view.

I would use forms builtins to hide the extra columns.

I would go-block and execute query.

I'm done -- forms does all of the scrolling for me. I don't have to do any more.

Might that work for you?

>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.

--
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 Received on Thu Sep 02 1999 - 09:33:51 CDT