Re: Where clause and cartesian product

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Wed, 14 Dec 2005 17:17:56 -0500
Message-ID: <9Lidnc8YmswZBz3enZ2dnUVZ_vmdnZ2d@comcast.com>

"Ed Prochak" <ed.prochak_at_magicinterface.com> wrote in message news:1134596216.406251.325420_at_f14g2000cwb.googlegroups.com...
> 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
>

Ed,

Even if you ignore how a particular database implements the join processing, it never will begin with a cartesian product, it will be an actual join based on the relational expression provided.

To illustrate, any database that processes the following join will 'logically' work with the dataset as shown:

SQL> select *
2 from emp join dept using (deptno);

    DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE         SAL 
COMM DNAME          LOC

---------- ---------- ---------- --------- ---------- --------- ---------- ----------

        10       7839 KING       PRESIDENT            17-NOV-81       5000 
ACCOUNTING     NEW YORK
        10       7782 CLARK      MANAGER         7839 09-JUN-81       2450 
ACCOUNTING     NEW YORK
        10       7934 MILLER     CLERK           7782 23-JAN-82       1300 
ACCOUNTING     NEW YORK
        20       7566 JONES      MANAGER         7839 02-APR-81       2975 
RESEARCH       DALLAS
        20       7902 FORD       ANALYST         7566 03-DEC-81       3000 
RESEARCH       DALLAS
        20       7369 SMITH      CLERK           7902 17-DEC-80        800 
RESEARCH       DALLAS
        20       7788 SCOTT      ANALYST         7566 09-DEC-82       3000 
RESEARCH       DALLAS
        20       7876 ADAMS      CLERK           7788 12-JAN-83       1100 
RESEARCH       DALLAS
        30       7698 BLAKE      MANAGER         7839 01-MAY-81       2850 
SALES          CHICAGO
        30       7499 ALLEN      SALESMAN        7698 20-FEB-81       1600 
300 SALES          CHICAGO
        30       7521 WARD       SALESMAN        7698 22-FEB-81       1250 
500 SALES          CHICAGO
        30       7900 JAMES      CLERK           7698 03-DEC-81        950 
SALES          CHICAGO
        30       7654 MARTIN     SALESMAN        7698 28-SEP-81       1250 
1400 SALES          CHICAGO
        30       7844 TURNER     SALESMAN        7698 08-SEP-81       1500 
0 SALES          CHICAGO

14 rows selected.

if it were working with a cartesian product, it would begin by working with a dataset like this:

SQL> select *
2 from emp, dept;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM 
DEPTNO     DEPTNO DNAME          LOC

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

--- 7839 KING PRESIDENT 17-NOV-81 5000 10 10 ACCOUNTING NEW YORK 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 10 ACCOUNTING NEW YORK 7566 JONES MANAGER 7839 02-APR-81 2975 20 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 10 ACCOUNTING NEW YORK 7902 FORD ANALYST 7566 03-DEC-81 3000 20 10 ACCOUNTING NEW YORK 7369 SMITH CLERK 7902 17-DEC-80 800 20 10 ACCOUNTING NEW YORK 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 10 ACCOUNTING NEW 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 10 ACCOUNTING NEW 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 10 ACCOUNTING NEW 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 10 ACCOUNTING NEW YORK 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 10 ACCOUNTING NEW YORK 7900 JAMES CLERK 7698 03-DEC-81 950 30 10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 23-JAN-82 1300 10 10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 17-NOV-81 5000 10 20 RESEARCH DALLAS 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 20 RESEARCH DALLAS 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 20 RESEARCH DALLAS 7566 JONES MANAGER 7839 02-APR-81 2975 20 20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 20 RESEARCH DALLAS 7902 FORD ANALYST 7566 03-DEC-81 3000 20 20 RESEARCH DALLAS 7369 SMITH CLERK 7902 17-DEC-80 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 20 RESEARCH DALLA 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 20 RESEARCH DALLA 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 20 RESEARCH DALLA 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 03-DEC-81 950 30 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 23-JAN-82 1300 10 20 RESEARCH DALLAS 7839 KING PRESIDENT 17-NOV-81 5000 10 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 30 SALES CHICAGO 7566 JONES MANAGER 7839 02-APR-81 2975 20 30 SALES CHICAGO 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 30 SALES CHICAGO 7902 FORD ANALYST 7566 03-DEC-81 3000 20 30 SALES CHICAGO 7369 SMITH CLERK 7902 17-DEC-80 800 20 30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 30 SALES CHICAGO 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 30 SALES CHICAGO 7900 JAMES CLERK 7698 03-DEC-81 950 30 30 SALES CHICAGO 7934 MILLER CLERK 7782 23-JAN-82 1300 10 30 SALES CHICAGO 7839 KING PRESIDENT 17-NOV-81 5000 10 40 OPERATIONS BOSTON 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 40 OPERATIONS BOSTON 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 40 OPERATIONS BOSTON 7566 JONES MANAGER 7839 02-APR-81 2975 20 40 OPERATIONS BOSTON 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 40 OPERATIONS BOSTON 7902 FORD ANALYST 7566 03-DEC-81 3000 20 40 OPERATIONS BOSTON 7369 SMITH CLERK 7902 17-DEC-80 800 20 40 OPERATIONS BOSTON 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 40 OPERATIONS BOS 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 40 OPERATIONS BOS 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 40 OPERATIONS BOS 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 40 OPERATIONS BOSTON 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 40 OPERATIONS BOSTON 7900 JAMES CLERK 7698 03-DEC-81 950 30 40 OPERATIONS BOSTON 7934 MILLER CLERK 7782 23-JAN-82 1300 10 40 OPERATIONS BOSTON

56 rows selected.

Cartesian implies no join condition, return all possible combinations. Not part of the 'logical' (or physical) processing when an actual join condition is supplied.

++ mcs Received on Wed Dec 14 2005 - 16:17:56 CST