Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Sudhanshu.

Asked: July 18, 2002 - 10:07 am UTC

Last updated: March 29, 2009 - 10:43 am UTC

Version: 8.1.7

Viewed 1000+ times

You Asked

Hi Tom,

I have visited the Explicit & Implicit cursor archive question in this forum.

Is it true that Oracle 7.3 onwards implicit cursors are optimized and they don't do double fetch Or Does they do an internal array fetch.

The following implicit cursor runs faster than the below explicit if I table blah have an index on x column otherwise explicit runs faster.

Could you please tell me the reason for this awkard behavior ?

select x
into y
from blah
where x = j;
--------------------------------
cursor c(p number) is
select x
from blah
where x = p;

for j in 1 .. 999 loop
open c(j);
fetch c into y;
close c;
end loop;



Cheers
Sudhanshu


and we said...

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1544606261686 <code>

shows going way back to 7.1 (and 7.0 but I don't have that installed) this has been so.

tkyte@ORA716.WORLD> variable x varchar2(25)
tkyte@ORA716.WORLD>
tkyte@ORA716.WORLD> alter session set sql_trace=true;

Session altered.

tkyte@ORA716.WORLD>
tkyte@ORA716.WORLD> exec select * into :x from dual HELLO_THERE;

PL/SQL procedure successfully completed.

tkyte@ORA716.WORLD> print x

X
--------------------------------
X

tkyte@ORA716.WORLD>


and tkprof clearly shows:


SELECT *
FROM
DUAL HELLO_THERE
...
Fetch 1 0.00 0.00 0 1 3 1
....

one fetch call -- not two.



The reason for your behavior is because you are comparing an apple with a toaster oven. Compare:

for j in 1 .. 999 loop
open c(j);
fetch c into y;
if ( c%notfound ) then raise NO_DATA_FOUND; end if;
fetch c into y;
if ( c%found ) then raise TOO_MANY_ROWS; end if;
close c;
end loop;


The select into looks for the first two matching rows -- it looks to the end of the table to ensure that you are getting at least one and at most one row (that is what select into it for).

Your test -- it would find the first row and STOP. If the data was physically organized so that your data was "near the top" of the table -- it would run much faster. If it was such that your data was "near the bottom" of the table -- it would run the same speed.

But -- test apples vs apples and you'll see that the implicit cursor is faster (when you compare it to the same logic procedurally)


If you care just about getting the first row, use:

select x in to y from blah where x= j AND ROWNUM=1;

it'll be faster then your loop

Rating

  (55 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Always useful of course!

Chuck Jolley, July 18, 2002 - 3:05 pm UTC

Is there a general rule about how much faster the implicit cursors are?
I have a few proc that are >1000 lines long that I use explicit cursors in for clarity.
It puts the SQL at the top and the logic in the body.
(wheels within wheels)
They are only run by DBAs, not users, as they take between 30 min and one hour.
I am perfecly willing to rewrite them, but will it pay?
thanks,
chuck



Tom Kyte
July 18, 2002 - 5:09 pm UTC

Well, I use implicit cursors myself -- for clarity.


begin
open c1;
fetch c1 into x;
close c1;
end;


begin
select count(*) into x
from t
where .....;
end;

to me -- the second is more "clear". I don't have to constantly page up to see "what the heck do that cursor do again"...

the only thing you can do -- benchmark.

My 2cents -- you have the code, the code works, you seem "happy" or at least OK with the performance -- it ain't broke -- this won'[t make it run 10X faster, i would leave it be.

cursor for loop: explicit or implicit?

Freek D'Hooge, July 18, 2002 - 3:34 pm UTC

Tom,

the last couple of days there is an ongoing discussion on technet </code> http://www.oracle.com/forums/message.jsp?id=1126873&gid=478021 <code>about implicit cursor vs explicit cursors. (the question about the indexes was original also posted there).
For me it is clear that implicit cursors are faster then explicit ones, but is the following a implicit cursor or an explicit one:

DECLARE
cursor c_cur is
select * from t;
BEGIN
for r_cur in c_cur loop
do something;
end loop;
END;

I know you look at this as an implicit cursor, but according to oracle documentation it is an explicit one?

Tom Kyte
July 18, 2002 - 5:14 pm UTC

it is a bit of both. I tend to call it implicit because there is no OPEN/FETCH or CLOSE. But it falls into the definition of both.


The docs state:

....
Explicit Cursors

The set of rows returned by a query can consist of zero, one, or multiple rows,
depending on how many rows meet your search criteria. When a query returns
multiple rows, you can explicitly declare a cursor to process the rows. Moreover,
you can declare a cursor in the declarative part of any PL/SQL block, subprogram,
or package.


You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you
initialize the cursor with the OPEN statement, which identifies the result set. Then, you can execute FETCH repeatedly until all rows have been retrieved, or you can use the BULK COLLECT clause to fetch all rows at once. When the last row has been processed, you release the cursor with the CLOSE statement. You can process
several queries in parallel by declaring and opening multiple cursors.


....

Explicit Implicit Cursors in Forms

Gary Myers, July 18, 2002 - 11:57 pm UTC

Note 1003695.6 in Metalink and a recent Forum posting state that in forms "A separate cursor is allocated for each implicit cursor, and implicit cursors are kept open until the form is exited. "

In Forms 6i, the cursors seem to be closed at the completion of the trigger.
{ I tried the following in a New Form trigger :
declare
v_temp varchar2(2000);
cursor c_1 is select 3 from dual;
cursor c_2 is select 4 from dual;
begin
select 1 into v_temp from dual;
select 2 into v_temp from dual;
open c_1;
fetch c_1 into v_temp;
close c_1;
for c_rec in c_2 loop
null;
end loop;
message(v_temp);
pause;
end;

The cursors were in visible v$open_cursor at the PAUSE, but vanished after that, while the form was still open.).

I recall when using Forms 5.0, needing a large MAX_OPEN_CURSORS database parameter because of forms cursors.

Did this behaviour change in 6i or earlier ?
Are there any problems in keeping a large MAX_OPEN_CURSORS ?

Tom Kyte
July 19, 2002 - 7:26 am UTC

they are cached, they are replaceable. You want large max open cursors for performance more then anything else.

It consumes more memory to have more open cursors -- that is the "downside"

ROWNUMskull

Mike, July 19, 2002 - 1:05 pm UTC

Tom: in this thread you state:

> If you care just about getting the first row, use:
>
> select x in to y from blah where x= j AND ROWNUM=1;

I may be taking this out of context but whenevery I see ROWNUM I pause (just as you might when seeing "WHEN OTHERS THEN"). I thought ROWNUM could lead to unpredictable results as the row selected may have nothing to do with the ORDER BY criteria.

I'm not trying to nit pick you, rather I've been reading the explicit vs. implicit cursor discussion and learned a ton. However, I'm still undecided on the safest technique for selecting only the first row of a multi-row result set. Both implicit or explict cursors work but I'm left wondering if explicit is not safer (e.g. not subjet to handling behavior changes in new releases). Your ROWNUM idea is another approach but not if results are unpredictable. Pondering...

Thanks

Tom Kyte
July 19, 2002 - 3:29 pm UTC

rownum.

It is as predicable as

open
fetch
close

and in fact will tend to return the same exact thing.

Rownum does work predicably with order by. If you code:

select * from t where rownum <= 5 order by x;

that says "take 5 rows, sort them by x"

if you code:

select * from ( select * from t order by x ) where rownum <= 5;

that says "order t by X and then take the first 5 -- AFTER the sort".

It is 100% predicable and reliable.

ROWNUM num num

Mike, July 19, 2002 - 6:00 pm UTC

Ahhh, the lights come on. Thank you sir for the ROWNUM goodies!

I'm still curious how you, in general, code a cursor that returns 0, 1 or more ordered rows but you only care about the first row returned. I see three aproaches:

open
fetch
close
-- Intention is clear.

--------------------------------------------

select * from ( select * from t order by x )
where rownum = 1 into l_rec;
-- Is this a potential maintence programmer
-- chuck hole?

--------------------------------------------

<<myloop>>
for rec in (select * from t order by x)
loop
l_first_row_stuff_to_keep := rec
exit myloop; -- premature termination
but has "goto" feel and
seems inappropriate.
end loop;

Curious about your thoughts on these options (or others I've overlooked). Thank you.

Tom Kyte
July 20, 2002 - 10:25 am UTC

select * into l_rec
from ( select * from t order by x )
where rownum = 1;

is

a) infinitely faster then open/fetch/close in most every case (at least as fast, generally much faster)

b) should be obvious "rownum=1" seems *obvious* to me. If not code it as:

-- Hey, look at that rownum = 1, the INTENT here is
-- to get the first row after ordering. Since we want
-- this to be FAST, we don't use the procedural approach of
-- open/fetch/close
select * into l_rec
from ( select * from t order by x )
where rownum = 1;


Now, maintenance programmer should be AOK.




Thanks Tom

Mike, July 20, 2002 - 2:09 pm UTC

Just wanted to say thanks for your experienced perspective on these details. Also, a HUGE tip-o-the-hat on your "Expert One on One" book -- it's truly a superb text.

I have a ton of Oracle/Unix books in my cubicle but YOURS is always the one on my desktop cracked open and in use. Of course, I have to keep it cracked open so I don't see your mug on the cover all day :-)))))) Just kidding and thanks again!

Helena Markova, February 13, 2003 - 8:27 am UTC


Might want to let Oracle know about this :-)

Rick, March 31, 2003 - 2:37 pm UTC

Oracle Applications Developers Guide Release 11i, SQL Coding Guidelines:
"All SELECT statements should use an explicit cursor. Implicit
SELECT statements actually cause 2 fetches to execute: one to get
the data, and one to check for the TOO_MANY_ROWS
exception. You can avoid this by FETCHing just a single record
from an explicit cursor."

Thanks for clearing up myths and providing informed, practical information.

Implicit cursor in Pro*C

Tony, July 18, 2003 - 12:29 am UTC

Is it possible to use implicit cursor in Pro*C? If yes, can you give an example?

Tom Kyte
July 18, 2003 - 1:46 am UTC



exec sql select count(*) into :n from dual;



is this right way ?

Deepak Gupta, September 11, 2003 - 7:46 am UTC

Hi Mr. Tom,

I am doing payroll processing in following way :-

I find there are too much phy i/o

The logic like this
( main loop select valid employee and for each employee further information is fetch through "select ... into" clause from different tables )

What do u suggest me ?

Is this right way ?


--------

declare
cusor is SELECT EMPLOYEE_NUMBER
FROM EMPLOYEE_MASTER
WHERE STATUS = 'A';

XEMP VARCHAR2(5);

begin

OPEN EMP
LOOP
FETCH EMP INTO XEMP;
EXIT WHEN EMP%NOTFOUND;

select BASIC,HRA....... INTO ....
FROM SALARY_MASTER
WHERE EMPLOYEE_NUMBER=XEMP;

select PAID_DAYS INTO ....
FROM MONTH_PAID_DAYS
WHERE EMPLOYEE_NUMBER=XEMP;

select LOAN_AMT INTO ....
FROM LOAN_MASTER
WHERE EMPLOYEE_NUMBER=XEMP;


select INCOME_tAX INTO ....
FROM INOCMETAX_MASTER
WHERE EMPLOYEE_NUMBER=XEMP;

---
XMONTHLY_SALARY_PAID = XBASIC+XHRA ......
XMONTHLY_SALARY_DED = xINCOMETAX+xLOAN_dED....

......

.....

END ;

Thanks + Regards



Tom Kyte
September 11, 2003 - 8:45 am UTC

one word for you


join.

Best approach when NOT sorting?

Kurt, December 11, 2003 - 10:49 am UTC

Tom,
This question is similar to the original question by Sudhanshu - I'm trying to find the "best" way to get the first row from a query. The difference with my query is that the data does not need to be sorted.

I need to find any record that satisfies a query, and return an ID from that record. The query seeks to detect "bad" records, and if I didn't need to actually return the ID, I'd try using "where exists" rather than the 3 approaches below. I don't need to sort the records in the query because I don't care which record is returned. The query can return 0, 1, or more records. I ignore the possibility of NO_DATA_FOUND in this comparison test, but I'm handling it in the real code.

I've tried to execute a simple test below using 3 different approaches - explicit cursor, implicit cursor, select into where rownum=1 (which I believe is a specialized case of implicit cursor).

In the real application, the query would join 2 tables and would include other predicates as well. I'm hypothesizing that the actual query is irrelevant for the purpose of this comparison test, as long as the query is identical (or as close as possible) in all three approaches. So the query I use below is simple.

Here are the statements used to create the objects and trace the statements, and below that are the results of TKPROF.

I'm using Oracle EE 9.2.0.1.0 on Windows2000 Pro - my workstation at home.

Please take a look and consider the questions at the end. Thanks.

--***********************************************

create table t as
select * from all_objects;

alter table t
add constraint t_pk primary key (object_id);

exec dbms_stats.gather_schema_stats(user);

-- Use an explicit cursor
create or replace function f1
return t.object_id%type
is

l_object_id t.object_id%type;

cursor c is
select object_id explicit from t;
begin
open c;
fetch c into l_object_id;
close c;

return l_object_id;
end;
/

-- Use an implicit cursor
create or replace function f2
return t.object_id%type
is

l_object_id t.object_id%type;

begin
for x in (
select object_id implicit from t
)
loop
l_object_id := x.implicit;
exit;
end loop;

return l_object_id;
end;
/

-- Use a "select into" construct
create or replace function f3
return t.object_id%type
is

l_object_id t.object_id%type;

begin
select object_id sel_into into l_object_id from t
where rownum = 1;

return l_object_id;
end;
/

-- Pre-load the library cache
select f1 from dual;
select f2 from dual;
select f3 from dual;

alter session set sql_trace = true;

declare
l_object_id t.object_id%type;

begin

for i in 1..100000 loop
l_object_id := f1;
end loop;

for i in 1..100000 loop
l_object_id := f2;
end loop;

for i in 1..100000 loop
l_object_id := f3;
end loop;

end;
/

alter session set sql_trace = false;

--***********************************************
-- Results of TKPROF
--***********************************************

declare
l_object_id t.object_id%type;
begin
for i in 1..100000 loop
l_object_id := f1;
end loop;
for i in 1..100000 loop
l_object_id := f2;
end loop;
for i in 1..100000 loop
l_object_id := f3;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 155.23 164.08 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 155.25 164.10 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67
********************************************************************************

SELECT object_id explicit
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 8.16 7.34 0 0 0 0
Fetch 100000 13.75 12.47 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 21.92 19.82 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************

select object_id implicit from t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 8.22 7.47 0 0 0 0
Fetch 100000 13.82 13.11 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 22.05 20.59 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************

SELECT object_id sel_into from t
where rownum = 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 7.92 7.00 0 0 0 0
Fetch 100000 13.80 13.20 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 21.73 20.21 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)

--***********************************************

After calling each function 100,000 times, it seems like the performance difference between all 3 approaches in negligible, and therefore I don't get a clear feeling that any approach is superior to any other. I almost never use explicit cursors, so I'd shy away from the function 1 approach. Function 3 seems better than function 2 to me because it's more clear that we're after only 1 record.

A co-worker chose the implicit cursor approach (function 2), and I was about to suggest that the "select into" approach (function 3) would be much faster - but after benchmarking it, I'm not so sure.

Did I perform a valid test, or did I fall victim to the apples vs. toasters trap? Are there other statistics I should be considering? Is there another approach I should consider? Or is this a situation where any subjective decision is fine?

Thanks, Kurt

Tom Kyte
December 11, 2003 - 12:27 pm UTC

if I had to do this -- i would either:

select max(id) into l_id from T where rownum = 1;

or

begin
select id into l_id from t where rownum = 1;
exception
when no_data_found then id := null;
end;

probably the max(id) with a comment as to why i was doing what I did.


your test should have included "what happens when no_data_found" to be unbiased.

The max() approach appears to be the slowest!

Kurt, December 11, 2003 - 8:57 pm UTC

Tom,
Thanks for the quick response!

I've added the max() approach as function 4, modified function 3 to account for no data found, and I ran the complete test again.

Unfortunately, function 4 appears to be the slowest approach.

Any thoughts?

Thanks, Kurt

--************************************************

(previous table setup and calls to functions 1-3 as before)

-- Use the max() function
create or replace function f4
return t.object_id%type
is

l_object_id t.object_id%type;

begin
select max(object_id) max_meth into l_object_id from t;

return l_object_id;

exception
when NO_DATA_FOUND then return null;
end;
/

-- Pre-load the library cache
select f1 from dual;
select f2 from dual;
select f3 from dual;
select f4 from dual;

alter session set sql_trace = true;

declare
l_object_id t.object_id%type;

begin

for i in 1..100000 loop
l_object_id := f1;
end loop;

for i in 1..100000 loop
l_object_id := f2;
end loop;

for i in 1..100000 loop
l_object_id := f3;
end loop;

for i in 1..100000 loop
l_object_id := f4;
end loop;

end;
/

alter session set sql_trace = false;

--***********************************************
-- Results of TKPROF
--***********************************************

declare
l_object_id t.object_id%type;
begin
for i in 1..100000 loop
l_object_id := f1;
end loop;
for i in 1..100000 loop
l_object_id := f2;
end loop;
for i in 1..100000 loop
l_object_id := f3;
end loop;
for i in 1..100000 loop
l_object_id := f4;
end loop;
end;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.07 0 0 0 0
Execute 1 209.30 218.39 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 209.32 218.46 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 67
********************************************************************************

SELECT object_id explicit
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 8.03 7.31 0 0 0 0
Fetch 100000 13.76 12.79 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 21.80 20.11 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************

select object_id implicit from t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 8.13 7.39 0 0 0 0
Fetch 100000 14.12 13.33 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 22.25 20.73 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************

SELECT object_id sel_into from t
where rownum = 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 7.77 6.93 0 0 0 0
Fetch 100000 14.50 13.62 0 600000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 22.27 20.55 0 600000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)
********************************************************************************

SELECT max(object_id) max_meth
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 20.73 20.41 0 0 0 0
Fetch 100000 8.29 7.38 0 200000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 29.03 27.80 0 200000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)

Tom Kyte
December 13, 2003 - 10:28 am UTC

ok, in looking deeper at your example -- you would be best suited with where rownum=1

you have no predicate. you just want "the first row". where rownum=1 is the right approach.


Oops - Ignore the exception handler in function 4.

Kurt, December 11, 2003 - 9:10 pm UTC

Tom,
The exception handler in function 4 should be removed - it was a copy-and-paste holdover.

I ran the test again, and here is the revised function and the TKPROF output.

Kurt

-- Use the max() function
create or replace function f4
return t.object_id%type
is

l_object_id t.object_id%type;

begin
select max(object_id) max_meth into l_object_id from t;

return l_object_id;
end;
/

SELECT max(object_id) max_meth
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 100000 21.97 21.75 0 0 0 0
Fetch 100000 8.04 7.65 0 200000 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 200000 30.01 29.41 0 200000 0 100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 67 (recursive depth: 1)

Explicit vs Implicit

Arnaam, December 31, 2004 - 8:59 am UTC

Oracle Applications Developers Guide Release 11i, SQL Coding Guidelines:
"All SELECT statements should use an explicit cursor. Implicit
SELECT statements actually cause 2 fetches to execute: one to get
the data, and one to check for the TOO_MANY_ROWS
exception. You can avoid this by FETCHing just a single record
from an explicit cursor."


I understand that since we need a relativelt less code Implicit cursors are faster....But what about the two fetches mentioned in the guide that they do....

are they not expensive ...


Tom Kyte
December 31, 2004 - 11:20 am UTC

they are mythology. total mythological, with a bit of truth to be found in ancient history.

In the database, the double fetch was never true, never never never true.

In really really old (like version 2.3) forms -- this might have been true (so we are going back some 15-20 YEARS)......


Do you have a pointer to that document online? I'll gladly file the doc bug.




converting explicit to for loop

Alex, April 13, 2005 - 3:12 pm UTC

I'm doing something wrong, I was hoping you could help me with that. I am trying to rewrite this the best way possible (no indications of poor performance, just for clarity)

CURSOR cDMP
IS
SELECT id, version
FROM groups
WHERE NAME = pNAME;

begin
while some_cursor%FOUND
loop fetch cDMP
into vid, vversion

I'm trying to change it to something like:

for x in (SELECT id, version
FROM groups
WHERE NAME = pNAME)
loop.....

Fetching the rows is where I'm having trouble. Could you suggest the proper way to do this (if the first way can be improved on)?

Tom Kyte
April 13, 2005 - 4:07 pm UTC

the rows are fetched automagically when you


for x in ( select id, version from groups where name = pname )
loop
x.id and x.version exist right here.
end loop;


or, you can just change it to

for x in (cdmp)
loop
x.id and x.version...
end loop;

(leaving the cursor declared as it is now, just removing the tons of procedural code...)

Option 2

Alex, April 14, 2005 - 11:24 am UTC

Ok I'd like to know what I'm doing wrong here.

DECLARE

CURSOR cDMP
IS
SELECT id, version
FROM groups
WHERE NAME = pNAME;


CURSOR cTEST
IS
SELECT oid
FROM oid_table
WHERE oid = void;

BEGIN

vsubmit := 'N';

FOR x IN cDMP
LOOP
FOR y IN cTEST
LOOP
IF cTEST%NOTFOUND
THEN
INSERT INTO submit_test
(oid,
modified_date,
modified_by
)
VALUES (y.oid,
NULL,
SYSDATE,
'Alex'
);

vsubmit := 'Y';
END IF;
END LOOP;
END LOOP;
END;

This throws ORA-01001: invalid cursor.
I checked metalink for possible causes for this, there were many but none that looked like they applied to what I'm doing. They were talking about memory mismanagement issues?Not sure what that means. Thanks always.



Tom Kyte
April 14, 2005 - 11:33 am UTC

there is no need or reason to have "if ctest%notfound", if you are in the loop, looping over ctest, it would be physically impossible for it to NOT HAVE been found! (that you are in the loop means "hey guys, you found me")

but also, you are running the SAME query over and over and over in the loop? The answer to ctest would never change -- there are no inputs to it at all??

sorry, but I'm not sure what this is to be "doing" ?


Typo

Alex, April 14, 2005 - 12:03 pm UTC

Whoops sorry, that's not right. Where I put cTEST%NOTFOUND, that should be a different cursor that I missed. It's neither cDMP or cTEST. I have to edit everything I post so I made a typo. This is correct:

DECLARE
pNAME varchar2(100) := '80';
void varchar2(10) := '10';
vname varchar2(50) := 'Joe';

CURSOR cDMP
IS
SELECT id, version
FROM groups
WHERE NAME = pNAME;


CURSOR cTEST
IS
SELECT oid
FROM oid_table
WHERE oid = void;

CURSOR cEMP
is
SELECT emp_name
FROM emp_table
WHERE emp_name = vname;

BEGIN

vsubmit := 'N';

FOR x IN cDMP
LOOP
FOR y IN cTEST
LOOP
IF cEMP%NOTFOUND
THEN
INSERT INTO submit_test
(oid,
modified_date,
modified_by
)
VALUES (y.oid,
NULL,
SYSDATE,
'Alex'
);

vsubmit := 'Y';
END IF;
END LOOP;
END LOOP;
END;

Although I'm not sure what you mean by why am I doing the same query over and over.

Tom Kyte
April 14, 2005 - 1:20 pm UTC

well, you never opened cemp, so it is an invalid cursor.

ctest, look at it.

select oid from oid_table where oid = void;

void is constant, it is 10, seems to me that it would return the same data over and over again every single time you did it in the loop

wrong use of for loops

Alex, April 14, 2005 - 4:50 pm UTC

Yes I see what you mean. I think I went overboard when trying to re-tool the original code this came from. I was trying to replace while loops like

while cDMP%FOUND
loop
open cDMP;
fetch cDMP into....
while cTEST%FOUND
loop
open cTEST;
fetch cTEST into....
if cEMP%NOTFOUND then
insert....

with for loops. My changes didn't work so I know it's not the same. The hardcoded values are just there because this code is from a stored procedure, and instead of overwriting the original on the developer database with my broken wrong one, I just put it in a pl/sql block. They are representing parameters. I probably just should have created a new SP.

Tom Kyte
April 14, 2005 - 4:53 pm UTC

while cDMP%FOUND
loop
open cDMP;

that code would never fly.... you ask if it was found before it was open?

Alex, April 15, 2005 - 9:09 am UTC

Again with the typos. If I could cut and paste from sql plus believe me I would, I'd have to re-write everything using aliases though in order to do that.

open cDMP;
fetch cDMP into....
while cDMP%FOUND
loop
open cTEST;
fetch cTEST into...
while cTEST%FOUND
loop
open cEMP;
fetch cEMP into....
if cEMP%NOTFOUND then
insert....

That is the idea. I was trying to clean this up a little and improve the performance also. Maybe the for loop approach wasn't the way to go though.

Tom Kyte
April 15, 2005 - 9:36 am UTC

that would be

for x in cdmp
loop
for y in ctest
loop
for z in cemp
loop
....
end loop;
end loop;
end loop;


but truth be told, I would make it:

for x in ( select from A SINGLE QUERY THAT LETS the database
do what databases do best -- JOIN )
loop
...
end loop;

three loops each with a query screams "JOIN, JUST JOIN" to me.

Alex, April 15, 2005 - 12:09 pm UTC

Inside the loop though, how do I specify my insert condition? Without a cursor I can't say when cEMP%NOTFOUND. I was trying to use x.column_in_the_select, but that throws PLS-00324: cursor attribute may not be applied to non-cursor.

Tom Kyte
April 15, 2005 - 1:10 pm UTC

right, but if you are in the loop.....
by definition......
it was found.......

there can be nothing else that is true.

still sounds like a single query with a join of the first two cursors and a not in against the third

insert into table
select ...
from dmp, test
where join
and not exists ( select ... from emp where ... );



Alex, April 15, 2005 - 2:40 pm UTC

I was referring to your suggested way actually but you answered my question anyway with your pseudo code.

The revamped code inserts slightly more records than the original code, not sure yet why but my question is my empty trace file. When I run a trace on the new code, it produces an empty file:

TKPROF: Release 8.1.7.0.0 - Production on Fri Apr 15 13:54:55 2005

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Trace file: dev2_ora_1672.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: dev2_ora_1672.trc
Trace file compatibility: 8.00.04
Sort options: default

0 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
22 lines in trace file.

I mean, at the very least the alter session set sql_trace=true; should be in there. Do you know why this could happen? I'm doing it the exact same way as the original script, using the command before the declare, and it works.

Also, I was hoping you could suggest a pl/sql book that has many of the techniques you use such as putting a query in place of a cursor in a for loop (for x in (select...)) or correlated updates. I never see anything like these in books, only from your code. Mastering Oracle PL/SQL: Practical Solutions perhaps?

Tom Kyte
April 15, 2005 - 2:55 pm UTC

are you sure that was your tracefile, only had 22 lines in it. probably not.

what server type are you using? select server from v$session where sid = (select sid from v$mystat where rownum = 1 );


Mastering Oracle PL/SQL: Practical Solutions

is a good book written by smart people for plsql programming.

Alex, April 18, 2005 - 9:31 am UTC

Well I thought something screwy was going on with the database I was hoping everything would be back to normal Monday morning.  Still have the same ridiculous problem.

SQL> select server from v$session where sid =
  2  (select sid from v$mystat where rownum = 1 );

SERVER
---------
DEDICATED

SQL>
SQL>

I'm a little confused as to why you think that was not my tracefile.  I use them all the time, I know the file path, it creates a file, but for whatever reason none of the statements are in it.  I know the script is running too because the reason I ran into this problem is because it's inserting a number of rows that doesn't look right.  Thanks for your help. 

Tom Kyte
April 18, 2005 - 9:55 am UTC

It is 22 lines long and has no sql in it.  You said you turned on tracing.  If tracing was on and you ran sql, then the trace file would show it.  That is why I think this was not your trace file.



so, I'll assume this is unix, show us a cut and paste like this, you might have to tweak the query to select the right name on your OS.

ops$tkyte@ORA9IR2> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  2    from v$process a, v$session b, v$parameter c, v$instance d
  3   where a.addr = b.paddr
  4     and b.audsid = userenv('sessionid')
  5     and c.name = 'user_dump_dest'
  6  /
 
TRACE
-------------------------------------------------------------------------------
/home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> !ls -l /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
ls: /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc: No such file or directory
 
ops$tkyte@ORA9IR2> alter session set sql_trace=true;
 
Session altered.
 
ops$tkyte@ORA9IR2> !ls -l /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
-rw-rw----    1 ora9ir2  ora9ir2       916 Apr 18 09:53 /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
 
ops$tkyte@ORA9IR2> select * from dual;
 
D
-
X
 
ops$tkyte@ORA9IR2> !ls -l /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
-rw-rw----    1 ora9ir2  ora9ir2      1369 Apr 18 09:53 /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
 
ops$tkyte@ORA9IR2> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[tkyte@localhost tkyte]$ cat /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
/home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /home/ora9ir2
System name:    Linux
Node name:      localhost.localdomain
Release:        2.4.21-4.ELsmp
Version:        #1 SMP Fri Oct 3 17:52:56 EDT 2003
Machine:        i686
Instance name: ora9ir2
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 5066, image: oracle@localhost.localdomain (TNS V1-V3)
 
*** 2005-04-18 09:53:26.741
*** SESSION ID:(10.424) 2005-04-18 09:53:26.740
APPNAME mod='SQL*Plus' mh=3669949024 act='' ah=4029777240
=====================
PARSING IN CURSOR #1 len=32 dep=0 uid=268 oct=42 lid=268 tim=1087726959708374 hv=3943786303 ad='61c91228'
alter session set sql_trace=true
END OF STMT
EXEC #1:c=0,e=76,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1087726959707955
=====================
PARSING IN CURSOR #1 len=18 dep=0 uid=268 oct=3 lid=268 tim=1087726966027052 hv=1333943659 ad='61c73890'
select * from dual
END OF STMT
PARSE #1:c=0,e=455,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=4,tim=1087726966027042
EXEC #1:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=1087726966027242
FETCH #1:c=0,e=51,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=4,tim=1087726966027343
FETCH #1:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=1087726966027628
XCTEND rlbk=0, rd_only=1
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=222 op='TABLE ACCESS FULL OBJ#(222) (cr=3 r=0 w=0 time=41 us)'
[tkyte@localhost tkyte]$ wc /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
     33     148    1492 /home/ora9ir2/admin/ora9ir2/udump/ora9ir2_ora_5066.trc
[tkyte@localhost tkyte]$



 

Alex, April 18, 2005 - 11:28 am UTC

It seems this tracefile issue has something to do with TOAD. To be sure I ran a quick test from sqlplus like you did, it worked. So I ran my script from sqlplus, and it just hangs on one statement, but it does create a tracefile. I think there's something different about the database I'm using, if I use a different dev db no issues.
Wonder what you think this could mean:

UPDATE INTERFACE_TEST SET INDICATOR = 'Y', USER_ID =
'Alex'
WHERE
PID = '01'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 1.30 0 0 0 0
Execute 1 0.00 7409948.48 0 6 1 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 7409949.78 0 6 1 0

Misses in library cache during parse: 0
Optimizer goal: ALL_ROWS
Parsing user id: 48 (recursive depth: 1)

Tom Kyte
April 18, 2005 - 11:38 am UTC

unless the update actually ran for 86 days, looks like a bad tracefile somehow.

Alex, April 18, 2005 - 11:47 am UTC

Dang it, sorry that should be 7409.95....you reminded me I need to update my tkprof.

Tom Kyte
April 18, 2005 - 12:09 pm UTC

so, if it ran for 2 hours, it was probably blocked. If you had traced with a 10046 level 12 -- the waits will be in the trace file (and using 9.2 tkprof, they would be in the tkprof report).

Probably enqueue wait, it was blocked

How can I turn something like this into a implicit cursor?

mark, May 05, 2005 - 12:33 pm UTC

CURSOR GetWOForBlockLHCUR(blockFrom IN yard_blocks.block_name%type) -- SECTION IS NOT INCLUDED IN THE SEARCH
IS
SELECT wo.order_id, ic.l2, ic.l3, wo.to_block, wo.to_row, wo.to_stack, ic.out_vessel_code,
ic.status_code, ic.container_id, ic.hazardous, ic.out_of_service, ic.over_dimensions,
ic.reefer, wo.manual_location, cm.ssl_owner_code, cm.lht_code, ic.gross_weight,
wo.move_type, ic.out_port_of_discharge,ic.In_mode,ic.container,ic.out_mode
FROM work_orders wo, inv_containers ic, container_masters cm,
yard_blockouts_temp yb, tml_blockout_reasons t, Yards y
WHERE ic.L1 = BlockFrom AND wo.queue_code = Queue_in AND wo.assigned_date is null
AND wo.HOLD = 'N' AND wo.ORDER_PROBLEM IS NULL AND MaxLiftWeight_in > ic.gross_weight
AND MOVE_TYPE = 'Y' AND wo.INV_CONTAINER_ID = ic.CONTAINER_ID
AND (t.safety_blockout IS NULL OR t.safety_blockout = 'N')
AND ((ic.reefer = 1 and plug_unplug = 'U') OR (ic.reefer = 0))
AND ((Queue_in = 'EXREL'
AND NOT EXISTS (SELECT * FROM container_holds where container = ic.CONTAINER))
-- AND NOT IN (SELECT * FROM container_holds where container = ic.CONTAINER))
-- AND (SELECT MAX(HOLD_CODE) FROM container_holds where container = ic.CONTAINER) = NULL)
-- AND (SELECT count(2) FROM container_holds where container = ic.CONTAINER) = 0)
OR Queue_in <> 'EXREL')
AND ic.CONTAINER = cm.CONTAINER AND yb.reason_code = t.code
AND y.yard_id = yb.yard_id AND ic.l1 = yb.l1(+) AND ic.l2 = yb.l2(+)
ORDER BY wo.priority,ic.l1,ic.l2,ic.l3 asc, ic.l4 DESC;


Tom Kyte
May 05, 2005 - 1:11 pm UTC

that I would probably level in the declare section (big) and then just:


for x in ( getWOForBlockHCUR( inputs ) )
loop
    process x.....
end loop;


ops$tkyte@ORA10G> declare
  2          cursor c( p_in in varchar2 )
  3          is
  4          select * from dual where dummy = p_in;
  5  begin
  6          for x in C('X')
  7          loop
  8                  dbms_output.put_line( x.dummy );
  9          end loop;
 10  end;
 11  /
X
 
PL/SQL procedure successfully completed.
 
 

Still can't come up with it!

A reader, May 05, 2005 - 2:29 pm UTC


what am I doing wrong??

A reader, May 05, 2005 - 2:54 pm UTC

Tom, I really want to be able to use implicit cursor here, but I am having a hard time understanding it.

1 declare
2 l_yard yard_blocks.block_name%type;
3 begin
4 for x in (SELECT wo.order_id, ic.l2, ic.l3, wo.to_block, wo.to_row, wo.to_stack,
5 ic.out_vessel_code, ic.status_code, ic.container_id, ic.hazardous, ic.out_of_service,
6 ic.over_dimensions, ic.reefer, wo.manual_location, cm.ssl_owner_code, cm.lht_code,
7 ic.gross_weight,wo.move_type, ic.out_port_of_discharge,ic.In_mode,ic.container,ic.out_mode
8 FROM work_orders wo, inv_containers ic, container_masters cm,
9 yard_blockouts_temp yb, tml_blockout_reasons t, Yards y
10 WHERE ic.L1 = BlockFrom AND wo.queue_code = Queue_in
11 AND wo.assigned_date is null
12 AND wo.HOLD = 'N' AND wo.ORDER_PROBLEM IS NULL
13 AND MaxLiftWeight_in > ic.gross_weight
14 AND MOVE_TYPE = 'Y' AND wo.INV_CONTAINER_ID = ic.CONTAINER_ID
15 AND (t.safety_blockout IS NULL OR t.safety_blockout = 'N')
16 AND ((ic.reefer = 1 and plug_unplug = 'U') OR (ic.reefer = 0))
17 AND ((Queue_in = 'EXREL'
18 AND NOT EXISTS (SELECT * FROM container_holds where container =ic.CONTAINER))
19 AND (SELECT count(*) FROM container_holds where container = ic.CONTAINER) = 0)
20 OR Queue_in <> 'EXREL')
21 AND ic.CONTAINER = cm.CONTAINER AND yb.reason_code = t.code
22 AND y.yard_id = yb.yard_id AND ic.l1 = yb.l1(+) AND ic.l2 =yb.l2(+)
23 ORDER BY wo.priority,ic.l1,ic.l2,ic.l3 asc, ic.l4)
24 loop
25 begin
26 SELECT wo.order_id, ic.l2, ic.l3, wo.to_block, wo.to_row, wo.to_stack,
27 ic.out_vessel_code, ic.status_code, ic.container_id, ic.hazardous, ic.out_of_service,
28 ic.over_dimensions, ic.reefer, wo.manual_location, cm.ssl_owner_code, cm.lht_code,
29 ic.gross_weight,wo.move_type, ic.out_port_of_discharge,ic.In_mode,ic.container,ic.out_mode
30 FROM work_orders wo, inv_containers ic, container_masters cm,
31 yard_blockouts_temp yb, tml_blockout_reasons t, Yards y
32 WHERE ic.L1 = BlockFrom AND wo.queue_code = Queue_in
33 AND wo.assigned_date is null
34 AND wo.HOLD = 'N' AND wo.ORDER_PROBLEM IS NULL
35 AND MaxLiftWeight_in > ic.gross_weight
36 AND MOVE_TYPE = 'Y' AND wo.INV_CONTAINER_ID = ic.CONTAINER_ID
37 AND (t.safety_blockout IS NULL OR t.safety_blockout = 'N')
38 AND ((ic.reefer = 1 and plug_unplug = 'U') OR (ic.reefer = 0))
39 AND ((Queue_in = 'EXREL'
40 AND NOT EXISTS (SELECT * FROM container_holds where container =ic.CONTAINER))
41 AND (SELECT count(*) FROM container_holds where container = ic.CONTAINER) = 0)
42 OR Queue_in <> 'EXREL')
43 AND ic.CONTAINER = cm.CONTAINER AND yb.reason_code = t.code
44 AND y.yard_id = yb.yard_id AND ic.l1 = yb.l1(+) AND ic.l2 =yb.l2(+)
45 ORDER BY wo.priority,ic.l1,ic.l2,ic.l3 asc, ic.l4)
46 end loop;
47* end;
term@CTMSSTAG > /
AND ic.CONTAINER = cm.CONTAINER AND yb.reason_code = t.code
*
ERROR at line 21:
ORA-06550: line 21, column 11:
PLS-00103: Encountered the symbol "AND" when expecting one of the following:
loop
ORA-06550: line 23, column 11:
PLS-00103: Encountered the symbol "ORDER" when expecting one of the following:
. ( * @ % & - + ; / at mod rem return returning
<an exponent (**)> and or ||
ORA-06550: line 23, column 60:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
. ( , * @ % & - + ; / at for mod rem <an exponent (**)> asc
desc ||


Tom Kyte
May 05, 2005 - 2:59 pm UTC

mis matched ()'s in there.

The

for x in (SELECT
^^^

matches up with:

OR Queue_in <> 'EXREL')
^^^
AND ....


so you have missing ()'s

and the next problem after you get that is the query inside the loop isn't "meaningful"??


Thanks

A reader, May 05, 2005 - 4:12 pm UTC

I guess I was looking for you to show me with the
submitted cursor. Either way thanks.




cursors

mohannad, May 19, 2005 - 12:57 pm UTC

q1. are using for rec in (...................
will be faster than opening and closing a cursor ???

---------
q2. and if we assuse that i want to retrive a single values
are using open cursor close cursor will be faster than using select into??/

Tom Kyte
May 19, 2005 - 1:54 pm UTC

q1) marginially faster and more bug free, but best of all, less keystrokes for you.

but the cursors are opened and closed, just not by you.

q2) no.

cursors

mohannad, May 19, 2005 - 4:59 pm UTC

then if i use select into will be more faster than open & close the cursors ???


Tom Kyte
May 20, 2005 - 7:00 am UTC

it'll not only be marginally faster, it will result in programs with less bugs.

cursors

mohaNNAD, May 19, 2005 - 5:03 pm UTC

what you mean by marginally will be faster in your last message???

do you recommend using cursors????

Tom Kyte
May 20, 2005 - 7:01 am UTC

meaning "small", "marginal", meaning that performance is not what to consider here, meaning that it is

a) a little faster to use implicit
b) with less code
c) and fewer bugs.

cursors

mohannad, May 20, 2005 - 12:37 pm UTC

then if i want to retrieve a single value i should use select into a, and if there is more that one value then i should use for rec in(......),and is there any cases where you recomend to use open,fetch,close
thanks a lot


Tom Kyte
May 20, 2005 - 6:39 pm UTC

only with refcursors if you are forced to use dynamic sql.

and in 9ir2 and before when you need bulk collect (which you don't need in 10g as it bulk collects 100 rows at a time for you automagically)

cursors

mohannad, May 20, 2005 - 7:31 pm UTC

is this true
if i want to retrieve a single value i should use select into instesd of open/close cursors, and if
there is more that one value then i should use for rec in(......) instesd of open/close cursors

Tom Kyte
May 21, 2005 - 8:41 am UTC

if you run a query that returns a single row, select into is the way to go (catching no_data_found and dealing with zero rows if you allow for zero rows)

for x in ( select ... )

is good in 10g for all cases.

in 9ir2 and before, if the select returns hundreds of rows, you would consider bulk collect.

Open, Fetch, Loop

Richard, June 11, 2005 - 5:51 am UTC

In "Conducting the Oracle Job Interview" (Mike Ault & DKB, Rampant Press, ISBN 0-9727513-1-9) Page 57, it states (as a question an employer should ask of a candidate):

******************************************************
In what order should an open/fetch/loop set of commands in a PL/SQL block be implemented if you use the %NOTFOUND cursor variable in the exit when statement? Why?

The answer given is:
OPEN then FETCH then LOOP followed by the exit when. If not specified in this order, it will result in the final return being done twice because of the way the &NOTFOUND is handled by PL/SQL.
******************************************************

OPEN then FETCH then LOOP? Shouldn't that be OPEN, LOOP, FETCH?

Tom Kyte
June 11, 2005 - 9:43 am UTC

some people for whatever reason


open c
fetch c
loop
exit when c%notfound;
process record
fetch c
end loop
close c;


My answer would be:

well, given that it is more error prone and less efficient to use explicit cursors, unless I need to bulk collect I would have no open, fetch or closes, it would simply be:

for x in C
loop
process record
end loop


less bugs (no worries about processing same row twice or what not)
less code to maintain
easier to understand
more productive


And in 10g when the need to bulk collect in plsql disappears, since plsql does it, explicit cursors would be something I use when processing a ref cursor only!

Then again...

Richard, June 11, 2005 - 6:03 am UTC

Confusion! I thought OPEN, LOOP, FETCH, was correct, as that's the sequence of events in "Beginning Oracle Programming" (TK, Dillon & Beck, Wrox Press, ISBN-1-861006-90-X), Page 374. However, on Page 377, OPEN, FETCH, LOOP is used. Which is the best method, then?

Tom Kyte
June 11, 2005 - 9:45 am UTC

open
loop
fetch
exit when
process
end loop
close


is the conventional way to do it

What Do You Make of This, Then?

Richard, June 11, 2005 - 10:06 am UTC

From the aforementioned Ault/Burleson tome (BOLD text is my emphasis):

**********************************************************
If the candidate indicates that a question does not make sense, or that the question is AMBIGUOUS, the interview is normally terminated at that point.
**********************************************************

Given your earlier reply, I guess you'd be out on the street within 2 minutes ;)

Also, woe betide (apparently) anyone turning up for interview without:
1. An MA, or better
2. A white, crisp shirt; a dark suit; conservative tie & dark leather shoes

and (my favourite):
3. [don't favour any candidate with] a degree-level qualification from a night school/"non-traditional graduate school" [!!!!!!!!]

My POV on point 3: I would have thought that any candidate willing to both realise their shortcomings and then attempt to do something about them was an A1 candidate. In the UK, we have the Open University - it's primarily for those among us who want to gain a degree, but, for whatever reason, couldn't do so at the *usual* post-highschool stage of their lives. The Open University is held in very high regard, as are its graduates.

The sad thing is, someone (probably more than one) will have been eliminated from attending an interview, or shown the door before they even sat down, all because an HR person read this book and saw *Open University* on the résumé; or, the interviewer used the above cursor question, and balked when told that the question was ambiguous.



Tom Kyte
June 11, 2005 - 10:31 am UTC

What I make of that is that I wouldn't want to work for someone that didn't want people to

a) get clarifications before acting (dangerous isn't it "oh i though you told me
to drop that tablespace, sorry for not getting clarficiation first")

b) are closed minded to other perspectives (just because you are hiring doesn't
mean you are smarter). I've learned things in interviews from people who
could answer the question better than I.

Their book would not help you get a job with me, for I ask ambigous questions and if you don't ask for clarifications, you cannot give me the right answer. (my three sql questions I ask:

give a table with a DATE column answer me this:

1 how many records are in the table
2 how many record by MONTH are in the table
3 query out the most frequently occuring month in that table

1 is not ambigous.

2 could be, by month do you mean "jan" or do you mean "jan-2004", "jan-2005", .... it could be, best to ask don't you think, hate to make the wrong guess. I might be looking for "historically what is our busiest month" or I might want to know month-year by month-year how we've done

3 is so ambigous.....


I would gladly leave the interview if they didn't want me to QUESTION AUTHORITY. Imagine a place where asking clarification is apparently a sign of weakness, or questioning a "superior" is grounds for dismissal. Ouch. Run, don't walk.

I have had people without college degrees work for me, some of the smartest people in the group (1/6th of my last group didn't have a 4 year college degree...)

As for the dress, a t-shirt and cut offs might get you some demerits, but you don't need to dress like a sales guy to impress. It doesn't hurt, but to me it doesn't hurt to just look repectfully "nice". I would wear a suit myself (not sure about white shirts, they are what I wear when the rest of my shirts are at the cleaners). But my best DBA hire ever did not, but he dressed nicely.

I agree with you on point 3.

A reader, June 15, 2005 - 2:44 pm UTC

Hi Tom,

I have situation like this:

How can I check if a cursor has data or not.

This is what I am trying to do:


Procedeure p(i number)
is
ex1 exception;
cursor c1(n number) is select ename,job from emp where deptno=n;
begin

open c1(i);

if c1%notfound then
raise ex1;
end if;

....
exception
when ex1 then
...
end;


i have also tried this:

if sql%rowcount=0 then
raise ex1;
end if;

Even though i am passing an invalid deptno; it is not raising the
exception.

What is the right way to check if a cursor has data/records.

Please Help.

Tom Kyte
June 16, 2005 - 3:26 am UTC

why??? the client application is the only thing that "could care" and the client application is the only thing that "can tell"

for in order to tell if ANY cursor (not just 'ref' types) has 'data' you have to fetch a row.

and a row, once fetched, is just that -- fetched - not there anymore.


So, this would be something for the client to process.

Burleson & Ault - You Both Have a Lot to Answer For!!!

Richard, June 17, 2005 - 9:12 am UTC

Guess what? I've just attended an interview for a DBA role (multi-million dollar company, leading light in their industry, etc., etc.) and I was set 3 written tests. Fair enough, you might say, BUT the questions had been lifted from the Burleson/Ault book, which I referred to earlier in this thread!!! Talk about coincidence!

The BIG problem was not that I immediately realized that this was not the company for me, nor that the manager had decided to spend less than $20 (for the book) on screening candidates, it was that he'd not even copied the questions correctly!

Therefore, I answered as best I could, with
*** This is a Donald Burleson question! Please do NOT rely upon the answer in the book! ***
at the end of each of my answers to any DKB question (about 80% of the questions were DKB/Ault questions).

Net result:
1. I waste my time on a non-interview (though at least I realized what was happening, and have, naturally, declined the position.

2. The recruitment agency loses-out.

3. The company loses me - which, even though I say it myself - is a big loss to them.

Messages to Donald and Mike: Don, stick to tiny horses; Mike, stick to scuba diving. You are both literally poisoning the Oracle job market. Your book is a hazard, not a help.


What is a cursor made of anyway ? Threads, Lists, Cheese !!

David, July 15, 2005 - 7:54 am UTC

Tom,

I have always wondered , what EXACTLY is a cursor? Of course we know that Oracle creates a cursor for every SQL query, and a cursor can be opened/closed , a cursor once opened can be re-used , a "REF Cursor" can be passed to a calling client application which uses it as a recordset.

But here are my questions :

What is a cursor made of ? I mean , in terms of a C data structure ? Is it just an array of pointers to the physical locations (rowids) of the data ?

What exactly happens when you "OPEN" and "CLOSE" a cursor? Is memory allocated for that SQL statementID? Is a temp file created on disk ?


Does OPEN/FETCH/CLOSE correspond to malloc, read, free ?


Thanks

Tom Kyte
July 15, 2005 - 8:10 am UTC

think of a cursor like a file.

open a file, read from file, close file.


A cursor is just a "context", a "state", a C structure if you will. A pointer to a program (execution plan) and a state (where in that plan are we).

when you open a cursor, you point to a program (plan) and set up your bind variables (inputs) and set up your state (state = haven't fetched anything yet).

When you fetch, it'll look at your state and figure out based on the plan what to do. If your query was "select * from ten_billion_row_table", it'll find the first block and get the first rows (no "temp files" or anything, no big ram allocations, it just does as little as it needs to to get you the first bits of data).

when you close it, you free up the cursor slot in your session, your state goes away (but the program lives on in the shared pool)


the best analogy C-wise would be fopen, fread, fclose.

Thanks for this explication!!!

Jairo Ojeda, July 15, 2005 - 11:49 am UTC


Doc Bug still needed on that apps developer guide

A reader, July 15, 2005 - 4:38 pm UTC

Oracle Applications Developer’s Guide, RELEASE 11i VOLUME 1
The part number for this volume is A83705–03.
To reorder this book, please use the set part number, A75545–03.

</code> http://download-east.oracle.com/docs/cd/B12190_11/current/html/docset.html <code>

It STILL says "All SELECT statements should use an explicit cursor. Implicit
SELECT statements actually cause 2 fetches to execute: one to get
the data, and one to check for the TOO_MANY_ROWS
exception. You can avoid this by FETCHing just a single record
from an explicit cursor."

I can even see that one customer tried to convince support this wasn't true. They didn't listen to the customer. Perhaps they will listen to you!


Tom Kyte
July 15, 2005 - 8:37 pm UTC

well, it does cause two fetches, it is just that the open/fetch/fetch/close can be done in a single call.

I don't see that doc on that page?

Excellent answer Tom. Thank you

Dave, July 18, 2005 - 11:55 am UTC

Tom,
Thank you for your explanation of the cursor internals. Based on your explanation , another question popped up in my head.
What about a REF Cursor which is passed to say a Java program or a JSP program which uses the "REF Cursor" variable as a recordset and loops through it to display data on a page. If a cursor is a structure consisting of a pointer to an execution plan and a state(context) , is that what is passed to the client program. If so how is the client program able to handle a Oracle-specific structure ?
OR
Is a REF-Cursor different from regular cursors and all that is passed to the client is an array containing the fetched results of the query (results that have already been fetched by Oracle even though we never usually issue FETCH statements on a REF Cursor).

Thanks

Tom Kyte
July 18, 2005 - 12:20 pm UTC

that is just a cursor.

it operates the same as a cursor would, because it is in fact a cursor.

Excellent as USUAL !!!

Vikram, July 19, 2005 - 5:03 am UTC

Tom,

That was excellent stuff !!!
I wonder how one person could do so much !!!

Just curious to know - the "tom" in this site refers to a person called "Thomas Kyte" or a group of "Toms" !!!

Regards,
Vikram

Tom Kyte
July 19, 2005 - 7:35 am UTC

There is one person. Unless the answer begins with "I asked 'so and so' to answer and they said..."


Using Cursors Compared to IN Operator

Rahul Dutta, December 06, 2005 - 1:31 pm UTC

Hi Tom,

I know this is very small thing but still asking as I am little confused!!

There is a situation where I have to DELETE rows from table A where A.ID is equal to B.ID. So the quesry I would prefer is:
DELETE from A
WHERE A.ID IN (SELECT B.ID from B,A where A.ID=B.ID);

Can you please suggest whether this would be a costly quesry? If yes, Can you please suggest me the alternative? The developers are debating on using CURSORS for this operation, would using CURSORS be a better alternative, when I would not be putting any logic on the Recordset.

Thanks a ton as always.
Rahul

Tom Kyte
December 06, 2005 - 3:35 pm UTC

delete from a where id in (select id from b);




What about FTS?

Purushoth, December 06, 2005 - 8:30 pm UTC

Looks from the usage that table B will have more records than A and (select id from b) will result in FTS on b.
The query

delete from a where id in (select id from b)

thus will have

* FTS on B
* Scan on A of matching id (can be FTS or indexed scan based on ID is indexed or not)

If B.id and A.id were indexed then we can take advantage of index scan?

delete from a where id in (select b.id from a, b where a.id = b.id)

will have

- Full Index scan on A or B
- Index scan on B or A
- Index scan on A (Delete)

Surprisingly when I tried using rowid like

delete from a where rowid in (select a.rowid from a,b where a.id=b.id)

the query took longer than using 'id' column. Is there any catch on using rowid?

Tom Kyte
December 07, 2005 - 1:50 am UTC

I certainly am hoping for a full scan!!! Please be a full scan!

or, if B is really big and A is really small, the CBO can in fact turn that into an EXISTS, it is smart enough.


But if A and B are sizable - please - bring on the full scans, avoid those nasty, slow indexes.




what am I doing wrong here

A reader, May 16, 2006 - 11:58 am UTC

I am trying to create an implicit cursor...

  1  declare
  2  pvisit    gate_visit.visit%TYPE;
  3  pref_id   gate_containers.reference_id%TYPE;
  4  begin
  5     for x in ( SELECT gc.action, gc.booking, gv.coop_in_date, gc.ssl_user_code, s.long_description
  6               FROM gate_containers gc,gate_visit gv,ssl s
  7          where gc.visit = gv.visit
  8          AND gc.ssl_user_code = s.code(+))
  9    loop
 10     select visit into pvisit, reference_id into pref_id
 11     from
 12     gate_containers gc, gate_visit gv,ssl s
 13      WHERE gc.visit = pvisit
 14          AND gc.reference_id = pref_id
 15          AND gc.visit = gv.visit
 16          AND gc.ssl_user_code = s.code(+);
 17  end loop;
 18* end;
SQL> /
   select visit into pvisit, reference_id into pref_id
                                          *
ERROR at line 10:
ORA-06550: line 10, column 43:
PL/SQL: ORA-00923: FROM keyword not found where expected
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored
 

Tom Kyte
May 16, 2006 - 12:15 pm UTC

select column1, column2
into variable1, variable 2

thanks!

A reader, May 16, 2006 - 12:22 pm UTC


Implicit Cursor Vs Cursor For Loop

Muhammad Riaz Shahid, June 14, 2006 - 9:30 am UTC

Dear Tom,
Can you tell us why the cursor for loop is faster than implicit cursor?

Here is the test case:

SQL> declare
  2     l_start number default dbms_utility.get_time;
  3  begin
  4     for i in 1..100000
  5     loop
  6             for x in (select NULL from scott.emp where empno = i)
  7             loop
  8               null;
  9             end loop;
 10     end loop;
 11     dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs');
 12  end;
 13  /
278 hsecs

PL/SQL procedure successfully completed.


SQL> ed
Wrote file afiedt.buf

  1  declare
  2     lv_dummy VARCHAR2(1);
  3     l_start number default dbms_utility.get_time;
  4  begin
  5     for i in 1..100000
  6     loop
  7             BEGIN
  8               SELECT NULL
  9               INTO lv_dummy
 10               from scott.emp where empno = i;
 11             Exception
 12               WHEN NO_DATA_FOUND THEN NULL;
 13             END;
 14     end loop;
 15     dbms_output.put_line( (dbms_utility.get_time-l_start) || ' hsecs');
 16* end;
SQL> /
377 hsecs

PL/SQL procedure successfully completed.


I expected that implicit cursors will be fast. So is it valid to state that "For row existance check, use cursor for loops instead of implicit cursor"?

N.B: I didn't put rownum = 1 in sql since i knew i am already comparing with a P.K. For non-PK columns comparison, i will be using rownum = 1
 

Tom Kyte
June 14, 2006 - 12:35 pm UTC

they are BOTH implicit cursors.


They are absolutely NOT EQUIVALENT either - they do not do the same logic.

You are missing a counter in the first case where you count to make sure you never fetch more than one row and through and exception.


You are comparing "not equivalent things" there.


I would NOT recommend using the for loop, no. It is not intuitive, it does not do what the select into does.


And in fact, if you are checking for the existance of a row, my recommended approach is:


<this space INTENTIONALLY left void of all code>


why "check", just try to do something and of the row exists upon which to do it - fine, else - you'll know then that it doesn't exist.

checking for rows is just lame in almost all cases.

BULK COLLECT and Implicit

V, October 05, 2006 - 1:50 pm UTC

Can you use the LIMIE CLAUSE with implicit cursors:
SELECT object_name BULK COLLECT into object_name_t from t1;

Tom Kyte
October 05, 2006 - 4:21 pm UTC

you use rownum with that.

select ... bulk colect into ....
from t1
where rownum <= ....;


but be sure you know what you are asking for there, that'll get a somewhat random sprinkling of rows.

(eg: it doesn't in general MAKE SENSE unless it is a top-n query...


select ... bulk collect into ...
from (select * from t1 ORDER BY SOMETHING)
where rownum <= ....;


Doc error?

Raghu, December 05, 2007 - 2:32 pm UTC

I have followed your discussions on implicit /explicit cursors and have been enforcing implicit cursors within my technical team - till someone pointed this out to me on one of the Oracle Apps Docs

4-9 Oracle Applications Developer's Guide(Part No. B31458-02)

"¿ All SELECT statements should use an explicit cursor. Implicit SELECT statements
actually cause 2 fetches to execute: one to get the data, and one to check for the
TOO_MANY_ROWS exception. You can avoid this by FETCHing just a single
record from an explicit cursor."

Guess this is a doc error?
Tom Kyte
December 10, 2007 - 8:00 am UTC

that is a documentation bug, absolutely.

the proof has been supplied so many times.

It has its origins in the years from 1980-1989... from forms. It hasn't been true of the DATABASE plsql ever.


FETCH behaviour

Narendra, August 08, 2008 - 5:32 am UTC

Tom,

I know you have said above that whatever has been mentioned in the documentation is NOT TRUE and the proof has been supplied so many times.
=============================================================
Doc error? December 5, 2007 - 2pm US/Eastern
Bookmark | Bottom | Top
Reviewer: Raghu

I have followed your discussions on implicit /explicit cursors and have been enforcing implicit
cursors within my technical team - till someone pointed this out to me on one of the Oracle Apps Docs
4-9 Oracle Applications Developer's Guide(Part No. B31458-02)

"¿ All SELECT statements should use an explicit cursor. Implicit SELECT statements
actually cause 2 fetches to execute: one to get the data, and one to check for the
TOO_MANY_ROWS exception. You can avoid this by FETCHing just a single
record from an explicit cursor."

Guess this is a doc error?



Followup December 10, 2007 - 8am US/Eastern:
that is a documentation bug, absolutely.

the proof has been supplied so many times.

It has its origins in the years from 1980-1989... from forms. It hasn't been true of the DATABASE plsql ever.

=============================================================
I am working in an Apps environment where development team is following this (using explicit cursor) standard. While I read this thread and tried to convince the developers that they do not need to use explicit cursors, I failed to convince. Then I decided to prove them the point with a test case, but surprisingly I am getting the results in-line with the documentation. Following is my test case and corresponding trace details.
SQL> set serveroutput on size 10000
SQL> set linesize 121
SQL> clear
SQL> !clear

SQL> select * from v$version ;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production
CORE    8.1.7.0.0       Production
TNS for Solaris: Version 8.1.7.4.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL> create table t as select * from all_objects where rownum <= 1000 ;

Table created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'T');

PL/SQL procedure successfully completed.

SQL> variable x VARCHAR2(100);
SQL> alter session set sql_trace=true;

Session altered.

SQL> select object_name from t where rownum = 1 ;

OBJECT_NAME
------------------------------
/1001a851_ConstantDefImpl

SQL> exec select object_name into :x from t explicit_t where rownum = 1 ;

PL/SQL procedure successfully completed.

SQL> print x

X
-------------------------------------------------------------------------------------------------------------------------
/1001a851_ConstantDefImpl

SQL> alter session set sql_trace=false;

Session altered.

SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
CRBDEV3:/ora/sat2/BASE/oracle/sat2db/8.1.7.4/admin/udump $tkprof sat2_ora_5281.trc fetch.txt

TKPROF: Release 8.0.6.1.0 - Production on Fri Aug 8 09:57:50 2008

(c) Copyright 1999 Oracle Corporation.  All rights reserved.


And here is the TKPROF output
select object_name 
from
 t where rownum = 1 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.05          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          5         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.01       0.05          0          5         12           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY 
      1   TABLE ACCESS FULL T 

********************************************************************************

BEGIN select object_name into :x from t explicit_t where rownum = 1 ; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.03       0.06          0          0          1           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.03       0.06          0          0          1           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173  
********************************************************************************
SELECT OBJECT_NAME   
FROM
 T EXPLICIT_T  WHERE ROWNUM = 1


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           0
Fetch        1      0.00       0.00          0          5         12           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          5         12           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 173     (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  COUNT STOPKEY 
      1   TABLE ACCESS FULL T 

********************************************************************************

Can you please let me know if my test case is incorrect / incomplete or the multiple FETCH theory is indeed true (at least in 8i) ?
Tom Kyte
August 08, 2008 - 1:15 pm UTC

implicit and explicit cursors only exist in the realm of a programming language like plsql, SQLJ, pro*c

sqlplus doesn't have the concept of "implicit or explict" it is just a scripting environment that executes queries and dumps results


that, and the thing you call "explicit" is an example of....

an IMPLICIT cursor!

you just showed that an implicit cursor does not double fetch (you did not define, open, fetch, close that cursor - you did not EXPLICITLY handle the cursor, the cursor was therefore IMPLICIT)


ops$tkyte%ORA10GR2> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA10GR2> declare
  2          n number;
  3  begin
  4          select user_id into n from all_users where rownum = 1;
  5  end;
  6  /

PL/SQL procedure successfully completed.


SELECT USER_ID
FROM
 ALL_USERS WHERE ROWNUM = 1


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           0
Fetch        1      0.00       0.00          0          7          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0          7          0           1




Apologies

Narendra, August 08, 2008 - 7:15 am UTC

Tom,

Sorry. Please ignore my above post.
I realized my mistake in my test case. When I corrected it and ran, it worked as you said.

Madis, February 19, 2009 - 9:23 am UTC

Hi Tom and everyone else,

After reading through this thread I just can't believe that I'm the very next one to fail to convince one developer to use implicit cursors instead of explicit ones. And it's because he also pointed me to that doc - "Oracle Applications Developer's Guide Release 11i". Couple of minutes ago I downloaded it from Oracle's documentation library and the relevant section is still there:


All SELECT statements should use an explicit cursor. Implicit
SELECT statements actually cause 2 fetches to execute: one to get
the data, and one to check for the TOO_MANY_ROWS
exception. You can avoid this by FETCHing just a single record
from an explicit cursor.


To get that documentation I
1) went to the Oracle Technology Network site http://www.oracle.com/technology/index.html
2) selected "Documentation" -> "Applications" from the upper menu
3) selected "Oracle Applications 11.5.10.2+ Online Documentation Library"
4) selected "Documentation" tab
5) selected "Oracle Applications Developer's Guide" (Part No. A75545-03)

That's the doc in pdf format. And the relevant section can be found following the path
"Using PL/SQL in Oracle Applications" -> "Overview of Using PL/SQL in Applications" -> "SQL Coding Guidelines"

And there it is - the second point.

Tom - from reading the above thread I understand that you've failed to locate the documentation online. I'd be more than happy to help you to file a bug. In that doc there's a section about feedback which has this e-mail address - appsdoc@us.oracle.com. Is it enough to send a relevant information there or is there a more proper way to file a documentation bug report? Sorry for ignorance - haven't done any bug reports before.

Regards,
Madis

Tom Kyte
February 19, 2009 - 1:37 pm UTC

That has been recently fixed - or will be soon, they had me review some of their "application developer guidelines" and I stripped out the great wealth of mythology that had accumulated over time (most of the advice was based on myth and was wrong)

Please - bear in mind - the Oracle application developers (the people that make HR, CRM, Financials and the like) - they are just developers. That they work at Oracle doesn't mean they learned all about databases via osmosis - they come with as many "database misconceptions" as anyone else does.


and even if it were true, cannot the developer see the FLAW (the fatal flaw) in the supposed advice.

If it did not check for the 2nd row (and it does, it does so in a single fetch call, it has done so forever, this was never true in plsql) - you would never know you had a too_many_rows condition! You would be getting a random row (that could actually change from day to day - same query, same data - DIFFERENT ROW)

Madis, February 19, 2009 - 5:03 pm UTC

He understands the too_many_rows issue perfectly. But our query we worked on is exactly like this - we just want the very first row. And then got in trouble trying to explain why

select col into var from table where rownum = 1

doesn't do the second fetch for checking too_many_rows condition.
Tom Kyte
February 21, 2009 - 8:34 pm UTC

who is "he" and how do YOU know "he understands" it perfectly?


That query with rownum=1 is non-deterministic, do YOU understand that? given the same data, it could return a different answer today than tomorrow than the next day.


select into will always be at least as fast as open/fetch/close IF NOT FASTER

and it will ALWAYS be more "accurate", "correct", "bug free"



no_data_found performance issues

Fábio Oliveira, March 24, 2009 - 10:27 am UTC

Hi Tom.

I've found the most unusual thing about cursors and there's nothing I can find to justify it. I was hoping to find here some awkward mechanism that would explain such behavior but nothing until then.


So...

I'm having a big difference in performance when fetching a cursor (implicit or explicit) that results no rows.

Let me give you an example:

FUNCTION a(i_record IN x.id%TYPE) RETURN y.DESC%TYPE IS
l_desc y.DESC%TYPE;
BEGIN
BEGIN
SELECT DESC
INTO l_desc
FROM y
WHERE x = i_record
AND status = 'A';
EXCEPTION
WHEN no_data_found THEN
RETURN NULL;
END;
RETURN l_desc;
END;

FUNCTION b(i_record IN x.id%TYPE) RETURN y.DESC%TYPE IS
l_desc y.DESC%TYPE;
BEGIN
SELECT (SELECT DESC
INTO l_desc
FROM y
WHERE x = i_record
AND status = 'A')
FROM dual;
RETURN l_desc;
END;

DECLARE
CURSOR c_a IS
SELECT a(id)
FROM x
WHERE rownum <= 1000;
CURSOR c_b IS
SELECT b(id)
FROM x
WHERE rownum <= 1000;

TYPE t_a IS TABLE OF c_a%ROWTYPE;
TYPE t_b IS TABLE OF c_b%ROWTYPE;

l_a t_a;
l_b t_b;

BEGIN
OPEN c_a;
FETCH c_a BULK COLLECT
INTO l_a;
CLOSE c_a;

OPEN c_b;
FETCH c_b BULK COLLECT
INTO l_b;
CLOSE c_b;
END;

This is only an example and my "table x" has like 20 columns (no LOB) and more than 100000 rows.
In my test the first fetch took more than 3 times the time the second fetch did for 1000 calls and only one result in table y. And this is happening with many of my tables whenever most of those sub-selects doesn't return any row.

I don't know where to start because the difference is said to be in the "select ... into ..." inside the a and b functions.

The index used is a non-unique index and this is also happening when I use an explicit cursor in functions a and b.

I know it's not a good practice to use a function to do what a sub-select does but take this as an example only. I use a function because I would need to have some logic in there.

Thanks in advance for your answer,
Fábio Oliveira
Tom Kyte
March 29, 2009 - 10:43 am UTC

no tables
no indexing scheme
no tkprofs

nothing to really go on. I'm not going to try to guess, so, how about you provide what I always provide to you - a full test case that completely demonstrates the issue.

Many times, in the construction of such a test, you will smack your forehead and say "Oh, I see why, it is so obvious...."