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

    Unanswered: cursor performance advantage

    Hello,

    I have a very long sql query,and it contains 'like name '%aaa%' kind of filters.
    I m wondering if I put this sql into plsql cursor and get it output from procedure,would it be faster? if so how can I measure it
    I like to compare this sql runtime with plsql runtime.
    I have toad and oracle sql developer tools

    Best Regards

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool No improvement

    Changing SQL statement to execute in PL/SQL cursor would not improve the response time.

    For queries that contain 'like name '%aaa%' kind of filters, perhaps using Oracle text indexes would help.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2008
    Posts
    99
    Hello,
    I found this and I m using in my situation
    Implicit vs. Explicit Cursors which is faster - cursor_comparison.sql script

    first I put filter in cursor definition
    cursor c_1 is
    select ...where like...;

    secondly I put in the code
    begin...

    open c_1
    fetch ...into...;
    if ..like then
    end if
    close c_1

    does this work in this case? for the same test time comsumption changes :/

    Best Regards

  4. #4
    Join Date
    Feb 2009
    Posts
    62
    No - Fetching every row from the table and manually filtering them to see which ones match the condition will be slower than using a query that only fetches the rows that match the condition in the first place.

Posting Permissions

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