Sam Durai wrote:
> Thanks for looking into the issue. We cannot have duplicates on
> VEH_IDENT_NBR, OPTN_CD, ERR_FLAG. I do have updated stats on the small
> table and most of the time this table would be empty so I'm not sure
> whether Unique Index would be of help.
Not on all three, but that doesn't help. Imporatnt is whether you could
have two (VEH_IDENT_NBR, OPTN_CD) with different ERR_FLAG.
because you join on (VEH_IDENT_NBR, OPTN_CD).
Anyway: try my rewrite and see what it does to the plan. It should
become much simpler. All the goo above the JOIN should go away (and
hopefully in the process remove the tablescan).


--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab