What is the use of bulk collect in oracle?

What is the use of bulk collect in oracle?
how this increases performance?
sakthikumarAsked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
let us say you have a cursor declared but how do you traverse the record set in the cursor. You need to iterate a loop and for every iteration you fetch a record from the database. Only when loop iteration happens ( while using cursor for loops ) or fetch happens in the loop, the call goes to database to fetch the value.

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
bulk collect/fetch are used to fetch more than 1 record at a time.

For example, you can fetch 1000 records at a time into an array. If you fetch one record at a time, you need to fetch 1000 times to fetch all 1000 records where as with bulk collect/fetch you can just do it with one fetch.

Technically speaking, with one call to database you can fetch more than 1 record at a time by using bulk collect/fetch. If you are going to hit the database less number of times for fetching the data, then it obviously means the performance is going to increase right. You save time/resources right.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
http://psoug.org/reference/array_processing.html  - for more information with examples.
0
 
sakthikumarAuthor Commented:
How can we say, it will go to db all the times, for eg. in a cursor,  the results will be in the memory or buffer right? and for every fetch, it need to get from memory only. correct or not?
0
All Courses

From novice to tech pro — start learning today.