Re: Bloom filters and decorrelated lateral views (DCL)

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