Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Tree Query question
On Wed, 12 Nov 1997 22:50:21 -0700, Chrysalis <cellis_at_iol.ie> wrote:
>Notes:
>(1) I *always* use an explicit cursor in function/procedure
>definitions rather than using "select ... into ...".
>The result is one fetch instead of two per execution
>of the function.
>
I first heard this said some years ago and thought it was nonsense, so I've been performing some experiments
First try using sys.dual
declare
i number;
dummy char;
begin
for i in 1..100000 loop
select dual.dummy into dummy from sys.dual;
end loop;
end;
/
or
declare
i number;
dummy char;
cursor c is
select dual.dummy from sys.dual;
begin
for i in 1..100000 loop
open c; fetch c into dummy; close c;
end loop;
end;
/
iterations time without cursor time with cursor 100 80 90 1000 321 440 10000 2624 3825 100000 25627 37784
Using an explicit cursor is significantly slower.
The signifcant factor is the time for the second fetch so I generated an extreme case of a 130,000 record table unindexed, with a single numeric column.
declare
i number;
dummy number;
begin
for i in 1..100 loop
select a into dummy from fred where a = 1;
end loop;
end;
/
executes in 44504 ms.
declare
i number;
dummy number;
cursor c is
select a from fred where a = 1;
begin
for i in 1..100 loop
open c; fetch c into dummy; close c;
end loop;
end;
/
executes in 140 ms
Changing the value 1 here to 65536 takes 28151 ms.
In this case, a full table scan must be performed and the cursor method is quicker because it doesn't have to scan the entire table.
More realistically, select into is going to be used to retrieve a single occurence either by primary key or using an aggregate function e.g. max(a).
On an unindexed table:
declare
i number;
dummy number;
begin
for i in 1..100 loop
select max(a) into dummy from fred;
end loop;
end;
/
takes 47148 ms
declare
i number;
dummy number;
cursor c is
select max(a) from fred;
begin
for i in 1..100 loop
open c; fetch c into dummy; close c;
end loop;
end;
/
takes 45986 ms
The cursor version is two percent faster.
However, adding a primary key to the table and doing a search for a key value:
declare
i number;
dummy number;
begin
for i in 1..100000 loop
select a into dummy from fred where a = 1;
end loop;
end;
/
takes 22392 ms
declare
i number;
dummy number;
cursor c is
select a from fred where a = 1;
begin
for i in 1..100000 loop
open c; fetch c into dummy; close c;
end loop;
end;
/
takes 34399 seconds.
These figures are not significantly affected by encapsulating the code in a procedure.
Conclusion
Using an explicit cursor to retrieve a single row is sometimes very
much slower and sometimes a little faster. Only under very artificial
conditions does it provide a significant performance advantage.
Not using an explicit cursor requires less coding and is easier to
follow.
Not using an explicit cursor automatically traps an error where the
programmer wrongly expects only a single row to be returned.
DO NOT USE EXPLICIT CURSORS FOR SINGLE ROW FETCHES. Received on Thu Nov 13 1997 - 00:00:00 CST