请教一个SQL优化思路

jihuyao

Other conditions in where clause including id in each join?  If yes, partition plus local index’

jihuyao

Instead of hash join, nested loop join using unique local index with partitions should have performed most major front work before the query.  In real process only narrowed partition is searched using local index and return true or false.

newkid

jihuyao 发表于 2022-8-30 02:38
Instead of hash join, nested loop join using unique local index with partitions should have performe ...

看不懂你在说什么，但是楼主没有具体说明，你根本无法预测计划是会变成什么样子，驱动表是哪个也不知道，而且看起来A表也没分区。所以目前看来还是我在8楼的建议最靠谱。

jihuyao

Basically hash/hash composite partition on A and hash partition on B and C and create local index on B and C.  Use A as leading table and perform parallel as needed.  To avoid table structure change try if index partition can be used for the same purpose.

newkid

jihuyao 发表于 2022-9-10 03:15
Basically hash/hash composite partition on A and hash partition on B and C and create local index on ...

如果A表用了子分区，那么一个 b_id值或者c_id 值就可能出现在多个 segment 里面了。这样就和 B表 C表上的分区无法一一对应了。这样还能用上 partition wise 连接吗?