咨询Oracle中Partition wise join功能原理、用法及简单示例

你已经掌握分区基础概念的话，这个功能逻辑其实非常好懂，本质就是通过提前对齐俩表的分区规则，省掉大表join时跨分区捞数据、全局匹配的额外开销。
先给你打个最直白的比方：

你要办一场同年龄段相亲局，规则是只能和同出生年份的嘉宾配对。
普通join的玩法：把所有男嘉宾、女嘉宾全赶到大操场，挨个问出生年份，全场跑找同岁的人配对，人越多越乱，来回跑的时间比配对时间还长。
Partition wise join的玩法：提前把男嘉宾按出生年份分10个固定房间（90年房、91年房…99年房），女嘉宾也按完全一样的规则分10个对应房间，配对的时候直接锁死房间：90年男和90年女就在1号房配，91年的就在2号房配，每个房间配完直接出结果，最后把所有房间的配对名单拼起来就是最终结果，没人需要跨房间跑，效率差好几倍。

核心使用前提 #

• 两张要join的表，必须以join关联字段作为分区键，且分区规则、分区数量完全对齐：比如都是按user_id做hash分8区，或者都是按order_date做范围分区、分区边界完全一致。要是一个表按join键分区、另一个按别的字段分区，根本没法按上面说的“分房间配对”逻辑跑。
• 并行执行场景下收益最高：每个配对的分区组可以直接分给独立的并行进程处理，进程之间不需要交换数据，几乎没有锁和通信开销。

可直接跑的示例 #

拿最常见的用户表、订单表按user_id关联的场景举例，俩表都按join键user_id做对齐的hash分区：

-- 建用户画像表，按user_id做hash分区，共8个分区
CREATE TABLE user_profile (
    user_id   NUMBER,
    user_name VARCHAR2(100),
    city      VARCHAR2(50)
) PARTITION BY HASH(user_id) PARTITIONS 8;

-- 建订单表，分区键、分区数量和用户表完全一致
CREATE TABLE user_order (
    order_id     NUMBER,
    user_id      NUMBER,
    order_amount NUMBER(10,2),
    create_time  DATE
) PARTITION BY HASH(user_id) PARTITIONS 8;

正常写关联SQL就行，不需要加特殊语法，Oracle优化器识别到俩表分区对齐，会自动选择Partition wise join执行路径：

SELECT
    p.user_name,
    p.city,
    o.order_id,
    o.order_amount
FROM user_profile p
INNER JOIN user_order o
    ON p.user_id = o.user_id
WHERE o.create_time >= DATE '2024-01-01';

执行时的实际逻辑完全没有全局匹配的步骤：

• 给8个并行进程分别分配一对匹配的分区：进程1管user_profile的1号分区+user_order的1号分区，进程2管俩表的2号分区，以此类推
• 每个进程只在自己拿到的两个小分区里做user_id匹配，不需要访问其他分区的数据
• 所有进程跑完之后，直接把各自的匹配结果拼起来返回给用户，全程没有跨分区的数据传输

几个容易踩的误区 #

• 不是表做了分区就一定能用上这个功能：如果分区键不是join字段，或者俩表分区规则、分区数对不上，优化器不会走这个路径。比如俩表都按create_time做范围分区，但join键是user_id，就完全用不了。
• 不需要强制加hint触发：只要分区对齐，优化器算成本的时候自然会选这个执行计划，毕竟比普通join的IO、网络/内存开销低太多。
• 还有个简化版叫部分Partition wise join：如果只有一张表按join键分区，另一张表没分区，Oracle会在执行时动态把未分区的表按相同规则临时切分成对应分区，再做配对，性能比全量PWJ差一点，但还是比普通全局join快很多。

内容的提问来源于stack exchange，提问作者user2205174