Paul,
Thanks for the feedback.
It would be the "_unnest_subquery" = false that blocks the anti-join.
I think you'll also find that the emplid is declared not null.
I've done a little experimental work - the oddity of costing is all about the
LOBINDEX. I'll be writing a blog note about it tomorrow.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Paul Houghton <Paul.Houghton@xxxxxxxxxxxxx>
Sent: 24 May 2018 18:41:39
To: 'Oracle Mailinglist'
Subject: 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 ?
