Cartesian product in the query

 

There should not be any Cartesian product in the query unless there is a definite requirement to do so. I know this is a silly point but we all have done this mistake at one point.

This is one of the most common SQL mistakes made by DBAs and developers.

What is a Cartesian Product?

A Cartesian Product occurs when two tables are joined without a proper join condition.

Oracle combines every row from Table A with every row from Table B.

Formula:

Rows Returned = Rows in Table A × Rows in Table B

---

Sample EMP Table

EMPNO	ENAME	DEPTNO
7369	SMITH	20
7499	ALLEN	30
7521	WARD	30
7566	JONES	20
7698	BLAKE	30

Total Rows = 5

---

Sample DEPT Table

DEPTNO	DNAME
10	ACCOUNTING
20	RESEARCH
30	SALES

Total Rows = 3

---

Scenario 1 (Wrong Query - Cartesian Product)

Developer writes:

SELECT e.empno,
       e.ename,
       d.dname
FROM emp e,
     dept d;

Notice:

NO JOIN CONDITION

Missing:

e.deptno = d.deptno

---

What Oracle Does

For each employee:

SMITH

Oracle pairs with:

ACCOUNTING
RESEARCH
SALES

Then:

ALLEN

pairs with all departments.

Result:

EMPNO	ENAME	DNAME
7369	SMITH	ACCOUNTING
7369	SMITH	RESEARCH
7369	SMITH	SALES
7499	ALLEN	ACCOUNTING
7499	ALLEN	RESEARCH
7499	ALLEN	SALES
...	...	...

Rows returned:

5 Employees × 3 Departments
= 15 Rows

instead of 5 rows.

---

Explain Plan

EXPLAIN PLAN FOR
SELECT e.empno,
       e.ename,
       d.dname
FROM emp e,
     dept d;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan:

--------------------------------------------------
| Id | Operation              | Name             |
--------------------------------------------------
|  0 | SELECT STATEMENT       |                  |
|  1 |  MERGE JOIN CARTESIAN  |                  |
|  2 |   TABLE ACCESS FULL    | EMP              |
|  3 |   BUFFER SORT          |                  |
|  4 |    TABLE ACCESS FULL   | DEPT             |
--------------------------------------------------

Important line:

MERGE JOIN CARTESIAN

Oracle is warning:

I found no join condition.
I am multiplying rows.

---

Layman Example

Imagine:

EMP Table

5 employees

A
B
C
D
E

DEPT Table

3 departments

HR
IT
Finance

Oracle produces:

A-HR
A-IT
A-Finance

B-HR
B-IT
B-Finance

C-HR
C-IT
C-Finance

D-HR
D-IT
D-Finance

E-HR
E-IT
E-Finance

Total:

15 combinations

This is Cartesian Product.

---

Scenario 2 (Correct Query)

SELECT e.empno,
       e.ename,
       d.dname
FROM emp e,
     dept d
WHERE e.deptno = d.deptno;

Result:

EMPNO	ENAME	DNAME
7369	SMITH	RESEARCH
7499	ALLEN	SALES
7521	WARD	SALES
7566	JONES	RESEARCH
7698	BLAKE	SALES

Only:

5 rows

returned.

---

Explain Plan

---------------------------------------------------
| Id | Operation            | Name               |
---------------------------------------------------
|  0 | SELECT STATEMENT     |                    |
|  1 | HASH JOIN            |                    |
|  2 | TABLE ACCESS FULL    | EMP                |
|  3 | TABLE ACCESS FULL    | DEPT               |
---------------------------------------------------

Notice:

HASH JOIN

instead of:

MERGE JOIN CARTESIAN

Good sign.

---

Real Production Disaster Example

Suppose:

ORDERS

10 Million Rows

CUSTOMERS

1 Million Rows

Developer writes:

SELECT *
FROM orders o,
     customers c;

Missing join:

o.customer_id = c.customer_id

Oracle tries:

10,000,000 × 1,000,000

=

10,000,000,000,000
(10 Trillion Rows)

Consequences:

• Huge CPU usage
• TEMP tablespace fills
• Query runs for hours
• System slowdown
• Other sessions get impacted

This is why DBAs fear Cartesian Products.

---

When Cartesian Product is Intentional

Sometimes it is required.

Example:

Generate all combinations.

SELECT color,
       size
FROM colors,
     sizes;

Colors:

Red
Blue

Sizes:

S
M
L

Result:

Red-S
Red-M
Red-L
Blue-S
Blue-M
Blue-L

Here Cartesian Product is intentional.

---

Interview Question

Q. How do you identify a Cartesian Product in an Explain Plan?

Answer:

Look for:

MERGE JOIN CARTESIAN

or check whether the query is joining multiple tables without a join condition.

---

DBA Golden Rule

Whenever a query joins multiple tables:

FROM emp e,
     dept d

Immediately verify:

WHERE e.deptno = d.deptno

or

JOIN dept d
ON e.deptno = d.deptno

If not, Oracle may create a Cartesian Product, causing row multiplication, excessive CPU usage, large TEMP consumption, and severe performance problems.