Stamping Out Cartesian Products


Over the last number of years, I kept finding Cartesian Products (CP) in queries and other objects like views.

These have caused a number of serious performance issues.  A single CP in a query can make the whole server go slow.  The other problem is the result sets, which come back many multiples too big.

No gizmo tool will help you with a CP.  No query analyzer will tell you that you have a problem, or how to fix it.  Sorry mouse lovers.

Cartesian products are one problem area that only observation, knowledge, and good SQL skills will overcome.

When I have pointed out the cartesian products to the rest of the crew, many were in denial.  The really odd thing was that the more “experienced” the database person was, say, over 10 or 15 years of experience, the less obvious it was to them.  And the more they insisted on playing with statistics or indexes.  Which of course did nothing to deal with the real issues.

It became clear that cartesian products are a subject where not a lot is known.   So, I presented at DAMA and the Oracle User Groups on this subject.

You can find the presentation here.

———->

Cartesian Products Presentation – PDF

<———–

It may actually be a little too simple.   But I wrote it for those who didn’t understand.

Do take a read, and help stamp out cartesian products.

About these ads

One Response to Stamping Out Cartesian Products

  1. […] Cartesian products are either misunderstood, or, completely unknown to many database practitioners. And especially non database programmers. See the presentation I made on cartesian products at the Oracle User’s Group. […]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: