Oracle SQL:匹配long，逗号分隔列表

Question

我们使用Oracle 11g，并且我正在从我们的制造执行系统访问一个数据库，在这个系统上我没有任何写权限。我不能用临时桌子。我必须检索生产ID的长列表的数据。生产ID的格式是灵活的，所以我也可以使用不同的格式，但我认为逗号分隔的列表非常方便。然而，我的问题是如何过滤我的要求一个长长的列表？使用in类

SELECT 
    productionid, 
    tool, 
    proddate 
FROM 
    proddb 
WHERE 
    productionid in ('123','231','312', ...)

不是很快，限制在1000个条目，我想不是为这个场景设计的。那么，筛选数百或1000个生产ids的大列表的最佳方法是什么？

Answer 1

选项1:使用集合：

CREATE TYPE string_list IS TABLE OF VARCHAR2(20);

然后：

SELECT productionid, tool, proddate 
FROM   proddb 
WHERE  productionid MEMBER OF string_list('123','231','312',...);

或者，使用内置的SYS.ODCI*LIST类型：

SELECT productionid, tool, proddate 
FROM   proddb 
WHERE  productionid IN (SELECT column_value
                        FROM   TABLE(SYS.ODCIVARCHAR2LIST('123','231','312',...)));

选项2:使用多维IN列表绕过1000项限制：

SELECT productionid, tool, proddate 
FROM   proddb 
WHERE  (productionid, 1) IN ((123,1),(231,1),(321,1),...);

选项3:使用子查询：

SELECT productionid, tool, proddate 
FROM   proddb 
WHERE  productionid IN (
         SELECT 123 FROM DUAL UNION ALL
         SELECT 213 FROM DUAL UNION ALL
         SELECT 321 FROM DUAL -- UNION ALL ...
       )

或者：

SELECT productionid, tool, proddate 
FROM   proddb p
       INNER JOIN (
         SELECT 123 AS id FROM DUAL UNION ALL
         SELECT 213 FROM DUAL UNION ALL
         SELECT 321 FROM DUAL -- UNION ALL ...
       ) i
       ON (p.productionid = i.id)

或

WITH filters AS (
  SELECT 123 AS id FROM DUAL UNION ALL
  SELECT 213 FROM DUAL UNION ALL
  SELECT 321 FROM DUAL -- UNION ALL ...
)
SELECT productionid, tool, proddate 
FROM   proddb p
       INNER JOIN filters f
       ON (p.productionid = f.id)

Answer 2

正如前面已经说过的，最好的方法是通过简单的标准(分组数据、日期等)找到一个表/一组表，返回所需的it。

但是，如果您绝对必须使用一组庞大的任意in进行查询，而且如果一致性对您来说不是问题(即数据在一瞬间不会发生很大的变化)，您还可以将查询分割为4或5个查询，每个查询有1000个条目。