Why this Difference
Senthil Kumar S, April 11, 2002 - 7:55 am UTC
Hi Tom,
I am a regular reader of your valuable questions. I have gained a lot from this site.
Well, please have a look at the following block of code.
DECLARE
TYPE name_num_rc IS REF CURSOR;
cur_1 name_num_rc;
rec1 emp.ename%type;
rec2 emp.ename%type;
cursor cur_2 is select ename from emp order by ename;
BEGIN
open cur_1 for select ename from emp order by ename;
fetch cur_1 into rec1;
LOOP
exit when cur_1%notfound;
dbms_output.put_line('Name1 '|| rec1);
fetch cur_1 into rec1;
end loop;
dbms_output.put_line('***********************************' );
for i in cur_2 loop
fetch cur_2 into rec2;
dbms_output.put_line('Name '|| rec2 );
end loop;
end;
/
I find a difference in number of rows obtained from the above block. I could find no bug/error. Please let me know why I am unable to see it.
Thanks in Advance
Senthil
( Sorry for filling up this section with a query, but I could not do other than this)
April 11, 2002 - 9:18 am UTC
for i in cur_2 loop
fetch cur_2 into rec2; <<<<====================
dbms_output.put_line('Name '|| rec2 );
end loop;
remove that. you are double fetching, skipping every other record in effect.
This is my preferred way to code what you have above (well, except for the CURSOR CUR_2 is part... i would never do it that way, i would use for i in ( select... :
1 DECLARE
2 TYPE name_num_rc IS REF CURSOR;
3 cur_1 name_num_rc;
4 rec1 emp.ename%type;
5 cursor cur_2 is select ename from emp order by ename;
6 BEGIN
7 open cur_1 for select ename from emp order by ename;
8 LOOP
9 fetch cur_1 into rec1; <<<<====== fetch here, not outside the loop
10 exit when cur_1%notfound;
11 dbms_output.put_line('Name1 '|| rec1);
12 end loop;
13 dbms_output.put_line('***********************************' );
14 for i in cur_2
15 loop <<<<================= don't fetch here, don't define a record!
16 dbms_output.put_line('Name '|| i.ename );
17 end loop;
18* end;
scott@ORA817DEV.US.ORACLE.COM> /
Name1 ADAMS
Name1 ALLEN
Name1 BLAKE
Name1 CLARK
Name1 FORD
Name1 JAMES
Name1 JONES
Name1 KING
Name1 MARTIN
Name1 MILLER
Name1 SCOTT
Name1 SMITH
Name1 TURNER
Name1 WARD
***********************************
Name ADAMS
Name ALLEN
Name BLAKE
Name CLARK
Name FORD
Name JAMES
Name JONES
Name KING
Name MARTIN
Name MILLER
Name SCOTT
Name SMITH
Name TURNER
Name WARD
PL/SQL procedure successfully completed.
Then Why ??
A reader, April 13, 2002 - 8:14 am UTC
Tom,
Then why is Forms When asked to generate the Code for The Validation Events Write Tem Using Explicit Cursors.
Your comments pls.
Regards,
Ganesh R
April 13, 2002 - 10:04 am UTC
Because I did not write it I suppose. To each their own.
There is no disputing the *facts* that:
o implicit cursors are easier for my fingers to type in
o implicit cursors result in less code
o implicit cursors are faster
YOU make the choice when YOU write the code.
Somewhat silly comparison
Michael O'Neill, June 12, 2002 - 3:44 pm UTC
Someone sent me the link to this "benchmark" to prove to me that implicit cursors were not only easier to use but that they were also faster.
What your benchmark does that is silly, in my opinion, is open and close the cursor needlessly in the explicit cursor sample. If one puts the OPEN and CLOSE before and after the LOOP, the results are dramatically different. Opening and closing cursors outside of the loop is the norm. I've never opened and closed a cursor inside the same loop I was fetching iteratively from.
On my box the improvement was 750-fold by moving the opening and closing of the cursor outside the loop.
I think this "benchmark" misleads many about the value of using explicit cursors in the code. For quick and dirty work implicit is fine, but for most long-use code, explicit will yield superior performance.
June 13, 2002 - 7:54 am UTC
Dude,
If one puts the OPEN and CLOSE outside the loop -- one has a HUGE BUG in their code. They have made an error, they have not thought about what they heck they were doing (opps -- sorry -- that would be you)
If you take this:
scott@ORA716.WORLD> declare
2 cursor c1 is select * from dual;
3
3 l_dummy dual.dummy%type;
4 l_start number default dbms_utility.get_time;
5 begin
6 for i in 1 .. 10000
7 loop
8 open c1;
9 fetch c1 into l_dummy;
10 close c1;
11 end loop;
12 dbms_output.put_line
13 ( 'Elapsed ' ||
14 round( (dbms_utility.get_time-l_start)/100, 2 ) );
15 end;
16 /
Elapsed 12.18
and code it as this:
scott@ORA716.WORLD> declare
2 cursor c1 is select * from dual;
3
3 l_dummy dual.dummy%type;
4 l_start number default dbms_utility.get_time;
5 begin
6 for i in 1 .. 10000
open c1; 7 loop
9 fetch c1 into l_dummy;
11 end loop;
close c1; 12 dbms_output.put_line
13 ( 'Elapsed ' ||
14 round( (dbms_utility.get_time-l_start)/100, 2 ) );
15 end;
16 /
you know what you just did?
you fetched 10000 times from a cursor that returns 1 record. 9,999 of the FETCHES DID NOTHING, RETURNED NO DATA -- IN FACT -- Oracle is so smart, it didn't even bother to do a context switch to the kernel for that -- it KNEW the result set was empty. That is why you got 750 times faster -- when you do NOTHING, it goes faster then doing SOMETHING If you want to compare 10,000 "select intos" versus explicit cursor fetches -- guess what?
YOU HAVE TO OPEN and CLOSE a cursor 10,000 times!!!!! Think about itYour
supposed improvement is a bug in your code, does not work. Does not fetch any data. Doesn't do squat.
I KNOW your benchmark is fatally flawed and erroneous. It is the thing that misleadsFor quick and dirty, for production quality, for the long haul, for EVERYTHING -- implicit cursors rule. They are:
o faster
o easier to code
o less error prone (as you just so nicely demonstrated for all of us)
A bunch of us had a discussion on this "somewhat silly comparison" on the newsgroups.
You can see the crux of the discussion here:
</code>
http://groups.google.com/groups?hl=en&lr=&ie=UTF8&oe=UTF8&selm=9jpff01657%40drn.newsguy.com&rnum=9
Someone tried to make the same argument you did. To see him get beat soundly about the head by others pointing out the same deficiency (that his code quite simply is a really huge BUG)
http://groups.google.com/groups?hl=en&lr=&ie=UTF8&oe=UTF8&threadm=44ff9953.0107241139.496f2ddf%40posting.google.com&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF8%26oe%3DUTF8%26selm%3D44ff9953.0107241139.496f2ddf%2540posting.google.com <code>
is the entire thread.
I take alot of time before posting a benchmark. I do not concoct examples to prove MY point (eg: i try to not be biased, try not to prove my position but rather try to show scienticially that this is the WAY IT IS).
You just did not think this one thru at all.
Erroe code for benchmarking
Kumar, June 13, 2002 - 9:49 am UTC
declare
cursor c1 is select * from dual;
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 10000
open c1;
loop
fetch c1 into l_dummy;
end loop;
close c1;
dbms_output.put_line
( 'Elapsed ' ||
round( (dbms_utility.get_time-l_start)/100, 2 ) );
end;
/
When u try to execute the above the code from sqlplus i get an error
ERROR at line 7:
ORA-06550: line 7, column 10:
PLS-00103: Encountered the symbol "OPEN" when expecting one of the following
* & - + / at loop mod rem <an exponent (**)> ||
The symbol "loop was inserted before "OPEN" to continue.
ORA-06550: line 14, column 7:
PLS-00103: Encountered the symbol ";" when expecting one of the following:
loop
So i changed to
declare
cursor c1 is select * from dual;
l_dummy dual.dummy%type;
l_start number default dbms_utility.get_time;
begin
for i in 1 .. 10000
loop
open c1;
fetch c1 into l_dummy;
--dbms_output.put_line( 'Elapsed ' ||round( (dbms_utility.get_time-l_start)/100, 2 ) );
close c1;
end loop;
dbms_output.put_line( 'Elapsed ' ||round( (dbms_utility.get_time-l_start)/100, 2 ) );
end;
Now it compiles.
The Reason i am asking this bcos i have seen somebody benchmarked the above code by placing open cursor statment before the loop,i want to know how does it compile.
June 13, 2002 - 10:21 am UTC
You missed the LOOP keyword after 10,000
Btw:
loop
fetch c1 into l_dummy;
end loop;
is what we call "an infinite loop". You need:
loop
fetch c1 into l_dummy;
exit when c1%notfound;
end loop;
If they put the OPEN before the for i in 1 .. 10000 loop -- they just
a) wasted their time
b) have meaningless numbers
c) ran a big ol buggy piece of code
This code:
for i in 1 .. 10000
open c1;
loop
fetch c1 into l_dummy;
end loop;
close c1;
is SO WRONG.
Array fetch of 2 rows
Sam, February 04, 2003 - 4:20 pm UTC
Tom,
You mentioned PL/SQL will automatically set array size to 2 for implicit cursors. Is this feature introduced in a recent release? In the newgroup discussion link you posted, you and others didn't make this argument. Instead, the suggestion is to add "rownum = 1" to the query. Does pl/sql actually look at the query and figure out if there is a possibility of more than 1 row being returned? Similarly, if I have an implicit cursor "select 1 into my_var from dual where 1=0", does pl/sql even try to execute the query? Or does it immediately throws the "No Data Found" exception?
Thank you very much.
February 04, 2003 - 4:35 pm UTC
for SELECT INTO type cursors.
it has been so for many years:
tkyte@ORA716> alter session set sql_trace=true;
Session altered.
tkyte@ORA716>
tkyte@ORA716> declare
2 l_x dual.dummy%type;
3 begin
4 select dummy into l_x from dual look_for_me;
5 end;
6 /
PL/SQL procedure successfully completed.
SELECT DUMMY
FROM
DUAL LOOK_FOR_ME
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 2
Fetch 1 0.00 0.01 2 1 3 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.01 2 1 3 3
Misses in library cache during parse: 1
Optimizer hint: CHOOSE
Parsing user id: 10 (TKYTE) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT HINT: CHOOSE
1 TABLE ACCESS (FULL) OF 'DUAL'
see, one fetch -- if it did not ask for 2 rows, we would see 2 fetches there -- one to get the row and one to make sure another row does not exist
The "where rownum=1" is a way to make it so that code:
open c;
fetch c;
close c;
can be replaced with
select into from ...
without getting a TOO_MANY_ROWS error. If you just want the first row from a result set, just select into with ROWNUM=1.
It is the SELECT INTO semantics that tell PLSQL what to do.
PLSQL executes that query with the 1=0, only the SQL engine is smart enough to know it needs to do nothing.
Array fetch of 2 rows
Sam, February 04, 2003 - 4:25 pm UTC
Please ignore my previous comment, I wasn't reading the newsgroup posting carefully. Sorry for the unnecessary posting.
Somewhat Silly II
Michael O'Neill, June 29, 2006 - 2:08 am UTC
This is actually the first time actually reading your followup. I was doing some Google searching technique testing and ended back here after all these years.
I can absolutely state that I understand where my version of an explicit cursor test was pointless and non-comparable to the implicit test of the same.
Each and every point you had made about implicit cursors was true in every way then and even more so now.
Perhaps the only meaningful reason for using explicit cursors is purely an exercise in understanding how a database works - but not in any production code of worth.
Saying that, I would like to point out that my original post used the word "silly" and was not intended to be as derisive as you obviously took it, Frankly, your response was completely rude. Good luck with all that venom.
I think you're obviously brilliant, but I don't think I'll ever see asktom.oracle.com in a positive light again.
Cheers,
Michael
June 29, 2006 - 7:23 am UTC
sorry if you feel that way.
You came
You posted a bogus "test"
You called my example "silly"
Think about it, attitude meets attitude. You posted a totally bogus, incorrect, buggy concept and said "ah hah, see - your 'benchmark' is silly, we are 750 times faster!
Good luck with your own venom?
Cool Michale
Suren, June 29, 2006 - 9:41 am UTC
Hey Michale Cool Buddy, i dont think tom or who ever answers our questions here are Rude, they may respond with some unwanted statments for a post but thats not intended for hurting any one, by enlarge it makes us understand and remeber for long what mistakes we were doing...
So Cool Pal Ask Tom is the Best site for every individual even Remotely connected to Oracle.
If we dont come here there nothing for this site to loose and we are bound to loose lots of Tips, Tricks,Code,Upgrades,Solutions etc.This site makes me feel confident on my Oracle skills.
Think About it Attitude meets Attitude
Being wrong is not "attitude"
Michael O'Neill, June 29, 2006 - 1:46 pm UTC
I don't agree that "attitude was met with attidue" then or now.
My post, years ago, was my honest perception that the benchmark was "somewhat silly" (how provacative?) and then yesterday my attitude was very conciliatory. I was wrong on each of my technical points, however I don't believe I was ever arrogant or rude. I was met by both on each follow up.
I hope and expect that everyone has a fine time during Tom's upcoming Dallas presentation next month. I will not be attending, nor will any of my staff.
I found this thread most useful, indeed.
Michael
June 29, 2006 - 1:56 pm UTC
...
Somewhat silly comparison June 12, 2002 Edit Followup
Reviewer: Michael O'Neill from Garland, TX
Someone sent me the link to this "benchmark" to prove to me that implicit
cursors were not only easier to use but that they were also faster.
What your benchmark does that is silly, in my opinion, is open and close the
cursor needlessly in the explicit cursor sample. If one puts the OPEN and CLOSE
before and after the LOOP, the results are dramatically different. Opening and
closing cursors outside of the loop is the norm. I've never opened and closed a
cursor inside the same loop I was fetching iteratively from.
On my box the improvement was 750-fold by moving the opening and closing of the
cursor outside the loop.
I think this "benchmark" misleads many about the value of using explicit cursors
in the code. For quick and dirty work implicit is fine, but for most long-use
code, explicit will yield superior performance.
.................
"somewhat silly comparision" - not provocative?
"benchmark" - air quotes - as if the "benchmark" was something "not so smart"
"What your benchmark does that is silly" - not provocative?
"I think this "benchmark" ..." - air quotes again...
The absolutely "you are so wrong, explicit cursors are the only way to go" mistatement.
To me, all of that had to be met a little, well, forcibly. Sorry if you didn't like the tone - I've read and re-read it and would say it frankly in the same way in response to your comment.
You made the claim that I was misleading people, demonstrating the wrong way to do things - that was not the case and I would respond exactly the same again.
When you use the terms "silly", "misleads" - well, not sure what kind of reaction you think you might get.
I agree being wrong is not an attitude.
Calling things silly
Air quotes around words
those would be - no?
Alexander the ok, June 29, 2006 - 3:16 pm UTC
Tom I saw this and thought I'd point out if you're annoyed by air quotes you my want to chill out with them as well. I'm one of your biggest fans but you let em fly. Top of this page, on the homepage about your blog ("not")...
I could care less, but I figured someone should speak before you get accused of being hypocritical.
June 29, 2006 - 3:26 pm UTC
I use air quotes all of the time - on purpose.
I'm not being hypocritical at all. Pointing out that air quotes carry meaning is not hypocritical. If I said "people that use air quotes ....", that would be hypocritical. Air quotes are important.
I'm not annoyed by them, I read them for what they are. Like :) conveys a certain meaning.
Flame
Amit, June 29, 2006 - 3:20 pm UTC
I don't know if it is good etiquette to jump into a flame... but, while I can understand why Michael won't join Tom's conference, it is totally beyond me, why he would stop his staff from attending.
Insecurity
Warren, June 29, 2006 - 3:51 pm UTC
Insecurity. The staff may learn information that the boss doesn't know or that proves him wrong.
Imagine your boss drilling into you "you must use explicit cursors" from day one on the job. Then, he is shown why this is an incorrect statement. Do you think the boss would want their staff to know that what they have been telling the staff incorrect information for so long? What else might the boss have wrong?
Come on. Let 'em go. They will actually learn something!
"flame"
Michael O'Neill, June 29, 2006 - 4:06 pm UTC
I would hardly call this a flame. As far as I can tell Tom and I are trying to make our points and we seem to be listening to eachother. Clearly we don't agree, but that isn't a flame.
I understand much better *why* I received the original reaction I did. I definitely took a charge at Tom's point and didn't have the bullets to back it. My charge was intended to be far less provacative than it was by any reasonable interpretation; I see that now. Also, I don't see venom in Tom's follow up, just a littl overkill; a simple "Hey you need to take a second look. fella" probably would sufficed for me - but I understand I'm not the only recipient of the follow up.
Tom was generous enough to offer clarity, and I was obliged to offer the same. Still, I think I will stay away because of it, at least for a while.
Have a great holiday!
Michael
Contractor vs Employee
ToePick, June 30, 2006 - 12:34 am UTC
Your input would be appreciated. I have noticed that as a "contractor" I get alllll the good stuff and people listen, BUT as an employee I get the "not so fun stuff" make it work 'better' and nobody listens. Is it me?
Michalle : If you wanna stay away pls do so.
A reader, June 30, 2006 - 1:02 am UTC
Michalle,
By printing all your fight up here, even after your question is answered. Dont you think it is not necessary for all this.
If I was in this position, I would not have wasted other's time. By reading this thread I think I wasted 30 minutes of mine, sure others would have felt the same.
If you really wann fight , can you pls mail Tom personally !! "...."
A reader, June 22, 2010 - 11:15 am UTC
Hi Tom,
""IMPLICIT cursors rule. They are faster, they are easier.
The "double" hit is optimized away. Internally, it does an array fetch and askes for 2
rows. The whole round trip processing with the select into is less then the explicit
cursor. Not only that but you have less plsql code -- which is faster as well. The more
that can be done for you in C the better""
Is it the same in Oracle 11g also ? is implicit curosr is faster than explicit cursor in 11g ? are they both same performance wise? and also can you please explain with the reasons if implicit cursor is faster than explicit ?
thanks for your time
June 22, 2010 - 1:55 pm UTC
yes. test it for yourself.
we already explained why.
Parameterized query
Bharat Kaushik, June 26, 2010 - 3:05 pm UTC
To Tune the SQL you segest that we must use like below
for c in ( select * from a )
loop
In place of below
declare
cursor c in select * from a;
begin
open c;
<do something here>;
close c;
end;
If I have to fire a parameterized query, something like below
select * from a where col1=1, col2=2;
Above thing I know can be done using parameterized explicit cursor as below.
declare
cursor c ( p_col1 in number, p_col2 in number )
in
select * from a where col1=p_col1 and col2=p_col2;
begin
open c;
<do something here>;
close c;
end;
Can we do it using implicit cursor as well?
Query regarding Explicit cursor
Bharat Kaushik, June 29, 2010 - 6:13 pm UTC
Hi Tom,
My last query may not be clear, adding more clarification in it.
Actually for parameterized query as well, to make it an implicit cursor I can do like below,
for c in ( select * from a where col1=1, col2=2)
loop
This will make it an Implicit cursor only, but in my scenario I have nesting of 2 SQL's, I am getting the values of col1 and col2 from first SQL and I am passing it to second SQL inside loop, so there are no specific values to be passed, values for col1 & col2 will keep on changing with each 7 every iteration.
That is why I am serching for a way I can parameterize the implicit cursor.
Can this be done by any way?
Thanks in advance.
Best Regads,
Bharat