Re: Avoiding a Double Cursor...

I had always thought that Oracle could/would not guarantee any consistency using rownum(), i.e. using it repreatedly as a limit in a where clause would not always return the expected set of rows?

Since this idea goes back to the DB each time for a new page of data, can you be sure rownum() will get the appropriate set of rows?

Cheers
Gary

"stephen O'D" <stephen.odonnell_at_gmail.com> wrote in message news:1128027796.314718.249250_at_g14g2000cwa.googlegroups.com...
> redrobot5050_at_gmail.com wrote:
> > Another question about web apps built with the PL/SQL web cartridge. We
> > have procedures which display data from the db to the user in a table
> > format. We show the first 45 results, and if the user needs more, there
> > are links to other "page numbers" at the bottom of the table.
> >
> > To properly limit our data, we need a count of all the records that are
> > going to be returned by the query.
> >
> > Since we use weak reference cursors, the rowcount attribute (in Oracle
> > 9i) only tells us which row we're currently on. So we loop through the
> > whole cursor one time, counting up all the rows. Then we close and
> > re-open the cursor, and start iterating through it, displaying rows to
> > the user.
> >
> > Is there a better way to get the total results returned? I have tried
> > wrapping the query in a "SELECT COUNT (*) FROM ( << query goes here >>
> > )" and then executing immediate, but that does not seem to work either.
> >
> > does anyone have suggestions?
> >
> > Thanks
>
> The problem here is that Oracle cannot know how many rows are going to
> be returned with actually getting them.
>
> However, don't loop over all the rows once to get the row count, and
> then again to get the rows you want - that will be very slow.
>
> Create a new query which is
>
> select count(*)
> from <the rest of you query>
>
> ie, the exact same where clause as you actual select. The count(*)
> will be much faster than actually retriving the data.
>
> Then open the original cursor to get the rows you want. The best way
> to limit the rows here is to use rownum - in this was you can bring
> back a 'window' of results from the database:-
>
> 1 select *
> 2 from
> 3 (
> 4 select * from
> 5 (
> 6 select rownum rnum, ename
> 7 from emp
> 8 order by ename
> 9 ) where rnum <= 5
> 10* ) where rnum >= 2
>
> This query will return rows 2 to 5 inclusive.
>
> 1 select *
> 2 from
> 3 (
> 4 select * from
> 5 (
> 6 < ** your entire query goes here including order by ** >
> 9 ) where rnum <= max_row
> 10* ) where rnum >= min_row
>
> Obviously this means you need to have two ref cursors, but it will
> probably be faster.
>
> One other option you could try is:
>
> 1 select *
> 2 from
> 3 (
> 4 select * from
> 5 (
> 6 select rownum rnum, ename, count(*) over ()
> 7 from emp
> 8 order by ename
> 9 ) where rnum <= 5
> 10* ) where rnum >= 2
>
> In this case the total count will be on each row returned, and you
> could grand it on the first pass of processing the results.
> I am not sure if this would be more efficient - but I guess I can test
> it out while I am here:
>
> SQL> create table big_table (id integer, value varchar2(255))
> 2 ;
>
> Table created.
>
> SQL> insert into big_table select object_id, object_name from
> all_objects;
>
> 30314 rows created
>
> SQL> select count(*) from big_table;
>
> COUNT(*)
> ----------
> 30314
>
>
> Statistics
> ----------------------------------------------------------
> 1 recursive calls
> 1 db block gets
> 188 consistent gets
> 0 physical reads
> 120 redo size
> 381 bytes sent via SQL*Net to client
> 503 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed
>
>
> 1 select *
> 2 from
> 3 (
> 4 select *
> 5 from
> 6 (
> 7 select rownum rnum, id, value
> 8 from big_table
> 9 order by value
> 10 ) where rnum <= 45
> 11* ) where rnum >= 1
>
>
>
> 45 rows selected.
>
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 4 db block gets
> 184 consistent gets
> 156 physical reads
> 0 redo size
> 2465 bytes sent via SQL*Net to client
> 525 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 45 rows processed
>
> So thats 184 + 184 = 268 consistent gets for this case.
>
> 1 select *
> 2 from
> 3 (
> 4 select *
> 5 from
> 6 (
> 7 select rownum rnum, id, value, count(*) over ()
> 8 from big_table
> 9 order by value
> 10 ) where rnum <= 45
> 11* ) where rnum >= 1
>
>
> Statistics
> ----------------------------------------------------------
> 0 recursive calls
> 6 db block gets
> 184 consistent gets
> 471 physical reads
> 0 redo size
> 2619 bytes sent via SQL*Net to client
> 525 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 1 sorts (disk)
> 45 rows processed
>
> So, only 184 consistent gets in total - but there are many more
> physical reads here for some reason. I am afraid I am not sure why -
> could it to be with the sorting?
>
> I guess you could try both methods and see which performs better for
> you data and where clause etc.
>
> Stephen.
>
Received on Thu Sep 29 2005 - 16:40:20 CDT