Re: Join execution order

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 05 Jul 2007 05:49:52 -0700
Message-ID: <1183639792.963171.178180@n2g2000hse.googlegroups.com>

On Jul 5, 8:21 am, Lecter23 <lecte..._at_gmail.com> wrote:
> 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!

With the above SQL statement, the plan looks like this:

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

---

|* 1 | HASH JOIN | | 1 | 1233 | 1233 |00:00:00.01 | 74 | 1155K| 1155K| 1283K (0)|

|*  2 |   TABLE ACCESS FULL| T6   |	 1 |   1233 |	1233 |00:00:00.01 |
31 |	    |	    |	       |
|   3 |   TABLE ACCESS FULL| T5   |	 1 |  10000 |  10000 |00:00:00.01
|	 43 |	    |	    |	       |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("T5"."C1"="T6"."C1")
   2 - filter("T6"."C2"<1234)

• dynamic sampling used for this statement

Oracle realized that with the restriction on the T6 table, there would be fewer rows, so this time Oracle started working on table T6 first. 1,233 rows from T6 were hash joined with 10,000 rows from T5. There was a full tablescan on each table.

Now, adding the index hint:

---

| Id | Operation | Name | Starts | E-Rows | A-Rows | A- Time | Buffers | Reads | OMem | 1Mem | Used-Mem |

---

|*  1 |  HASH JOIN		     |		    |	   1 |	 1233 |   1233 |00:00:00.03
|	  103 |     24 |  1155K|  1155K| 1283K (0)|
|*  2 |   TABLE ACCESS FULL	     | T6	    |	   1 |	 1233 |   1233 |
00:00:00.01 |	   31 |      0 |       |       |	  |
|   3 |   TABLE ACCESS BY INDEX ROWID| T5	    |	   1 |	10000 |  10000 |
00:00:00.05 |	   72 |     24 |       |       |	  |
|   4 |    INDEX FULL SCAN	     | SYS_C0036467 |	   1 |	10000 |  10000
|00:00:00.02 |	   36 |     24 |       |       |	  |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

   1 - access("T5"."C1"="T6"."C1")
   2 - filter("T6"."C2"<1234)

• dynamic sampling used for this statement

Oracle first retrieves the 1,233 matching rows from T6, and then performs an index lookup on T5 to retrieve 10,000 rows - the 10,000 rows are hash joined with the 1,233 rows from T6. The execution time tripled.

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