SQL Cartesian Product Tips
The Cartesian product, also referred to as a
cross-join, returns all the rows in all the tables listed in the
query. Each row in the first table is paired with all the rows in
the second table. This happens when there is no relationship
defined between the two tables. Both the AUTHOR and STORE tables
have ten rows. If we use a Cartesian join in these two tables, we
will get back 100 rows.
5 author, store;
100 rows selected.
Most of the time, we do not want a Cartesian
join, and we end up with one because we failed to provide a filter
on the join. If we actually want a Cartesian join, then we should
use the ANSI cross join to tell others reading the script that we
actually wanted a Cartesian join.
author cross join store;
One reason to use a Cartesian join is to
generate a large amount of rows to use for testing. I can take a
large table and cross join it to another large table and produce a
very large results set. If I cross join dba_objects and dba_views,
I can produce the results set below.
SQL> select count(*)
3 dba_objects cross join dba_views;
Thatís a lot of rows!
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.