Oracle Database Administration: The Essential Refe

Cartesian Product

A Cartesian product is the result of joining every row in one table with every row in another table. This occurs when there is no WHERE clause to restrict rows. While this is legitimate in some cases, most occurrences of a Cartesian product are mistakes. The Cartesian product is somewhat analogous to a correlated subquery—that is, it performs a full table scan on the second table for every row in the first table. An example of a Cartesian product is:

SELECT ename,empno,dname 
FROM emp,dept;

In this example, the result would have a row for each employee listed as being in every department. In the standard emp and dept tables, there are 14 employees and 4 departments. This leads to a result set of 56 rows. If you have a larger case, where you are joining two tables with 10,000 rows each, you end up with 100,000,000 rows. It is important to understand when the Oracle optimizer will attempt to perform a Cartesian product and, if so, what the impact will be.