Cursor Question
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Cursor Question

  1. #1
    Join Date
    Sep 2000
    Posts
    77
    I have a table with 100,000 rows. I have a col status_cd with possible values of ( 'I' OR 'P'). I want to get min transaction_id of column with value P.
    My question is , is it better to have a either

    1. Cursor c1 is select * from table where status_cd = 'P'
    order by transaction_id desc ;

    open cursor and get first row and exit from cursor

    2. select min(transcation_id) from table where status_cd = 'P' ;


    Which one runs faster and why if i have index on status_cd.

    Thanks in advance


    Thanks

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    First thing, if you want "min", you should have your cursor def as "order by trans_id asc" and not "order by trans_id desc". I think this just a minor thing.

    On performance front, I think both will be the same. You may get better result if you have the IOT.
    svk

  3. #3
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    I think the second choice will be better. There are overheads in fetching from a cursor and also the first query will do a sort which will be time consuming.

    Thanks.

    Syed

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Using Hint clause FIRST_ROW in the select statement will be faster than any other methods. The 2nd choice is using cursor.

  5. #5
    Join Date
    Sep 2000
    Posts
    77
    Can i use hint FIRST_ROWS even if i use a MIN function in the select statemtent?
    Thanks

  6. #6
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    If the STATUS_ID column values are evenly distributed, index won't help performance. In this case, avoid using index.

    You can use FIRST_ROWS hint in any SELECT statement.

  7. #7
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    A couple of points here -

    1. The FIRST_ROWS hint probably won't help you here since you are only returning 1 row. The first row is also the last row is also ALL_ROW. In order to select the MIN value, a full table scan will be required (unless you have an index on the transaction_id column and can get it worked into the WHERE clause!). Also, since FIRST_ROWS and ALL_ROWS both invoke the cost based optimizer, if you don't have statistics generated on the table, Oracle will revert to rule based optimizer and ignore the hint.

    2. Since the min function does a sort, you are going to get a sort done with either method.

    3. Which method is most suitable depends on what you are trying to do. However, the simple sql statement will be less burdensome since it minimizes the amount of code you have to generate and maintain. Also, as previously pointed out, it involves less overhead on your system.

    4. Finally, since they are both fairly trivial scenarios to code, why not just code them up and see firsthand if there is any significant difference? An ounce of experimentation is worth a pound of "expert opinions"! ;-)

  8. #8
    Join Date
    Sep 2000
    Posts
    77
    Is there any internal logic in oracle that when i use a cursor
    it marks all the rows produced by cursor stmt and when i open cursor and fetch them it will return marked rows and also if select stmt results in 100,000 rows it will take time to mark all the rows ? And also If i execute the same cursor as simple select stmt (instead of a cursor) oracle won't take time to mark them and it will be faster. This is not my opininon but somebody argued with me. Is this true ?
    Thanks

  9. #9
    Join Date
    May 2000
    Location
    Richardson, Texas, USA
    Posts
    39
    Carp,

    Why should the MIN function do a sort? I think one scan is good enough the find the min/max value. Does Oracle say that they do a sort for finding the MIN/MAX? Correct me if I'm wrong.

    Thanks.

    Syed

  10. #10
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    As to "why" they do a sort, I don't know. TJTWOW (That's Just The Way Oracle Works).
    However, I tried several min/max statements, looked at the execution plans, and they all included sorts - so I would conclude it does a sort.

Posting Permissions

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



Click Here to Expand Forum to Full Width