Oracle中自定义集合与数据表等效表变量关联查询的实现方法

逐行遍历方案的性能问题 #

• 该方案性能极差：相当于对大表My_Table做全表扫描，逐行匹配集合内的键值，完全浪费了seq_no、task_seq字段上可能存在的索引，数据量越大性能下降越明显，完全不推荐使用。

最优实现方案 #

Oracle支持通过TABLE()函数将自定义集合类型转为SQL引擎可识别的临时行集，可直接实现和SQL Server版本完全一致的关联逻辑，性能优异。

前置条件 #

如果你当前的Picture_Rec和Picture_Arr是在PL/SQL块/包内定义的，需要先改为SQL全局级别的类型，否则SQL引擎无法识别该类型：

-- 创建SQL全局行类型
CREATE OR REPLACE TYPE Picture_Rec AS OBJECT (
     seq_no                         NUMBER,
     task_seq                       NUMBER); 
/
-- 创建SQL全局集合类型
CREATE OR REPLACE TYPE Picture_Arr IS TABLE OF Picture_Rec;
/

关联查询写法 #

SELECT M.*
FROM My_Table M
-- 将入参集合转为临时行集，12c+版本可省略CAST函数
INNER JOIN TABLE(CAST(你的入参Picture_Arr实例 AS Picture_Arr)) A
  ON M.seq_no = A.seq_no AND M.task_seq = A.task_seq;

性能说明 #

• 你的集合仅包含5-10行数据，Oracle优化器会自动选择嵌套循环（Nested Loop）作为连接方式
• 只要My_Table在(seq_no, task_seq)上建有联合索引，每次匹配都会走索引查找，总查询效率非常高，远优于逐行遍历方案

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