Re: Nested loops with a full table scan

From: tcm <tm_chaney_at_yahoo.com>
Date: 21 Mar 2005 14:41:01 -0800
Message-ID: <1111444861.413200.145120@o13g2000cwo.googlegroups.com>

Matthias Hoys wrote:
> "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:
> >
> >

> > | 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 ?

Actually I wasn't thinking. Since the driving table only produces 1 row, it probably doesn't matter if it uses NLs or hash since both result in a single table scan on T2. Received on Mon Mar 21 2005 - 16:41:01 CST