| |
 |
|
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.
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!
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
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
buy it
for 30% off directly from the publisher.
|
|
and include the URL for the page.
