Re: Join execution order

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 05 Jul 2007 04:36:50 -0700
Message-ID: <1183635410.222557.60830@m36g2000hse.googlegroups.com>

On Jul 5, 4:26 am, Lecter23 <lecte..._at_gmail.com> wrote:
> Hi everybody.
>
> My question is: Joins are executed after or before applying WHERE
> clause?
>
> I have 2 tables:
>
> USERS
> Id (PK)
> UserName
> IdCountry (FK -> COUNTRIES)
> Admin
>
> COUNTRIES
> Id (PK)
> CountryName
>
> And i have this SQL:
>
> SELECT UserName, CountryName
> FROM USERS U
> LEFT JOIN COUNTRIES C ON U.IDCOUNTRY = C.ID
> WHERE Admin = 1
>
> This query returns about 100 rows of 20000 total rows in users table.
> I need to know if Oracle applies WHERE and then LEFT JOIN with only
> 100 rows or make the join with all 20000 users and then filters
> admins.
>
> Thanks in advance!

An explain plan will likely be helpful to determine how the WHERE clause is applied. For example:
Set up 2 tables for testing with 10,000 rows each, one with a primary key (and associated index) and the second without a primary key: CREATE TABLE T5(
  C1 DATE,
  C2 NUMBER(12),
  PRIMARY KEY (C1)); CREATE TABLE T6(
  C1 DATE,
  C2 NUMBER(12)); INSERT INTO
  T5
SELECT
  TRUNC(SYSDATE)+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

INSERT INTO
  T6
SELECT
  TRUNC(SYSDATE)+ROWNUM,
  ROWNUM
FROM
  DUAL
CONNECT BY
  LEVEL<=10000;

Now, let's create a simple SQL statement to join the two tables together, placing a restriction on the column T6.C1, which does not have an index:
SELECT /*+ GATHER_PLAN_STATISTICS */

  T5.C1,
  T5.C2,
  T6.C2

FROM
  T5,
  T6
WHERE
  T5.C1=T6.C1
  AND T6.C1<=TRUNC(SYSDATE+500);

Let's look at the DBMS Xplan for this SQL statement: SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

|* 1 | HASH JOIN | | 1 | 500 | 500 |00:00:00.01 | 67 | 1155K| 1155K| 1174K (0)|

|*  2 |   TABLE ACCESS FULL| T5   |	 1 |	500 |	 500 |00:00:00.01 |	 31
|	    |	    |	       |
|*  3 |   TABLE ACCESS FULL| T6   |	 1 |	500 |	 500 |00:00:00.01 |	 36
|	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("T5"."C1"="T6"."C1")
   2 - filter("T5"."C1"<=TRUNC(SYSDATE@!+500))
   3 - filter("T6"."C1"<=TRUNC(SYSDATE@!+500))

dynamic sampling used for this statement

We have a full tablescan on each of the tables, and then the rows of the two tables are joined together using a hash join. Note the predicate information. At ID 2 (full tablescan of T5) Oracle is restricting the rows to only those with T5.C1<=TRUNC(SYSDATE+500) - note that my SQL statement did not need to request this restriction. At ID 3 (full tablescan of T6) Oracle is restricting the rows to only those with T6.C1<=TRUNC(SYSDATE+500) - this restriction was requested by the SQL statement. The step at ID 2 is performed first, followed by the step at ID 3, and then the step at ID 1. The WHERE conditions were applied before the tables were joined.

Now, what if we tell Oracle to use the primary key index on table T5?

SELECT
  INDEX_NAME
FROM
  DBA_INDEXES
WHERE
  TABLE_NAME='T5'; SYS_C0036467 Repeating the SQL statement, but using an index hint: SELECT /*+ GATHER_PLAN_STATISTICS INDEX(T5 SYS_C0036467) */

  T5.C1,
  T5.C2,
  T6.C2

FROM
  T5,
  T6
WHERE
  T5.C1=T6.C1
  AND T6.C1<=TRUNC(SYSDATE+500);

SELECT
*
FROM
TABLE (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST'));

|*  1 |  HASH JOIN		     |		    |	   1 |	  500 |    500 |00:00:00.01
|	   41 |  1155K|  1155K| 1234K (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID| T5	    |	   1 |	  500 |    500 |
00:00:00.01 |	    5 |       |       | 	 |
|*  3 |    INDEX RANGE SCAN	     | SYS_C0036467 |	   1 |	  500 |
500 |00:00:00.01 |	    3 |       |       | 	 |
|*  4 |   TABLE ACCESS FULL	     | T6	    |	   1 |	  500 |    500 |
00:00:00.01 |	   36 |       |       | 	 |
----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("T5"."C1"="T6"."C1")
   3 - access("T5"."C1"<=TRUNC(SYSDATE@!+500))
   4 - filter("T6"."C1"<=TRUNC(SYSDATE@!+500))

dynamic sampling used for this statement

In the above plan, note ID 3. Oracle is first performing an index range scan on the index SYS_C0036467 for those rows where T5.C1<=TRUNC(SYSDATE+500) - remember that the SQL statement did not specifically request this restriction, and at ID 2 the matching table rows are retrieved. At ID 1 a hash join is used to join the resulting rows from the two tables together. The WHERE conditions were applied before the tables were joined.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Jul 05 2007 - 06:36:50 CDT