RE: How does the FILTER operation work?

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 ?

We do have a number of parameters set mostly required by peoplesoft.

  *._gby_hash_aggregation_enabled=false
  *._ignore_desc_in_index=TRUE
  *._optimizer_adaptive_plans=FALSE
  *._optimizer_ads_use_result_cache=FALSE
  *._unnest_subquery=false

Thanks for your insights.

--
http://www.freelists.org/webpage/oracle-l