Re: Oracle Myths

> In terms of bind variables, try the following:
>
> declare
> x number;
> begin
> for i in 1 .. 50000 loop
> execute immediate 'select '||i||' into :b1 from dual' into x;
> end loop;
> end;
>
> which is 50000 different SQL's being run...Then try
>
> declare
> x number;
> begin
> for i in 1 .. 50000 loop
> select i into x from dual;
> end loop;
> end;
>
> which is a single SQL being run 50000 times (thanks to bind variables).
>
> hth
> connor

Just to be fair, omit the overhead incurred with execute immediate.

It's still impressive, though!

Rene

-------------->8--------->8----------- cut here

set feedback off
alter system flush shared_pool;

set timing on

declare
 x number;
 begin
   for i in 1 .. &&how_many loop

       execute immediate 'select '||i||' into :b1 from dual' into x;    end loop;
end;
/

set timing off
alter system flush shared_pool;
set timing on

declare
 x number;
begin
  for i in 1 .. &&how_many loop
    execute immediate 'select :i into :x from dual' into x using i;   end loop;
end;
/

set timing off
alter system flush shared_pool;
set timing on

declare
 x number;
begin
  for i in 1 .. &&how_many loop
    select i into x from dual;
  end loop;
end;
/

-- 
Recherchen im schweizerischen Handelsregister: 
http://www.adp-gmbh.ch/SwissCompanies/Search.php3