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

    Unanswered: which one is faster implicit/explicit?

    Hello

    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
    http://asktom.oracle.com/pls/asktom/...:1205168148688

    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 09:12.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    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
    Posts
    62
    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
    Posts
    2,296
    This is a perfect case/question that Tom Kyte preaches constantly:
    TEST FOR YOURSELF! DO NOT RELY ON OTHERS INFORMATION.
    BENCHMARK!


    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
  •