We have not gotten any updates from the OP, but even optimizer estimation
is good but there could still be problem due to inefficient selecting
cluster size and memory pressure. Even it is One-pass hash join there can
be lots of (“unexpected”) “direct path write temp” which related to these
I have covered them in the post:
On Thu, May 7, 2020 at 11:28 AM Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
Thanks Jonathan, I think this is really useful. I already wondered before
about missing bloom filters. :-)
Am 06.05.2020 um 19:21 schrieb Jonathan Lewis:
Until 19c a query that uses a Bloom filter will "lose" the Bloom filter
when it's changed to "insert as select".
However CTAS does work, and there is a patch, that allows insert /*+
append */ as select to use a Bloom filter.
See blog and comments:
On Wed, May 6, 2020 at 4:04 PM Lothar Flatz <l.flatz@xxxxxxxxxx> wrote:
I wonder if a Bloom Filter could be used on sce. Would that be faster
than probing? I would think so ...
There seems to be many rows not surviving the join.