Re: Cartesian Product

"Lonnie" <lonnie2000_at_atl.mediaone.net> wrote in message news:OGbK7.7115$gr6.2627607_at_typhoon.jacksonville.mediaone.net...
> 1. What would happen if the two tables that we were
> joining had 300,000 records in each table. What would
> be the total number of rows returned?

If you were joining them on a unique field in each of the tables, anything from 0-300,000 records, depending , and how many records were in both of the tables.

Joins on big tables are not a problem if the proper indexes etc are in place. (e.g. one of the database I am working on at the moment has a procedure that joins a table with 28,000,000 records to a table with 900,000 records. However I rarely want (or get) more than 50-100 records returned.)

>
> 2. What would a Cartesian Product statement do to the
> database if the two tables had 300,000 rows in each
> table?
>

A cartesian Product would return 90,000,000,000 records. (i.e. 300,000*300,000). If you ran this then the following might happen.

1. If the database is rarely being used for updates/deletes/inserts etc, and if you did not try and sort your data, your query would probably succeed (eventually)
2. If the database is frequently updated, it is likely that your select will end in a snapshot too old error. This would happen if the amount of updates exceeded the amount of rollback available. Your query would no longer be able to guarantee a read-consistent view, and would fail with the above error.
3. If you tried to sort the data, you would probably get an error telling you that you have run out of extents in your temporary tablespace. (Of course thie might not happen if you have a LOT of temporary tablespace available)
4. If you have set resource limits in your profile, you might exceed these.

Adrian Received on Wed Nov 21 2001 - 12:06:26 CST