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

在 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

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

连接类型	行数大小关系	匹配情况	性能	优化建议
inner join	常见，性能最好	有匹配	高效	使用索引字段连接
left join	左大右小	不一定匹配	中等	尽量加过滤条件
right join	左小右大	不一定匹配	中等	同上，尽量避免
full outer join	大数据慎用	不一定匹配	低效	避免在大数据量上使用
cross join	小表可用，谨慎使用	无条件连接	最低	通常不推荐
self 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执行时选择）

算法	特点	适用情况
nested loop join	一条一条查找	小表驱动大表，有索引时最佳
hash join	建立哈希表进行匹配	大表连接，大量数据无索引时
merge join	对两个表排序再合并	连接列已排序或排序开销可接受

五、性能优化建议

优先使用 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;