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
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?
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 ?
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
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.
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?
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
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
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)
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 ...
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)?
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.
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.
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.
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.
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.
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?
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.
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)
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.
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;
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 ||
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??/
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 ???
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????
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
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
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?
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?
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.
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.
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
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 Developers Guide, RELEASE 11i VOLUME 1
The part number for this volume is A8370503.
To reorder this book, please use the set part number, A7554503.
</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!
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
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
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
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?
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
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
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;
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?
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) ?
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
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.
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
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...."