Re: Avoiding a Double Cursor...

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 29 Sep 2005 14:03:16 -0700
Message-ID: <1128027796.314718.249250@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:03:16 CDT