Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: cursors vs views in Oracle

    Hi,
    In an Oracle database schema, what is a more effecient way to collect data. Would it be better to use a cursor within a procedure, or a view. This question is based on the backdrop of a database which is quiet crunched. So which one would be less task intensive given the fact that it is going to run every 3 seconds.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It depends.
    The proof is in the pudding.
    ALTER SESSION SET SQL_TRACE=TRUE
    run both cases and use TKPROF to see which is better for your situation.

  3. #3
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: cursors vs views in Oracle

    Depends on what you mean by collect? Do you want to interact with the data ie update, delete, etc, do you want to return it to a front-end ie display it on a screen or web page, do you want to parse it?

  4. #4
    Join Date
    Nov 2003
    Posts
    76
    the data is ultimately going to be displayed on a front -end screen.

  5. #5
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    If all you want to do is display the data to the screen then a view would probably be the way to go. No sense iterating through it in code if you can just select it out of DB and present it.

  6. #6
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    HI,

    Just to display the data, View will be a right choice.
    SATHISH .

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hmm,

    You can return a ref cursor from a procedure without iterating it. Also most ref cursors are weak cursors which means Oracle can do better sharing on them (being weak it knows it can safely share the cursor between different sessions).

    If we're talking view (as opposed to Materialised View), it and the cursor both require the underlying SQL to be executed for each select.

    If the view is updateable, Oracle doesn't know if the view will be updated. If the cursor is weak Oracle knows it can't be updated, hence more efficient sharing of the cursor.

    Hth
    Bill

Posting Permissions

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