Re: cursor result return

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 22 Feb 2005 11:20:01 -0800
Message-ID: <1109099821.734170@yasure>

Cris Carampa wrote:

> DA Morgan wrote:
>

>> Sybrand may well be correct about the difference ... but 9 seconds is
>> unbelievably slow.

>
>
> Doesn't it depend upon the number of columns selected? It takes 7
> seconds here (9.0.1.3 on Linux) for a "select * from 4352-rows-table"
> and 1/3 second for a "select 1 from same-table".
>
> Kind regards,

Unbelievably slow.

SQL*Plus: Release 10.1.0.3.0 - Production on Tue Feb 22 11:15:30 2005

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production With the Partitioning, OLAP and Data Mining options

SQL> desc zip_city

  Name                                      Null?    Type
  ----------------------------------------- -------- ------------
  ZIP_CODE                                  NOT NULL VARCHAR2(5)
  STATE_ABBREV                              NOT NULL VARCHAR2(2)
  CITY                                      NOT NULL VARCHAR2(30)
  LAST_CHANGE_BY                                     VARCHAR2(30)
  LAST_CHANGE_DT                                     DATE

SQL> SELECT COUNT(*)
2 FROM zip_city;

COUNT(*)

3887

SQL> set timing on

SQL> DECLARE
   2 TYPE myarray IS TABLE OF zip_city%ROWTYPE;    3 l_data myarray;
   4
   5 CURSOR c IS
   6 SELECT *
   7 FROM zip_city;
   8 BEGIN
   9 OPEN c;
  10 LOOP

  11      FETCH c BULK COLLECT INTO l_data LIMIT 100;
  12      EXIT WHEN c%NOTFOUND;

  13 END LOOP;
  14 END;
  15 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> Of course I cheated as I did this on my single CPU IBM ThinkPad running Windows XP Pro as opposed to a production system. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue Feb 22 2005 - 13:20:01 CST