Re: Where clause and cartesian product

Hari wrote:
> Hi,
>
> Im new to databases/Oracle.
>
> Recently I enrolled in an Oracle certification course. The Oracle
> course material says that usage of Where clause is preffered as it
> avoids a cartesian product (thus overloading the query). One colleague
> in the class said that even if one uses a Where clause in a SQL
> statement involving 2 tables, the oracle system first does a cartesian
> product of the 2 tables and then applies the where condition the ouput
> and displays it to the user (my teacher isnt sure about how it works).
> How does it work in reality?
>
> regards,
> Hari
> India

If you are just now learning SQL for the first time, than I say, don't worry about how ORACLE finds the result set. What the DBMS does internally is not your concern at the moment. Learn the logical operations of SQL and you'll understand better how to use more than just ORACLE.

So logically a join is first a cartesian product of the data sets represented by the two tables. the where clause then is a logical filter, eliminating result rows that do not match the conditions. Keep in mind that SQL is a layman's language for performing SET Algebra. Trying to figure out the optimizations at this point is only going to slow you down in the long run.

   Ed Received on Wed Dec 14 2005 - 15:36:56 CST