Re: How does the FILTER operation work?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Thu, 24 May 2018 18:45:55 +0000
Message-ID: <LO1P123MB0977D66697BE11E5FE9F6F5FA56A0_at_LO1P123MB0977.GBRP123.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Paul Houghton <Paul.Houghton_at_uis.cam.ac.uk> 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 ?

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


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

Received on Thu May 24 2018 - 20:45:55 CEST