Home » SQL & PL/SQL » SQL & PL/SQL » BULK COLLECT VS CURSORS
BULK COLLECT VS CURSORS [message #335907] Thu, 24 July 2008 02:40 Go to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Good Morning,
this message is for asking what the best way to speed an existing procedure in my application.
Suppose i've a cursor declared and i use it in this way:
Cursor c is
Select ...
from ..
where ...
order by ...;

riga c%rowtype;

Open c(....);
Loop
fetch c into riga;
exit when c%notfound;
[statements]
End Loop;
Close c;

That's the way i would like to use it :
Cursor c is
Select ...
from ..
where ...
order by ...;

type riga_table is table of c%rowtype index by binary_integer;
riga riga_table;

Open c(....);
fetch c bulk collect into riga;
close c;
for i in 1..c.count Loop
[statements using c(i).field1 .... ]
End Loop;

What is the best way to use the cursor.

Thanks in advance.

Regards,
Giulio
PS: If the information are not clear or not enough, please notify me it. Thanks.

Re: BULK COLLECT VS CURSORS [message #335919 is a reply to message #335907] Thu, 24 July 2008 02:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The best way is to remove cursor loop and do it in one SQL statement.

Regards
Michel
Re: BULK COLLECT VS CURSORS [message #335932 is a reply to message #335919] Thu, 24 July 2008 03:27 Go to previous messageGo to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi Michel,
thanks for the reply.
Please, could you explain how i can do it in one sql statement?
Consider that in the loop there are many rows of code to execute for each record that i fetch from the cursor.

Regards,
Giulio
Re: BULK COLLECT VS CURSORS [message #335935 is a reply to message #335932] Thu, 24 July 2008 03:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You posted a general question, I give a general answer.
The most efficient way depends on what you actually have to do.
If a way was ALWAYS better, there would be no other way.
If a way was ALWAYS worse, this way would not exist.

Regards
Michel
Re: BULK COLLECT VS CURSORS [message #335937 is a reply to message #335935] Thu, 24 July 2008 03:37 Go to previous messageGo to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Sorry Michel,
i believe you were speaking about of my question in particular.
If there were just one way, event this forum would be useless Smile

Regsrds,
Giulio
Re: BULK COLLECT VS CURSORS [message #336146 is a reply to message #335907] Fri, 25 July 2008 00:45 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Have you seen this example:-

http://www.orafaq.com/scripts/plsql/bulkbind.txt

Alway break your bulk binds into chunks where you have a large dataset .
Re: BULK COLLECT VS CURSORS [message #336156 is a reply to message #336146] Fri, 25 July 2008 01:11 Go to previous messageGo to next message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi coleing,
thanks a lot for your post.
Please, could you tell the difference between:
-- My way
for t in 1..TableOfMemory.count loop
...
end loop
-- Your way
for t in TableOfMemory.FIRST..TableOfMemory.LAST
...
end loop

Regards,
Giulio
Re: BULK COLLECT VS CURSORS [message #336158 is a reply to message #336156] Fri, 25 July 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Generally speaking, it is possible that first is not 1 and last is not count.
But not in the case of bulk collect (at least in the current versions).
More it is dangerous is you use "TableOfMemory.FIRST..TableOfMemory.LAST" as you can have an error if there is no record in the table which is not the case with "1..TableOfMemory.count" (of course you could trap the error in exception block).

Regards
Michel

[Updated on: Fri, 25 July 2008 01:17]

Report message to a moderator

Re: BULK COLLECT VS CURSORS [message #336199 is a reply to message #336158] Fri, 25 July 2008 03:05 Go to previous message
gdottorini
Messages: 23
Registered: November 2006
Location: Napoli
Junior Member

Hi Michel,
thanks for the explanation.

Regards,
Giulio
Previous Topic: Altering Table with an Indexed Column
Next Topic: Optimize the query
Goto Forum:
  


Current Time: Thu Dec 08 14:06:02 CST 2016

Total time taken to generate the page: 0.07076 seconds