left outer join performance

[Michael Shavlovsky]

Hi there!
I have interesting observation on left outer join performance, cant understand it. In one case this operation works 6 hours in other case 4 seconds on same number of rows (200k)! Why?

Need to joint two table DIH and Members on memberID. Code provided below works 6 hours to join 200k rows.

create index idx1 on DIH (MemberID_t);
create index idx2 on Members (MemberID_M);
create table temp1
select a.*, b.*
from DIH a left outer join Members b
on a.MemberID_t = b.MemberID_M;

But code to merge other tables temp1 and drugcount works 4 seconds with the same number of rows.

create index idx3 on temp1(Memberid_t);
create index idx4 on temp1(YEAR_t);
create index idx5 on DRUGCOUNT(Memberid_DC);
create index idx6 on DRUGCOUNT(YEAR_dc);

create table temp2
select a.*, b.*
from temp1 a left outer join DRUGCOUNT b
on a.Memberid_t = b.Memberid_DC
and a.YEAR_t = b.YEAR_dc;

What is the reason of such difference? and how to make first "join" work faster?
Thanks!