Oracle join类型及其性能对比分析

在 oracle 中，连接查询（join） 的连接类型及其性能对比

一、oracle中的连接方式分类与语法

1. 内连接（inner join）

返回两个表中满足连接条件的记录。

select *
from table1 t1
join table2 t2 on t1.id = t2.id;

特点：

• 只返回匹配的行。
• 是最常用的连接。
• 如果不指定连接方式（如使用 , 和 where），默认是内连接。

2. 外连接（outer join）

a）左外连接（left outer join）

返回左表的全部记录，如果右表中没有匹配的记录，则右表列为 null。

select *
from table1 t1
left join table2 t2 on t1.id = t2.id;

b）右外连接（right outer join）

返回右表的全部记录，左表没有匹配记录的列为 null。

select *
from table1 t1
right join table2 t2 on t1.id = t2.id;

c）全外连接（full outer join）

返回两个表中的全部记录，没有匹配的部分列为 null。

select *
from table1 t1
full outer join table2 t2 on t1.id = t2.id;

3. 自连接（self join）

同一个表连接自身。

select a.name, b.name
from employee a
join employee b on a.manager_id = b.id;

4. 交叉连接（cross join）

返回两个表的笛卡尔积（每个表1条记录，两表组合1×1条记录）。

select *
from table1
cross join table2;

5. oracle 专有语法：旧式连接(+）

select *
from table1 t1, table2 t2
where t1.id = t2.id(+);
-- 等价于 left join

二、连接查询的性能效率比较

三、连接效率影响因素详解

1. 连接字段是否建索引

• 有索引：优化器可快速查找匹配项（nested loop）。
• 无索引：可能全表扫描（hash join、merge join）。

2. 表大小（数据量）

• 小表连接快，大表连接要谨慎。
• oracle 优化器会根据统计信息选择合适连接方式。

3. 连接方式选择

• inner join 优于 outer join
• outer join 多用于有缺失数据容忍时

4. oracle 执行计划（explain plan）

建议使用如下命令查看连接执行方式：

explain plan for 
select ...
from ...
where ...;

select * from table(dbms_xplan.display);

四、连接算法（oracle执行时选择）

五、性能优化建议

• 优先使用 inner join，避免 full outer join。
• 连接字段建立索引（特别是被驱动表的连接键）。
• 使用 where 限定连接数据范围。
• 避免连接过多大表（建议 ≤ 4 张表）。
• 开启并更新表的统计信息，使 oracle 优化器选择最佳执行计划。
• 避免在连接字段上使用函数或类型转换，否则索引失效。
• 使用临时表（with 或 materialized view）分步处理复杂连接逻辑

六、高效连接案例

多表连接，带索引、过滤条件

select o.order_id, c.customer_name, p.product_name
from orders o
join customers c on o.customer_id = c.customer_id
join products p on o.product_id = p.product_id
where o.order_date >= sysdate - 30;

• 三表 inner join
• 所有连接字段建立索引
• 限定最近 30 天数据，减少扫描量

七、总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持代码网。