Curtesian Join in Execution Plan

Cartesian Join:
--------------------------
•A Cartesian join is used when one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.

•The optimizer uses Cartesian joins when it is asked to join two tables with no join conditions. In some cases, a common filter condition between the two tables could be picked up by the optimizer as a possible join condition.

•In other cases, the optimizer may decide to generate a Cartesian product of two very small tables that are both joined to the same large table.

•Applying the ORDERED hint, instructs the optimizer to use a Cartesian join. By specifying a table before its join table is specified, the optimizer does a Cartesian join.

With the example of creation script of table_a in example Example of Using Hints in Index,
SQL> create table table_e as select level a from dual connect by level<=100;
Table created.

SQL> select a.n, b.a from table_a a , table_e b;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1945448542

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 15M| 627 (2)| 00:00:08 |
| 1 | MERGE JOIN CARTESIAN| | 1000K| 15M| 627 (2)| 00:00:08 |
| 2 | TABLE ACCESS FULL | TABLE_E | 100 | 1300 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 10000 | 30000 | 624 (2)| 00:00:08 |
| 4 | TABLE ACCESS FULL | TABLE_A | 10000 | 30000 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement