Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Pagination question
lsllcm wrote:
> Hi Steve,
>
> The rownum usage for pagination is like the following sql.
>
> select b.*
> (Select * from A Order by A.Id) b
> where rownum<100
>
> The sql is from asktom site, it scan the index on table A column A.ID,
> and stops when it retrieves the first 100 rows.
>
> But in issued sql, the sql now retrieve the index B3ADDRES_ZIP_IX at
> first, then use nest loop join table B1PERMIT, because the rows return
> from index B3ADDRES_ZIP_IX is 45326, then the nest loop needs more
> time, the same is when we use the hash join.
>
> What do I want is the first 100 rows order by B1PERMIT.B1_FILE_DD and
> meet the search condition on B3ADDRESS.B1_SITUS_ZIP column.
>
> Thanks
> Jacky
Create an in-line view as follows:
(SELECT *
FROM B1PERMIT.B1_FILE_DD
WHERE rownum < 101)
Join it with the other tables(s). Then select from that. An alternative would be to use a PIPELINED TABLE FUNCTION.
Daniel Morgan
www.psoug.org
Received on Fri May 05 2006 - 17:02:48 CDT