Re: PL/SQL Performance Question

Hi,
Huh???
I think someone told you something wrong at one time. If you have a simple master/slave table construction as you mention, you INDEX the primary and foriegn keys (sometimes you don't index the foreign keys, but it doesn't matter right now). When you join using indexes, there is NO cartisan product ... which means Oracle does NOT process the product of the rows of the two tables involved. It has several options depending on how your indexes are constructed, but usually it chooses an index merge of somekind. This merge usually shortens the amounts of rows enourmously ...

NEVER "join" using two cursors. It will NEVER pay off. You use a database to solve these kind of problems, not to give you more problems. I've joined milions of rows with milions of rows and gotten answers in manners of 10ths of a second.

A table is not a file .. I've seen this mistake so many times and it's usually the biggest cause for performence problems, when a programmer mistakes a table for a file and makes his programs accordingly.

But always make sure, that your DBA knows his job by creating indexes so joins will work.

• Peter H. Larsen Oracle Consultant

Todd Dixon wrote:
>
> 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 - 13:17:13 CDT