With regard to the example without a cursor above,
won't you get an exception thrown any time you do the query
and there is no matching data?
andrewst <member14183_at_dbforums.com> wrote in message news:<3011121.1055855941_at_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.
Received on Fri Jun 20 2003 - 12:15:56 CDT
