Re: Join execution order

From: Lecter23 <lecter23_at_gmail.com>
Date: Thu, 05 Jul 2007 05:21:30 -0700
Message-ID: <1183638090.226680.286880@c77g2000hse.googlegroups.com>

Hi Charles.

Thank you SO MUCH for your time.

I undestand your example, and in this case, itīs normal to apply WHERE conditions before, because the condition field and the join field are the same, and Oracle can filter by this field in both tables. But let īs see my query:

SELECT UserName, CountryName
FROM USERS U
LEFT JOIN COUNTRIES C ON U.IDCOUNTRY = C.ID WHERE Admin = 1

The join field is ID_COUNTRY, but the WHERE field is Admin. Now oracle canīt filter by admin in COUNTRIES table.

Returning to your example, what happens if SQL is...

SELECT /*+ GATHER_PLAN_STATISTICS */

  T5.C1,
  T5.C2,
  T6.C2

FROM
  T5,
  T6
WHERE
  T5.C1=T6.C1
  AND T6.C2 < 1234

Filter by C2 < 1234 and make 1234 joins with C1? or make 10000 joins with C1 and then filter?

Thank you! Received on Thu Jul 05 2007 - 07:21:30 CDT