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: @OracleSK
Cornea D.C. <dmarc-noreply@xxxxxxxxxxxxx> 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
