Re: Nested loops with a full table scan

"tcm" <tm_chaney_at_yahoo.com> wrote in message news:1111432670.217518.98840_at_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.
>

Are your table and index statistics up to date ? Received on Mon Mar 21 2005 - 13:37:46 CST