Home » RDBMS Server » Performance Tuning » Cursor versus select into for simple select (Oracle 9,10)
Cursor versus select into for simple select [message #346222] Sun, 07 September 2008 12:00 Go to next message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
Hello,

I recently got looked at like I was an idiot when questioning why someone was using a cursor for a simple select into
declare
  CURSOR C1 (P_VAR1 IN VARCHAR2) IS
     SELECT col1
     FROM some_table
     WHERE col1=p_var1;
begin
  OPEN C1 (P_VAR1);
  FETCH C1 INTO V_VAR1;
  CLOSE C1;

versus
select col1 into v_Var1 where col1=p_var1;


Supposedly the cursor is better for parsing , precompiling, etc...

Is this something he came to believe from older versions of oracle?
Can anyone tell me if it is better?
Should I look at him like he is an idiot tomorrow?

I ran a simple test and select col1 into v_var1... performed better but that does not test repetitive parsing since the same command is being repeated. Perhaps should have tested with 1 execution.

Sorry if question in wrong place, already posted, etc..

Thanks.

Re: Cursor versus select into for simple select [message #346235 is a reply to message #346222] Sun, 07 September 2008 21:32 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This is related to an old myth that implicit cursors perform an extra fetch to check for TOO_MANY_ROWS error.

This was - and still is - true of the pre-compilers (eg. Pro*C), but it is not true for PL/SQL. There is also reasonable evidence that it was NEVER true for server-side PL/SQL.

Note that it is possibly true for client-side PL/SQL (such as in SQL*Forms). I saw someone mention this just recently in the Forum.

None the less, it is a VERY pervasive myth re server-side PL/SQL. Expect some pushback when you try to convince them they are wrong.

Ross Leishman
Re: Cursor versus select into for simple select [message #346242 is a reply to message #346222] Sun, 07 September 2008 22:14 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
>I recently got looked at like I was an idiot when questioning why someone was using a cursor for a simple select into
Alternatively, they could be asked to provide a reproducible benchmark that substantiates their position.
Re: Cursor versus select into for simple select [message #346511 is a reply to message #346222] Mon, 08 September 2008 14:37 Go to previous messageGo to next message
harrysmall3
Messages: 109
Registered: April 2008
Location: Massachusetts
Senior Member
I was one as well under the "spell of the myth".

Just wanted to point out that if it is intended to compare
the cursor vs. the singleton select then we are talking about
a select that only returns absolutely one value, otherwise
the TOO_MANY_ROWS exception.

In which case, the comparisson is the explicit cursor to
FOR C1 IN SELECT COL1 FROM ...

Where it has been shown via experiment to me at this forum that the Implicit FOR cursor starting in 10g carries also an
implicit BULK FETCH of 100 rows which is truly significant for performance.

I very much like anacedent's response as to how you should handle the scrutiny received on this inquiry!

Regards,
Harry



Re: Cursor versus select into for simple select [message #346747 is a reply to message #346511] Tue, 09 September 2008 10:45 Go to previous message
goo321
Messages: 28
Registered: June 2008
Location: houston
Junior Member
Thanks all.
Previous Topic: Before Asking the Index Question
Next Topic: A performance test of Oracle Analtyicals vs Group By
Goto Forum:
  


Current Time: Mon Dec 05 04:49:39 CST 2016

Total time taken to generate the page: 0.41759 seconds