| Cursor versus select into for simple select [message #346222] |
Sun, 07 September 2008 12:00  |
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  |
rleishman
Messages: 3728
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 #346511 is a reply to message #346222] |
Mon, 08 September 2008 14:37 |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
