Need Help With Commit in Cursor Loop - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 14 of 14

Thread: Need Help With Commit in Cursor Loop

  1. #11
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by mber
    The first one is more efficient, not fuction call overhead.

    Take care
    Wrong, the second one is more efficient!

    Remember that MOD is a built-in function, it will require no more of procesor's time as incrementing a variable! None of those two two operations will require more than one processor tick (is this the right term for granularity of processor's time in which a single instruction can be executed?).

    But on 200 M row table and commiting every 1.000 records, the variant with the explicit counter variable will have to execute "v_help := 0;" 200.000 times! This is overhead!

    In any case, I don't belive the difference in the performance on either two cases will be mesurable at all! The point is, the use of %ROWCOUNT is much less error-prone than maintaining a special variable for this. I'm sure somewhere in the cours of our PL/SQL careers every one of us have made the same error as both posters of PL/SQL code have made - to forget to reset the counter. With MOD() and %ROWCOUNT you simply can't forget, as there is nothing to reset.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #12
    Join Date
    Jul 2000
    Posts
    243
    thank you jmodic. it was very interesting to read.

  3. #13
    Join Date
    Oct 2000
    Posts
    90
    In my defence, I didn't forget to reset the counter, I had copied the code from the person above only to show how a CURSOR ... FOR ... LOOP works. I still am of the opinion that the SQL COPY command is the best option but I am open to being shot down in flames.

  4. #14
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by m1l
    In my defence, I didn't forget to reset the counter, I had copied the code from the person above only to show how a CURSOR ... FOR ... LOOP works. I still am of the opinion that the SQL COPY command is the best option but I am open to being shot down in flames.
    I agrea with you about the COPY command and it's efficiency and usefullness. However it has one limitation: it is only a SQL*Plus command (not a SQL command, as you said), so you can execute it only from SQL*Plus. This is pretty severe limitation, mybe not in this particular case, but in general.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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