Re: Bloom filters and decorrelated lateral views (DCL)

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
> 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