Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008

    Unanswered: which one is faster implicit/explicit?


    You should always use explicit cursors (CURSOR <cursor_name> IS) and avoid implicit cursors (SELECT INTO) entirely, because explicit cursors are always more efficient.
    PLSQL: Controlling Mythological Code

    The short answer is that implicit cursors are faster
    Implicit vs. Explicit Cursors which is faster - cursor_comparison.sql script

    I tested and satisfied myself that implicit cursors are better than explicit cursors

    I m confused,which one is faster?

    Beyond these I have another questions ,suppose I export data
    -I can run sql in toad and export it
    -I can develop plsql procedure and export it
    which one is faster

    Best Regards
    Last edited by sunsail; 07-17-09 at 08:12.

  2. #2
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    I didn't test it myself, but if I had to choose between Donald Burleson (your second link) and Tom Kyte (your last link), I'd rather believe what Tom says.

  3. #3
    Join Date
    Feb 2009
    You need to read for comprehension, rather than for sound bites.

    The first article is one debunking some oracle myths.

    If you include the line above the one you quoted, then you get this quote:
    For years, many experts in the Oracle world (including me) preached a fairly rigid dogma about explicit and implicit cursors. The dogma went like this:

    You should always use explicit cursors (CURSOR <cursor_name> IS) and avoid implicit cursors (SELECT INTO) entirely, because explicit cursors are always more efficient.
    Already, it starts to sound less like a piece of advice.

    If we include the paragraph following yours, we see that you've got the point of the article exactly wrong:
    Doesn't that sound logical and convincing? The problem is that although it might have been true a long time ago in Oracle6, it certainly isn't true for Oracle8i, Oracle9i, or Oracle 10g. In fact, implicit cursors often run faster than explicit cursors. That explicit cursors are always going to be more efficient is one of the most enduring of PL/SQL myths, because it was propagated early and widely.

  4. #4
    Join Date
    Jul 2003
    This is a perfect case/question that Tom Kyte preaches constantly:

    I would not rely on Burleson for anything reliable. He tests little and his information is frequently incorrect. All he does is cut/paste from the oracle docs (if that).

    THINK about what a cursor is and what it does.
    THINK about how oracle processes a cursor.

    If you know how oracle works and you benchmark and test then you will have your answer for yourself and will have the documentation to prove it to anyone in your organization.

    This reminds me of a co-worker who said:
    running insert,update,delete statements are way faster than MERGE.

    I said: What makes you say that?

    He said: I read it. It's a known fact.

    I said: PROVE IT. Benchmark this "known fact".

    Guess what we found out?
    Your best answer will be one you test.
    MAINLY because someone will always ask you: Why did you implement it like that?
    You can then say: I tested and benchmarked it. I chose the best option based on my findings. Let me forward you my results.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts