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