MERGE JOIN (CARTESIAN)

Description

Performs a cartesian join between two tables

Versions

This operation is implemented in the following versions

Example

Note that at least one of the tables must be analyzed otherwise a NESTED LOOPS execution plan is executed.

This example was developed using Oracle 9.2.0.1 on Windows 2000

This example requires the following table definition

    CREATE TABLE t1 (c1 NUMBER);
    CREATE TABLE t2 (c1 NUMBER);
	

The table does not need to be analysed

The statement

    SELECT t1.c1,t2.c1
    FROM t1,t2;

In Oracle 9.2 generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   MERGE JOIN (CARTESIAN)
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     SORT (JOIN)
4   3       TABLE ACCESS (FULL) OF 'T2'

In Oracle 10.2 generates the following execution plan

0     SELECT STATEMENT Optimizer=CHOOSE
1   0   MERGE JOIN (CARTESIAN)
2   1     TABLE ACCESS (FULL) OF 'T1'
3   1     BUFFER (SORT)
4   3       TABLE ACCESS (FULL) OF 'T2'