Re: count(*) vs. a cursor when determining the existing record on primary key

From: andrewst <member14183_at_dbforums.com>
Date: Tue, 17 Jun 2003 13:19:01 +0000
Message-ID: <3011121.1055855941@dbforums.com>

Originally posted by Ethel Aardvark
> Even a simple "select into" is less efficient than a single cursor.
> Why? If there are two or more rows, the "select into" needs to
> interrogate the DB to find this out to raise the error, the cursor
> method simply does not care.
>
> That said, I gather the PL/SQL and SQL engines are far more tightly
> integrated in version 9.
>
> The key thing that I picked up from my training with Steven was that
> if you are not sure which method is best, create a test-harness and
> try each one out!
>

And if you do, you will find that "select into" actually beats a single cursor fetch! The "don't use select into" rule is a myth.

Here's an example:

SQL> create table t2 as select object_id as id, object_name as name   2 from all_objects
  3 where rownum <= 10000;

Table created.

SQL> alter table t2 add primary key(id);

Table altered.

SQL> analyze table t2 compute statistics;

Table analyzed.

SQL> set timing on
SQL> declare
  2 v_id number;
  3 cursor c is

  4      select id
  5      from   t2
  6      where  id = 264888;

  7 begin
  8 for i in 1..1000 loop

  9      open c;
 10      fetch c into v_id;
 11      close c;

 12 end loop;
 13 end;
 14 /

PL/SQL procedure successfully completed.

 real: 361
SQL> / PL/SQL procedure successfully completed.

 real: 331
SQL> / PL/SQL procedure successfully completed.

 real: 350
SQL> declare
  2 v_id number;
  3 begin
  4 for i in 1..1000 loop

  5      select id
  6      into   v_id
  7      from   t2
  8      where  id = 264888;

  9 end loop;
 10* end;
SQL> / PL/SQL procedure successfully completed.

 real: 280
SQL> / PL/SQL procedure successfully completed.

 real: 261
SQL> / PL/SQL procedure successfully completed.

 real: 250

The "select into" code ran in about 75% of time of the explicit cursor code on average in this test.

--
Posted via http://dbforums.com

Received on Tue Jun 17 2003 - 08:19:01 CDT