Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Tree Query question

Re: Tree Query question

From: Keith Boulton <boulke_at_globalnet.co.uk>
Date: 1997/11/13
Message-ID: <346af89e.7574050@read.news.global.net.uk>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US