Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Pagination question

Re: Pagination question

From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 05 May 2006 15:02:48 -0700
Message-ID: <1146866571.572287@bubbleator.drizzle.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US