At 1:07 PM +0200 8/2/01, Joachim Zobel wrote:
>Hi.
>
>One of the really nice features of MySQL (OK, its not a "real"
>RDBMS, but who cares:) is LIMIT.
>
>Using Oracle I have found it a real pain to display search results
>in pages (of eg 20) while using connection pooling. The problem is
>that you can not be shure to repeat the same resultset on every
>page. This gets a real problem if the queries get expensive and I
>can't afford sorting because it destroys first row performance.
You can with a result index.
When your user first gets to the page, dump the rowids and rownums to
a result_index table.
create table result_index (
session_id number(10),
myrowid varchar(30),
myrownum number(10),
mydate date
);
insert into result_index select 123456,rowid,rownum,sysdate from your_table;
The date column is so that you can purge the result index after 24 hours...
delete from result_index where mydate < sysdate - 1;
Then, for the query...
select * from
result_index a,
your_table b
where
a.session_id = ? and
a.myrowid = b.rowid and
a.myrownum between ? and ?
I have not actually implemented this anywhere. I wrote something
similar where I limited based on rownum, but rownum only numbers the
result set, not the table set.
Rob
>Is there a generic soloution to this? Has anybody found this worth
>writing a pattern?
>
>Thanx,
>Joachim
>--
>"... ein Geschlecht erfinderischer Zwerge, die fuer alles gemietet werden
>koennen." - Bertolt Brecht - Leben des Galilei
--
"A good magician never reveals his secret; the unbelievable trick
becomes simple and obvious once it is explained. So too with UNIX."
