1

we use Oracle 11g 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?

William Robertson
  • 15,273
  • 4
  • 38
  • 44
ju.
  • 304
  • 3
  • 16
  • 1
    create productionid table and then join your table with productionid table,it enable you filter your data much faster – RF1991 Mar 27 '22 at 15:30
  • @RF1991, unfortunately I can't create a table on the database. Of course this would be also my favorite approach. – ju. Mar 27 '22 at 15:30
  • then use temporary table or table variable – RF1991 Mar 27 '22 at 15:38
  • Possible duplicate of https://stackoverflow.com/q/35231757/1509264 or, maybe, https://stackoverflow.com/questions/4722220/sql-in-clause-1000-item-limit/9084247#9084247 – MT0 Mar 27 '22 at 15:42
  • 2
    A long, comma-separated list may not be very handy - with over 1000 items, it would very likely be a CLOB, and there are no CLOB literals in Oracle. To even present it to your query, you would have to split such a long single string into chunks, present them as varchar2 literals, then convert them to CLOB and concatenate them in the query, before you can even start with your real query. Where do you get your list, and in what format? A JSON array would be handy, for example. –  Mar 27 '22 at 17:08
  • @mathguy Converting to JSON or XML or any other format wouldn't be a problem. – ju. Mar 27 '22 at 17:15
  • "Converting to" is not my question. My question was, where do you **get** your list, and in what format? Not what you can "convert" it to. Can you ask the "user" of your query (whatever or whoever it is) to provide the inputs to you in JSON format already? If not, what protocol do you have in place with "them"? What is it that you could convert to JSON or XML if you wanted to? Best to start with what you get in raw form. –  Mar 27 '22 at 18:09
  • I get a list with relevant production ID's (e.g. K32532-12, I11244-2, P65454-13) from different sources, mostly long excel lists from customer complaints, controlling or whatever. In the best case I am retrieving it from another mssql database. There is not a single source for these lists. So that's why I said it wouldn't be a problem for me to bring the format of the list in whatever shape is best for the query. I am running the query by myself, currently unfortunately with `SELECT xy FROM yz WHERE productionid in ('my','long','list')`. – ju. Mar 27 '22 at 18:27
  • 1
    This will not be too popular, but. Perhaps you should go talk yo your DBA and expalin the *business* problem, perhaps ask them to create a table (or a [Global Temporary Table](https://oracle-base.com/articles/misc/temporary-tables)) for you; or another solution. Keep in mind you and the DBA have the same job, just different aspects of it, **to provide the best solution for your organization**. – Belayer Mar 27 '22 at 18:51
  • @Belayer, I totally agree with you but the understanding for such "minor user problems" is unfortunately very limited in the IT department of my company. – ju. Mar 27 '22 at 19:01
  • 1
    You work for an organisation with an IT department which has **no interest** in solving business problems in an efficient and robust fashion? Oh dear. No wonder you have long excel lists of customer complaints. – APC Mar 28 '22 at 02:25
  • It's worse than you think ;) That's the problem of big corporate's who gave up all flexibility for their IT rule set. – ju. Mar 28 '22 at 05:04

2 Answers2

1

Option 1: Use a collection:

CREATE TYPE string_list IS TABLE OF VARCHAR2(20);

Then:

SELECT productionid, tool, proddate 
FROM   proddb 
WHERE  productionid MEMBER OF string_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.ODCIVARCHAR2LIST('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)

or

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)
MT0
  • 143,790
  • 11
  • 59
  • 117
  • Thanks a lot, all options look possible for me! But I have a few questions: Option #2 is bypassing the length limitation, but does it also overcome the performance issues? Regarding the internal ODCI list, I probably have simplified my example too much, actually my prod IDs contain letters like `K315.6`, so is there an available list for his? And my last question regarding option 3 is, if I write this for e.g. 1000 production ids, is there any limitation e.g. in query length which I have to consider? – ju. Mar 27 '22 at 16:08
  • 2
    @ju. - Option 2 bypasses the 1000 item limitation, but it does still have a limit - 65,535 items. This is a very general limit on the number of `and/or` delimited conditions (an `IN` list is essentially an `or` delimited condition). It is also very unlikely to perform better than what you have. The best option is likely the last one - use a subquery. Even better, write it in a `with` clause; then the optimizer may choose to materialize it - acting as an ad-hoc temp table (**very** temporary - removed after the query is executed). –  Mar 27 '22 at 16:48
  • Thanks a lot, I will try it out tomorrow. Most likely I will never exceed the 65k limit. – ju. Mar 27 '22 at 17:00
  • 1
    @ju. There are many `ODCI` collection types. If you want a list of strings then use `SYS.ODCIVARCHAR2LIST`. Option 3 is just limited by the size of a query. – MT0 Mar 27 '22 at 18:21
  • *"my prod IDs contain letters like K315.6, so is there an available list for his?"* Yes: SYS.DBMS_DEBUG_VC2COLL. – APC Mar 28 '22 at 02:22
1

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.

Little Santi
  • 8,563
  • 2
  • 18
  • 46