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.
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.