Re: Where clause and cartesian product

"Hari" <excel_hari_at_yahoo.com> wrote in message news:1134457314.403955.8310_at_o13g2000cwo.googlegroups.com...
> 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
>

yikes! get a new teacher! really! if he/she doesn't know how it works he/she has no business teaching certification courses.

if you're serious about learning oracle, read the oracle concepts manual (it's free at http://tahiti.oracle.com) and the tuning guide

you'll see that orale NEVER does a cartesian product as the first step of a join unless there is no join criteria

so

select *
from emp, dept

gets a cartesian product

select *
from emp. dept
where job = 'CLERK'

gets a cartesian product

select *
from emp join dept using (deptno)

does NOT product a cartesian product (use SET AUTOTRACE ON in SQL*Plus to verify how Oracle processes the query)

++ mcs Received on Tue Dec 13 2005 - 02:59:16 CST