Re: Bloom filters and decorrelated lateral views (DCL)

Precompute_subquery can be forced with sql patch or profile: https://orasql.org/2014/08/28/precompute_subquery-hint/

Sorry, I have no time to investigate this case, but I suspect that your hint was unused because of other transformation, so it became invalid. It's better to show exact plans with format 'advanced', so we would have full picture

On Thu, Apr 18, 2024, 13:37 Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Unfortunately:
>
> > > > Unfortunately is difficult/impossible to change the code, so I need
> to rely on sql profiles/patches.
>
> Otherwise you might even be able to materialize the one row from the
> subquery (or subqueries if they are different subqueries)
>
> Hmm. Can't change the code, but can you inject statements into execution
> stream immediately before the select?
>
> I'm not sure what the current CBO would do if you executed the one row
> (one column?) subquery (or subqueries) into the result cache immediately
> before the execution of this statement. Probably it would not take that
> into account in creating the plan since the result cache could be
> invalidated during the parse.
>
> Is the CBO produced estimate for the subquery(ies) already 1? If not,
> whether or not it allows the optimization to also use Bloom filters by
> doing the transformation under the covers that Sayan suggested, using the
> cardinality hint of 1 might do useful things for the plan. Cardinality of 1
> being specified at the start of the parse may do something better than a
> dynamic estimate of 1 as well.
>
> Others may know in advance whether or not this would work. I'd probably
> give it a whirl.
>
> I don't know if oracle rewrite is so bold to change the in to an equals if
> it is told there is just one row, and I *think* Sayan was suggestion that
> you change the text (which you've written you cannot).
>
> Sorry for the random, unsure of a solution comments. Mostly based on "
> Unfortunately is difficult/impossible to change the code, so I need to rely
> on sql profiles/patches" and not thinking Oracle would do the required
> rewrite for you, I wanted to keep the thread open for other ideas. I'd try
> cardinality and then cardinality + precompute_query to see what happens and
> tell us the results.
>
> Good luck,
>
> mwf
>
>
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Stefan Koehler
> Sent: Thursday, April 18, 2024 6:25 AM
> To: xt.and.r_at_gmail.com
> Cc: dragos-cristian.cornea_at_ing.com; ORACLE-L
> Subject: Re: Bloom filters and decorrelated lateral views (DCL)
>
> Hi Sayan,
> good catch :-)
>
> In deed with always only 1 returned row this would be a work-around.
>
> _at_ Dragos: Please be aware of the possible side effects described here (
> https://tanelpoder.com/2009/01/23/multipart-cursor-subexecution-and-precompute_subquery-hint/
> ) by Tanel as we don’t know the (application) context of the query.
>
> Best Regards
> Stefan Koehler
>
> Independent Oracle performance consultant and researcher
> Website: www.soocs.de
> Twitter: _at_OracleSK
>
> > Sayan Malakshinov <xt.and.r_at_gmail.com> hat am 18.04.2024 12:12 CEST
> geschrieben:
> >
> >
> > Hello,
> >
> > You said that
> >
> > >If non-correlated IN subqueries returning one value are replaced with
> the actual values (instead of B.COL IN (...) use B.COL =
> VALUE_FROM_IN_SUBQUERY), optimizer is able to use Bloom filters for all
> tables without any need of a hint.
> >
> > In this case you can use precompute_subquery hint
> >
> >
> > On Thu, Apr 18, 2024, 10:57 Stefan Koehler <contact_at_soocs.de> wrote:
> > > Hello Dragos,
> > > that's a pretty tough one without seeing the exact SQL and execution
> plan (and/or CBO trace) but as you mentioned you wanted to force a bloom
> filter for joins (which might/should also be part of JPPD for second join
> with B?), you need to be aware that JPPD and subquery decorrelation are
> inverse transformations of each other.
> > >
> > > Best Regards
> > > Stefan Koehler
> > >
> > > Independent Oracle performance consultant and researcher
> > > Website: www.soocs.de (http://www.soocs.de)
> > > Twitter: _at_OracleSK
> > >
> > > > Cornea D.C. <dmarc-noreply_at_freelists.org> hat am 18.04.2024 09:32
> CEST geschrieben:
> > > >
> > > >
> > > > Hi list,
> > > >
> > > > Database: 19.18 (on Exadata)
> > > >
> > > > We have the following query:
> > > >
> > > > SELECT
> > > > FROM
> > > > TABLE_A
> > > > INNER JOIN TABLE_B (join predicates 1 + non-correlated IN subquery
> > > > returning 1 row) LEFT OUTER JOIN TABLE_B (join predicates 2 +
> > > > non-correlated IN subquery returning 1 row) LEFT OUTER JOIN
> > > > TABLE_C (join predicates 3) LEFT OUTER JOIN TABLE_C (join
> > > > predicates 4) LEFT OUTER JOIN TABLE_C (join predicates 5) WHERE
> > > > DATE_FILTER_ON_A
> > > >
> > > > A and B are composite partitioned (range(date)/list) with tens of
> billions rows; however, there are no join predicates between A and B on the
> partitioning date column (business requirement).
> > > >
> > > > Execution plan starts with TABLE_A (cardinality between 1 and 500
> rows; it has a date filter and partition pruning occurs).
> > > > In absence of a good join condition between A and Bs, I tried to
> force Bloom Filters on Bs using a px_join_filter hint.
> > > > It works for the first B and the BF created on A is applied to B.
> > > > However, for the second join with B (the LEFT OUTER JOIN) optimizer
> chose to transform it into a decorrelated lateral view (VW_DCL_%) and the
> Bloom filter is not pushed into it (a NO_ACCESS hint can be seen in the
> outline section).
> > > > If NO_DECORRELATE hint is added, then I end up with a MERGE with the
> lateral view VW_LAT_% (which is also not using a BF) but that view is
> accessed of too many times.
> > > >
> > > > If non-correlated IN subqueries returning one value are replaced
> with the actual values (instead of B.COL IN (...) use B.COL =
> VALUE_FROM_IN_SUBQUERY), optimizer is able to use Bloom filters for all
> tables without any need of a hint.
> > > >
> > > > Unfortunately is difficult/impossible to change the code, so I need
> to rely on sql profiles/patches.
> > > >
> > > > Question: is it possible to push a Bloom filter into a DCL view ?
> > > >
> > > > Regards,
> > > > Dragos C
> > > --
> > > http://www.freelists.org/webpage/oracle-l
> > >
> > >
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
>
>

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