I think I assumed because the two tablescans were indented the same amount,
they were two result sets that a filter operation was run on (Hence my
confusion). Running the query with gather_plan_stats set, and looking at starts
I can see the access on temp has 21211 starts, so it is run for each row as
Jonathan says.
The index on photo.emplid is unique, so that is how the optimizer knows it
doesn't need to do a hash distinct. The lob storage does enable storage in row
chunk.
Question to OP - I am a little puzzled that your plan shows a FILTER
subquery rather than an anti-join. Maybe it's related to data sizes, but
do you have any optimizer parameters that are set to non-standard values ?
