|
 |
|
Oracle Tips by Burleson |
Cartesian Product
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.
SQL> select
2 author_key,
3 store_key
4 from
5 author, store;
AUTHOR_KEY STOR
----------- ----
A101 S101
A101 S102
A101 S103
A101 S104
A101 S105
A101 S106
A101 S107
A101 S108
A101 S109
A101 S110
A102 S101
A102 S102
…
A110 S105
A110 S106
A110 S107
A110 S108
A110 S109
A110 S110
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.
select
author_key,
store_key
from
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(*)
2 from
3 dba_objects cross join dba_views;
COUNT(*)
----------
164623840
That’s a lot of rows!
The above book excerpt is from:
Easy Oracle
SQL
Get Started
Fast writing SQL Reports with SQL*Plus
ISBN 0-9727513-7-8
Col. John Garmany
http://www.rampant-books.com/book_2005_1_easy_sql.htm |
