Re: What's the popular techniques to implement search result <<Previous, Next>>?

Thanks for your reply. Pardon me for some more questions.

If I also want the search result to be sorted, what should I do? For example, the following query,
SELECT * FROM USER WHERE ROWNUM BETWEEN 1 AND 10 order by userid selects the first 10 records and then order by name. What I would like to have is to first order the whole user table by userid and then select the rows I want.

I may have to create temporary table (order user by userid) as you have explain in the second case. My question is if the original user table changes, do I have to re-create the temp table, then I can conduct row based query.

Thanks,

Jack (jack.liu_at_visual-x.com)

> Use a pseudo column ROWNUM.For example:
>
> SELECT * FROM MY_TABLE WHERE ROWNUM BETWEEN 1 AND 10
>
> ROWNUM is automatically created by Oracle for every table, so you just
> have to use it. That's all.
>
> If you have a complex query joining several tables, then create a table
> which will contain the result set and select from that table:
>
> CREATE MY_TABLE AS
> SELECT T1.COL1, T2.COL2, T3.COL3, ...
> FROM TABLE1 T1, TABLE2 T2, TABLE3 T3, ...
> WHERE ...
>
> SELECT * FROM MY_TABLE WHERE ROWNUM BETWEEN 1 AND 10
>
> You can substitute variables for 1 and 10 inside your application, and
> every time someone clicks Next, you just increment the variables by 10
> and rerun the select.
>
> If your queries return large result sets that take some time, you can
> improve the performance of the CREATE table AS statement by running it
> in parallel (it's called Parallel Query or PQ, take a look in docs).
>
> hth
>
> Sergey
>
>
> In article <8i85lu$ibb_at_chronicle.concentric.net>,
> "Jack Liu" <jack.liu_at_visual-x.com> wrote:
> > Hi,
> >
> > This may be a general topic question discussed before. But please
 help.
> >
> > I am looking for efficient techniques for this problem:
> > When conducting a database search, it is possible to return thousands
> > of results. Mostly likely the web page only displays the first 10
> > results,
> > and provides link "Next" to the next 10 results. Then if the user
 clicks
> >
> > "Next", the database query should be able to efficiently skip the
 first
> > 10 and reach 11-20 of the search results. What's the common
> > techniques for this problem. I am kind of new to advanced Oracle
> > features.
> >
> > Thanks,
> >
> > Jack
> >
> >
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jun 15 2000 - 00:00:00 CDT