we use Oracle SQL 11 and I am accessing a database from our manufacturing execution system, on which I don't have any write permissions. E.g. I can't use temporary tables. I have to retrieve the data for a long list of production IDs. The format of the production IDs is flexible, so I can also use a different format, but I would expect a comma separated list is rather handy. However, my problem is how to filter my request for a long list? Using in like in

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

is not very fast, limited to 1000 entries and I guess is not designed for this scenario. So what would be the best approach to filter a large list of hundreds or thousand production ids?

CodePudding user response：

Option 1: Use a collection:

CREATE TYPE int_list IS TABLE OF INTEGER;

Then:

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

or, using a the built-in SYS.ODCI*LIST types:

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

Option 2: Use a multi-dimensional IN list to bypass the 1000 item restriction:

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

Option 3: Use a subquery:

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 ...
       )

or:

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)

CodePudding user response：

As it's already been said, the best way would be findind a table/set of tables that would return the required IDs through simple criteria (grouping data, dates, etc).

But if you absolutely MUST do your query from a huge set of arbitrary IDs, and if consistency is not an issue for you (i.e., data won't change greatly in a moment), you could also segment the query in 4 or 5 queries with 1000 items each.