Oracle PL/SQL 集合性能优化详解

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

---

1. 概述

PL/SQL 集合性能优化技巧[1]：

详细见：Oracle PL/SQL 集合详解、Oracle BULK COLLECT 与 FORALL 详解。

---

2. 选择集合类型

2.1 类型对比

类型	索引	持久	性能
Associative Array	数字/字符串	否	内存最佳
Nested Table	数字	是	中
VARRAY	数字	是	固定大小

2.2 选择

- 内存临时：Associative Array
- 持久存储：Nested Table
- 固定大小：VARRAY
- 查找：Associative Array

---

3. BULK COLLECT

3.1 批量查询

-- 慢
FOR rec IN (SELECT * FROM employees) LOOP
  ...
END LOOP;

-- 快
DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM employees;
END;
/

3.2 LIMIT

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_tab IS TABLE OF c%ROWTYPE;
  v_emp emp_tab;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emp LIMIT 1000;
    EXIT WHEN v_emp.COUNT = 0;
    
    FOR i IN 1..v_emp.COUNT LOOP
      ...
    END LOOP;
  END LOOP;
  CLOSE c;
END;
/

3.3 LIMIT 选择

- 小：100-1000
- 中：1000-10000
- 大：10000+
- 测试最佳

详细见：Oracle BULK COLLECT 与 FORALL 详解。

---

4. FORALL

4.1 批量 DML

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = salary * 1.1 WHERE id = v_ids(i);
END;
/

4.2 SAVE EXCEPTIONS

FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
  INSERT INTO t VALUES (v_ids(i));

EXCEPTION
  WHEN OTHERS THEN
    FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      log_error(...);
    END LOOP;

4.3 INDICES OF

-- 稀疏集合
FORALL i IN INDICES OF v_sparse
  INSERT INTO t VALUES (v_sparse(i));

---

5. 内存管理

5.1 LIMIT

-- 避免 OOM
FETCH c BULK COLLECT INTO v_emp LIMIT 1000;

5.2 TRIM

-- 释放
v_emp.TRIM(v_emp.COUNT);

5.3 DELETE

v_emp.DELETE;

5.4 监控

SELECT name, value FROM v$pgastat WHERE name LIKE '%PGA%';

---

6. 索引查找

6.1 Associative Array

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_cache emp_tab;
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * BULK COLLECT INTO v_cache FROM employees;
  -- 注意：BULK COLLECT 到 INDEX BY 表不直接支持
  -- 需循环
  FOR rec IN (SELECT * FROM employees) LOOP
    v_cache(rec.id) := rec;
  END LOOP;
  
  -- O(1) 查找
  IF v_cache.EXISTS(100) THEN
    v_emp := v_cache(100);
  END IF;
END;
/

6.2 字符串索引

DECLARE
  TYPE name_emp IS TABLE OF employees%ROWTYPE INDEX BY VARCHAR2(100);
  v_cache name_emp;
  v_emp employees%ROWTYPE;
BEGIN
  FOR rec IN (SELECT * FROM employees) LOOP
    v_cache(rec.email) := rec;
  END LOOP;
  
  -- 按 email 查找
  IF v_cache.EXISTS('[email protected]') THEN
    v_emp := v_cache('[email protected]');
  END IF;
END;
/

---

7. 集合操作

7.1 MULTISET

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_a num_tab := num_tab(1, 2, 3, 4, 5);
  v_b num_tab := num_tab(3, 4, 5, 6, 7);
  v_c num_tab;
BEGIN
  v_c := v_a MULTISET UNION v_b;
  v_c := v_a MULTISET UNION DISTINCT v_b;
  v_c := v_a MULTISET INTERSECT v_b;
  v_c := v_a MULTISET EXCEPT v_b;
END;
/

7.2 SUBMULTISET

IF v_a SUBMULTISET v_b THEN ...
IF v_a IS A SET THEN ...  -- 无重复
IF v_a IS EMPTY THEN ...

---

8. 表达式

8.1 集合表达式

DECLARE
  TYPE num_tab IS TABLE OF NUMBER;
  v_nums num_tab := num_tab(1, 2, 3, 4, 5);
BEGIN
  -- SQL 中使用
  SELECT COLUMN_VALUE BULK COLLECT INTO v_nums FROM TABLE(v_nums);
  
  -- 表连接
  SELECT e.name
  FROM employees e, TABLE(v_nums) n
  WHERE e.id = n.COLUMN_VALUE;
END;
/

---

9. 性能对比

9.1 循环 DML

-- 慢
FOR i IN 1..v_ids.COUNT LOOP
  UPDATE t SET ... WHERE id = v_ids(i);
END LOOP;

9.2 FORALL

-- 快
FORALL i IN 1..v_ids.COUNT
  UPDATE t SET ... WHERE id = v_ids(i);

9.3 SQL

-- 最快
UPDATE t SET ... WHERE id IN (SELECT COLUMN_VALUE FROM TABLE(v_ids));

---

10. 缓存

10.1 包级缓存

CREATE OR REPLACE PACKAGE emp_cache AS
  TYPE emp_tab IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
  v_cache emp_tab;
  
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY emp_cache AS
  FUNCTION get_emp(p_id NUMBER) RETURN employees%ROWTYPE IS
  BEGIN
    IF NOT v_cache.EXISTS(p_id) THEN
      SELECT * INTO v_cache(p_id) FROM employees WHERE id = p_id;
    END IF;
    RETURN v_cache(p_id);
  END;
END;
/

10.2 RESULT CACHE

CREATE OR REPLACE FUNCTION get_dept(p_id NUMBER) RETURN VARCHAR2
  RESULT_CACHE RELIES_ON (departments)
IS
  v_name VARCHAR2(100);
BEGIN
  SELECT name INTO v_name FROM departments WHERE id = p_id;
  RETURN v_name;
END;
/

详细见：Oracle PL/SQL 性能优化详解。

---

11. 批量操作

11.1 批量 INSERT

DECLARE
  TYPE emp_tab IS TABLE OF employees%ROWTYPE;
  v_emp emp_tab;
BEGIN
  SELECT * BULK COLLECT INTO v_emp FROM source;
  
  FORALL i IN 1..v_emp.COUNT
    INSERT INTO target VALUES v_emp(i);
END;
/

11.2 批量 UPDATE

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE sal_tab IS TABLE OF NUMBER;
  v_ids id_tab;
  v_sals sal_tab;
BEGIN
  SELECT id, salary * 1.1 BULK COLLECT INTO v_ids, v_sals FROM employees;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);
END;
/

11.3 批量 DELETE

DECLARE
  TYPE id_tab IS TABLE OF NUMBER;
  v_ids id_tab;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE status = 'INACTIVE';
  
  FORALL i IN 1..v_ids.COUNT
    DELETE FROM employees WHERE id = v_ids(i);
END;
/

11.4 RETURNING

FORALL i IN 1..v_ids.COUNT
  DELETE FROM employees WHERE id = v_ids(i)
  RETURNING id BULK COLLECT INTO v_deleted;

---

12. 性能监控

12.1 时间

DECLARE
  v_start NUMBER;
  v_end NUMBER;
BEGIN
  v_start := DBMS_UTILITY.GET_TIME;
  -- 操作
  v_end := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('Time: ' || (v_end - v_start) || ' hsec');
END;
/

12.2 Profiler

EXEC DBMS_PROFILER.START_PROFILER('test');
-- 操作
EXEC DBMS_PROFILER.STOP_PROFILER;

详细见：Oracle PL/SQL 性能监控详解。

---

13. 应用场景

13.1 批量处理

- 大数据
- ETL
- 报表

13.2 缓存

- 频繁查询
- 字典
- 配置

13.3 临时存储

- 中间结果
- 复杂计算

---

14. 常见坑与排错

14.1 内存

- 大集合 OOM
- LIMIT
- 分批

14.2 索引

- 越界
- EXISTS 检查

14.3 性能

- 循环 DML
- BULK
- SQL

---

15. 最佳实践

1. Associative Array：内存查找
2. BULK COLLECT：批量查询
3. LIMIT：内存
4. FORALL：批量 DML
5. SAVE EXCEPTIONS：容错
6. 缓存：频繁查询
7. RESULT_CACHE：函数
8. TRIM/DELETE：释放
9. Profiler：定位
10. 测试：性能

---

16. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “Collections” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html