Performance comparison

In general, one of our query is taking 1 hour of time to run. And we are using this Query as one of the Cursor in PL/SQL Program and used it as cursor for loop. But this program is taking long time to run. So instead of using this cursor sql in for loop, can we load to table type variable and using this table type variable as loop makes performance better than cursor for loop ? Can anyone suggest on this.

You have not provided sufficient details for me to say for certain that a PL/SQL table can be used, but I will tell you that most likely it is possible. A few years ago, I was responsible for improving the performance of an app that was performing large numbers of queries and comparing the results between each other in FOR loops. I used numerous associative arrays to handle the processing and achieved enormous speed improvements.

In general, one of our query is taking 1 hour of time to run. And we are using this Query as one of the Cursor in PL/SQL Program and used it as cursor for loop. But this program is taking long time to run. So instead of using this cursor sql in for loop, can we load to table type variable and using this table type variable as loop makes performance better than cursor for loop ? Can anyone suggest on this.

Click to expand...

As Matthew already stated you haven't provided enough information for a definitive answer, but it MIGHT be possible. This post shows one example of how that can be done: