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
-----------------------------------------------------------------------------------------------
ATTENTION:
The information in this electronic mail message is private and
confidential, and only intended for the addressee.
The information in this e-mail does not constitute
Investment Advice as defined in Art 4 of the Markets
in Financial Instruments Directive 2004/39/EC. The Markets
in Financial Instruments Directive 2004/39/EC is not applicable to
Life insurance and Private pensions products.
Should you receive this message by mistake, you are hereby
notified that any disclosure, reproduction, distribution or use of this
message is strictly prohibited. Please inform the sender by
reply transmission and delete the message without copying or
opening it.
Messages and attachments are scanned for all viruses known.
If this message contains password-protected attachments, the
files have NOT been scanned for viruses by the ING mail domain.
Always scan attachments before opening them.
-----------------------------------------------------------------------------------------------
