Hash join order

From: <pratap.deshmukh_at_gmail.com>
Date: Wed, 25 Jun 2008 14:13:08 -0700 (PDT)
Message-ID: <eba3c474-df59-43bc-ae13-99f3ec004ce4@m44g2000hsc.googlegroups.com>

Hi,

We are using Oracle 10.2.0.3.0. I have a simple query like this -

select *
from large_table, small_table
where small_table.col = large_table.col (+)

large_table has 140 million rows. Small table can have any number of rows.I am posting the execution plan for different scenarios where small table has different number of records.

Case 1 - Small table has 180K records and large table has 140 million records. The execution plan shows that small table is accessed first followed by large_table and the large_table probes the hash table of small table

---

| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | |

180K|   122M|       |   272K  (3)| 01:03:42 |       |       |
|*  1 |  HASH JOIN OUTER        |                            |
180K|   122M|   122M|   272K  (3)| 01:03:42 |       |       |


|   2 |   PARTITION RANGE SINGLE|                            |

180K|   120M|       |  3079   (4)| 00:00:44 |     3 |     3 |


|   3 |    PARTITION LIST ALL   |                            |

180K|   120M|       |  3079   (4)| 00:00:44 |     1 |    25 |
|*  4 |     TABLE ACCESS FULL   | SMALL_TABLE                |
180K|   120M|       |  3079   (4)| 00:00:44 |    51 |    75 |


|   5 |   INDEX FAST FULL SCAN  | LARGE_TABLE_INDEX          |

140M|  2007M|       |   105K  (3)| 00:24:40 |       |       |
------------------------------------------------------------------------------------------------------------------------------

Case 2 - Large table has 140 million records and small table has 12 million records. But this time the join order is reversed

---

| Id | Operation | Name | Rows |
Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |

---

| 0 | SELECT STATEMENT | |

12M|  8234M|       |   815K  (2)| 03:10:13 |       |       |
|*  1 |  HASH JOIN RIGHT OUTER  |                            |
12M|  8234M|  3614M|   815K  (2)| 03:10:13 |       |       |


|   2 |   INDEX FAST FULL SCAN  | LARGE_TABLE_INDEX          |

140M|  2007M|       |   105K  (3)| 00:24:40 |       |       |


|   3 |   PARTITION RANGE SINGLE|                            |

12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |     1 |


|   4 |    PARTITION LIST ALL   |                            |

12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |    25 |
|*  5 |     TABLE ACCESS FULL   | SMALL_TABLE                |
12M|  8062M|       |   200K  (4)| 00:46:50 |     1 |    25 |
------------------------------------------------------------------------------------------------------------------------------

Question is - Why is Oracle reversing the join order in Case 2 when ideally it should be probing the large table into the hash of the small table?

Regards,
Pratap Received on Wed Jun 25 2008 - 16:13:08 CDT