Nested loops with a full table scan

From: tcm <tm_chaney_at_yahoo.com>
Date: 21 Mar 2005 11:17:50 -0800
Message-ID: <1111432670.217518.98840@f14g2000cwb.googlegroups.com>

I just need a little clarification - I'm used to thinking that nested loops go hand in hand with index access, and hash joins with full table scans. In the event that the optimizer chooses a plan using full table scan in conjunction with nested loops, I would generally presume the table being entered is small, and/or no indexes exist and hopefully a quick scan of the table is not too painful. And I'm assuming that a nested loops using a full table scan would scan the table for every single iteration of the loop - is this true? I made 2 tables - one small and one large. T1 has 10 rows and T2 has 1 million rows. I have a unique key on the T1 pk and a non-unique index on the T2 join column. I also put a multi-bucketed histogram on the T2 join column so the skew is obvious to Oracle. In a very simple query limiting to one record on the T1 pk that is very popular in the distribution of data in T2, Oracle chooses this plan:

---

| Id | Operation | Name | Rows | Bytes
| Cost |

---

| 0 | SELECT STATEMENT | | 1 | 30
| 1889 |
| 1 | SORT GROUP BY | | 1 | 30
| 1889 |
| 2 | NESTED LOOPS | | 983K|
28M| 540 |
| 3 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 14
| 1 |

|*  4 |     INDEX UNIQUE SCAN         | SYS_C00343772  |     1 |


|       |

|*  5 |    TABLE ACCESS FULL          | T2             |   983K|
15M|   539 |
--------------------------------------------------------------------------------

Why isn't it using a hash join when it's going after 983K out of 1 million rows in the large table? Hash_join is enabled, Index_caching is 0 and optimizer_ind_cost_adj is 100...am I missing something? Is this plan not as bad as I think?
Thanks. Received on Mon Mar 21 2005 - 13:17:50 CST