How can I speed up the oracle query? ( where in( bla bla ))

Question

I have 2000 numbers (Uniq Primary Key). I want to get contact information of numbers. My database is Oracle.

I use IN(bla,bla) in my query. It works slow because of this.

Example My Query:

SELECT p.*,t.* 
FROM PERSONEL p 
  LEFT OUTER JOIN CODE_TITLE t ON t.Id = p.TitleId 
WHERE ID IN(1,2,....,2000)

When the query runs, it takes about 10-12 seconds.

Is there a method to use instead of IN(bla, bla)? Can you explain with an example ?

An `IN` with 2000 parameters won't run to begin with. Oracle has a limit of 1000 elements for the `IN` clause (when using constants) — , Sep 02 '20 at 08:04
Where do the values for the `IN` originate? If they're constant, build a table and use that. If they're somehow in your database, substitute a query. If they're external then try a temp table. — LoztInSpace, Sep 02 '20 at 08:09
Belayer: I randomly placed numbers from 1 to 2000. the numbers are not ordered. I need to use JOIN. — Sinan Bay, Sep 02 '20 at 08:45
a_horse_with_no_name: Okey. You say the limit is 1000 right. I should have written 1000 but I have 2000 numbers. It also works slow when I try with 1000 numbers. — Sinan Bay, Sep 02 '20 at 08:48
LoztInSpace: the numbers are a list that occurs at program runtime. the resulting number corresponds to a column on the table. Can you give an example for temp table? — Sinan Bay, Sep 02 '20 at 08:52
ID is presumably a key column on the ??CODE_TITLE?? table. How many rows in the table to which it belongs? — APC, Sep 02 '20 at 11:26

score 0 · Answer 1 · answered Sep 02 '20 at 12:13

Put your numbers (or whatever they really are) in a table. Let's call it LIST_TABLE. Then

SELECT p.*,t.* 
FROM PERSONEL p 
  LEFT OUTER JOIN CODE_TITLE t ON t.Id = p.TitleId 
WHERE ID IN(select list_id from list_table)

The type of table for LIST_TABLE (normal, GTT, external) will depend on where the values come from and your best mechanism for loading them.

score 0 · Answer 2 · answered Sep 02 '20 at 12:41

You can use xmltable('1 to 2000') as a derived table in order to generate integer set starting form 1 upto 2000, incrementing by 1 :

SELECT p.*,t.* 
  FROM PERSONEL p 
  LEFT JOIN CODE_TITLE t ON t.Id = p.TitleId                     
 WHERE Id IN ( SELECT TO_NUMBER(column_value) FROM xmltable('1 to 2000') )

P.S. indeed using WHERE ID BETWEEN 1 AND 2000 would suffice with index created on CODE_TITLE.ID in order to increase the query performance.

score 0 · Answer 3 · answered Sep 03 '20 at 06:56

create or replace type myTcType as table of number(16,0);

create or replace function in_list(p_string in varchar2) return myTcType
    as
    l_data myTcType := myTcType();
    l_string long default p_string || ',';
    l_n number;
begin
    loop
    exit when l_string is null;
    l_data.extend;
    l_n := instr(l_string, ',');
    l_data(l_data.count) := substr(l_string,1,l_n-1);
    l_string := substr(l_string,l_n+1);
    end loop;
    return l_data;
end;

select * from table(cast(in_list('1,2,3,4,5') as myTcType));

How can I speed up the oracle query? ( where in( bla bla ))

3 Answers3