We have a very poorly performing MERGE statement (an hour or more on
tables of ~10000 and ~100000). This may require building temporary
tables with appropriate indexes, but I thought I would ask your
collective opinion. Would building a comprehensive index on
nullid.angus_is improve this statement's performance?

merge into (select * from nullid.ANGUS_IN where bhid is null) t1
using (select ta.*, ts.regnum as sire_regnum, td.regnum as dam_regnum
         from is3.animals ta
         join is3.animals ts on ta.sire_bhid=ts.bhid
         join is3.animals td on ta.dam_bhid=ts.bhid ) t2
   on t1.tattoo=t2.tattoo
  and t1.sex=t2.sex
  and t1.birth_date=t2.birth_date
  and t1.assoc=t2.assoc
  and t1.sire_regnum=t2.sire_regnum
  and t1.dam_regnum=t2.dam_regnum
 when matched and t2.regnum is null then update
      set t1.bhid = t2.bhid

The proposed index would be:

create index nullid.an_in_tsbd
      on nullid.angus_in(tattoo, sex, birth_date,
            assoc, sire_regnum, dam_regnum)