Precompute_subquery can be forced with sql patch or profile:
https://orasql.org/2014/08/28/precompute_subquery-hint/
Sorry, I have no time to investigate this case, but I suspect that your
hint was unused because of other transformation, so it became invalid. It's
better to show exact plans with format 'advanced', so we would have full
picture
On Thu, Apr 18, 2024, 13:37 Mark W. Farnham <mwf@xxxxxxxx> wrote:
Unfortunately:
to rely on sql profiles/patches.Unfortunately is difficult/impossible to change the code, so I need
Otherwise you might even be able to materialize the one row from the
subquery (or subqueries if they are different subqueries)
Hmm. Can't change the code, but can you inject statements into execution
stream immediately before the select?
I'm not sure what the current CBO would do if you executed the one row
(one column?) subquery (or subqueries) into the result cache immediately
before the execution of this statement. Probably it would not take that
into account in creating the plan since the result cache could be
invalidated during the parse.
Is the CBO produced estimate for the subquery(ies) already 1? If not,
whether or not it allows the optimization to also use Bloom filters by
doing the transformation under the covers that Sayan suggested, using the
cardinality hint of 1 might do useful things for the plan. Cardinality of 1
being specified at the start of the parse may do something better than a
dynamic estimate of 1 as well.
Others may know in advance whether or not this would work. I'd probably
give it a whirl.
I don't know if oracle rewrite is so bold to change the in to an equals if
it is told there is just one row, and I *think* Sayan was suggestion that
you change the text (which you've written you cannot).
Sorry for the random, unsure of a solution comments. Mostly based on "
Unfortunately is difficult/impossible to change the code, so I need to rely
on sql profiles/patches" and not thinking Oracle would do the required
rewrite for you, I wanted to keep the thread open for other ideas. I'd try
cardinality and then cardinality + precompute_query to see what happens and
tell us the results.
Good luck,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Stefan Koehler
Sent: Thursday, April 18, 2024 6:25 AM
To: xt.and.r@xxxxxxxxx
Cc: dragos-cristian.cornea@xxxxxxx; ORACLE-L
Subject: 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.
@ 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: @OracleSK
Sayan Malakshinov <xt.and.r@xxxxxxxxx> hat am 18.04.2024 12:12 CESTgeschrieben:
the actual values (instead of B.COL IN (...) use B.COL =
Hello,
You said that
If non-correlated IN subqueries returning one value are replaced with
VALUE_FROM_IN_SUBQUERY), optimizer is able to use Bloom filters for all
tables without any need of a hint.
plan (and/or CBO trace) but as you mentioned you wanted to force a bloom
In this case you can use precompute_subquery hint
On Thu, Apr 18, 2024, 10:57 Stefan Koehler <contact@xxxxxxxx> wrote:
Hello Dragos,
that's a pretty tough one without seeing the exact SQL and execution
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.
CEST geschrieben:
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: www.soocs.de (http://www.soocs.de)
Twitter: @OracleSK
Cornea D.C. <dmarc-noreply@xxxxxxxxxxxxx> hat am 18.04.2024 09:32
billions rows; however, there are no join predicates between A and B on the
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
partitioning date column (business requirement).
rows; it has a date filter and partition pruning occurs).
Execution plan starts with TABLE_A (cardinality between 1 and 500
force Bloom Filters on Bs using a px_join_filter hint.In absence of a good join condition between A and Bs, I tried to
chose to transform it into a decorrelated lateral view (VW_DCL_%) and theIt 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
Bloom filter is not pushed into it (a NO_ACCESS hint can be seen in the
outline section).
lateral view VW_LAT_% (which is also not using a BF) but that view isIf NO_DECORRELATE hint is added, then I end up with a MERGE with the
accessed of too many times.
with the actual values (instead of B.COL IN (...) use B.COL =
If non-correlated IN subqueries returning one value are replaced
VALUE_FROM_IN_SUBQUERY), optimizer is able to use Bloom filters for all
tables without any need of a hint.
to rely on sql profiles/patches.
Unfortunately is difficult/impossible to change the code, so I need
----
Question: is it possible to push a Bloom filter into a DCL view ?
Regards,
Dragos C
//www.freelists.org/webpage/oracle-l
//www.freelists.org/webpage/oracle-l