如何在Oracle SQL中实现GBQ式预取查询以替代低效CTE

背景 #

用户提供了一段BigQuery中用于提升查询效率的预取数组语句：

DECLARE practices ARRAY<INT64>;

SET practices = (SELECT ARRAY(select distinct id from business));

select *
from business 
where business.id in UNNEST(practices)

需求是在Oracle SQL中实现相同逻辑的预取操作，以此重构现有查询，替代低效的CTE。用户此前尝试的方案无法在Oracle SQL环境中直接生效，或需要拆分到PL/SQL执行（如下无效示例）：

DECLARE practices = 'My Practice Name';

select *
from business 
where business.name = '&practices'

可行实现方案 #

方案1：纯SQL WITH子句+集合（推荐，无需PL/SQL） #

Oracle支持在WITH子句中结合内置集合类型，实现类似BigQuery的预取逻辑，全程在SQL环境执行，适合替换低效CTE：

WITH practices AS (
    -- 预取distinct id并转为内置数字集合类型
    SELECT CAST(COLLECT(DISTINCT id) AS SYS.ODCINUMBERLIST) AS ids 
    FROM business
)
SELECT b.*
FROM business b
-- 展开集合并匹配id
WHERE b.id IN (SELECT column_value FROM TABLE((SELECT ids FROM practices)));

也可以用JOIN写法，逻辑更直观：

WITH practices AS (
    SELECT CAST(COLLECT(DISTINCT id) AS SYS.ODCINUMBERLIST) AS ids 
    FROM business
)
SELECT b.*
FROM business b
JOIN (SELECT column_value AS id FROM TABLE((SELECT ids FROM practices))) p
ON b.id = p.id;

方案2：绑定变量+PL/SQL赋值（适合脚本/客户端执行） #

如果使用SQL*Plus或支持绑定变量的客户端，可以预先将预取结果存入绑定变量，避免重复扫描表：

-- 声明绑定变量
VAR practices REFCURSOR;

-- 预取distinct id到绑定变量
BEGIN
    OPEN :practices FOR SELECT DISTINCT id FROM business;
END;
/

-- 使用绑定变量执行查询
SELECT b.*
FROM business b
WHERE b.id IN (SELECT id FROM :practices);

方案3：PL/SQL块内完整实现（适合程序调用） #

如果允许在PL/SQL环境执行，可直接声明集合变量，完全对齐BigQuery的逻辑：

DECLARE
    -- 定义匹配id类型的集合
    TYPE id_array IS TABLE OF NUMBER;
    practices id_array;
BEGIN
    -- 批量预取distinct id到集合
    SELECT DISTINCT id BULK COLLECT INTO practices FROM business;
    
    -- 执行查询并处理结果（示例用DBMS_OUTPUT输出，可按需替换为返回结果集）
    FOR rec IN (SELECT * FROM business WHERE id MEMBER OF practices) LOOP
        DBMS_OUTPUT.PUT_LINE('ID: ' || rec.id || ' | 名称: ' || rec.name);
    END LOOP;
END;
/

关键说明 #

• SYS.ODCINUMBERLIST是Oracle内置的数字集合类型，无需自定义即可使用，简化代码；
• 预取逻辑的核心是先一次性获取所有distinct id，避免后续查询重复扫描表，和BigQuery的思路一致；
• 若处理超大数据量，BULK COLLECT（方案3）或COLLECT配合分区扫描能进一步提升性能。

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