Re: Cursor performance in Oracle

Hi Paul,
I would guess not. Assuming we are talking about a pl/sql context, there are two different types of cursors
implicit and explicit.
You will get an implicit cursor for statements like select ename from emp where empno = 10
ie the statement can retrieve only one single row Explicit cursors are coded by the developer cursor sel_emp is
select ename from emp where empno > 10
in the declaration
and called by
open sel_emp
fetch sel_emp into something
etc.
For implicit cursors the first danger is to forget to trap the no data found situation. Many inexperienced developers think this would never happen (the records we know we have, are always there, and operators don't make mistakes). However, to get this construct robust you need to code begin
select ename from emp
where empno = 10;
exception
  when no_data_found then
    <something>;
 when others then

    <etc>
It is even more dangerous because constructs like this are usually sprinkled liberally through PL/SQL code, even if the statement is exactly the same. Oracle will detect this is the same statement in the library cache, yet at the client side, it will still use multiple cursors. So the guideline is: try to avoid implicit cursors as much as possible and use explicit cursors everywhere. In Oracle a cursor is basically a handle, and it is used always, there is no difference in performance, yet the performance of your package can decrease if you are using implicit cursors everywhere.

Hth,

Sybrand Bakker, Oracle DBA
NB: If you want to read a real good book about PL/SQL programming, get the books of Steve Feuerstein published by O'Reilly Paul Davies wrote in message <375d20ad_at_newsread3.dircon.co.uk>...
>I'm migrating a system from Sybase to Oracle. In Sybase, one avoided
cursors
>when possible as they were many, many times slower than writing a straight
>query. Is this also true in Oracle?
>
>
Received on Tue Jun 08 1999 - 11:53:16 CDT