PL/SQL Performance Question

I have a simple question regarding the most efficient manner to "process" a set of rows. There are two different ways to do this and I want to understand the pros and cons of each method. This logic would be run as a stored procedure on the DBMS server, so I think all client related issues can be ignored.

Assume I have a table called "Widget" and another table called
"WidgetParts". The "WidgetParts" table is dependant on the "Widget" table.
In other words, a row on the "Widget" table could have zero or more rows on the "WidgetParts" table.

Now assume that I have some number of widgets whose parts I must "process". What are the pros and cons for these two methods of cycling through all of the widget parts:
1) Use a single cursor whose query joins the "Widget" table to the
"WidgetParts" table. If I had 500 widgets, each with 60 parts, then this
cursor would cycle through 30000 rows.
2) Use two cursors, one nested within the other. The first cursor would select each of the rows from the "Widget" table. In the example above, this cursor would cycle through 500 rows. The second cursor would be opened for each row fetched in the first cursor, and it would select the "WidgetParts" rows for each widget. Using the example above, this inner cursor would return 60 rows each time.

It seems to me that the resources available to the DBMS server would greatly affect the performance of method #1. What I am most curious about is how expensive it is to open and close a cursor repeatedly (as in method #2). Method #2 appears that it would be much less concerned about the resources available to the DBMS server, but will it run a little slower or exponentially slower?

Any feedback on this will be much appreciated.

Thanks,
Todd Dixon. Received on Tue Jun 16 1998 - 12:43:39 CDT