IN (...) 절에 Item을 무제한으로 넣고 싶다.

아쉽게도 Oracle에서 IN (..) 절에 사용 가능한 상수값의 개수에는 제한이 있다. 

Oracle 8i까지는 최대 256개까지만 사용가능하다. 

Oracle 9i부터는 최대 1000개까지만 사용가능하다. 

세상에... 1000개 이상의 값을 IN (...)에 사용할 일이 있는가?라고 묻고 싶겠지만, 알 수 없는 것이 세상이고 사람이다. 

아래 예제를 보자. 

drop table t1 purge;

create table t1(c1 int, c2 int)
;

insert into t1 
select level, level
from dual connect by level <= 10000
;

다음과 같이 총 1101개의 상수값을 사용하는 IN (...) 절을 만든다. 

var v_sql clob;

begin
  :v_sql := 'select count(*) from t1 where c1 in (';
  
  for idx in 1 .. 1100 loop
    :v_sql := :v_sql || idx || ', ';
  end loop;
  
  :v_sql := :v_sql || ' 1101);';
  
end;
/

spool long_in.sql

다음과 같은 SQL 문장이 완성된다.

select count(*) from t1 where c1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134,  135, 136, 137, 138, 139, 140, ..., 1101);

이 SQL 문장을 수행하면? 불행하게도 다음과 같은 에러가 발생한다.

ERROR at line 25:
ORA-01795: maximum number of expressions in a list is 1000

미친 짓 같겠지만 어떻게든 이 제약을 피하고 싶다면? 
(현실 세계에서는 이런 미친 짓이 실제로 발생하곤 한다)

내가 아는 한에서는 다음과 같은 방법들이 있다.

1. IN (...)을 살짝 비틀기

다음과 같이 무의미한 Multiple Column을 이용하면 1,000개의 제약이 사라진다. 

select count(*) from t1 where (1, c1) in ((1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12), (1, 13), (1, 14), (1, 15), (1, 16), (1, 17), (1, 18), (1, 19), (1, 20), (1, 21), (1, 22), (1, 23), (1, 24), (1, 25), (1, 26), (1, 27), (1, 28), (1, 29), (1, 30), (1, 31), (1, 32), (1, 33), (1, 34), (1, 35), (1, 36), (1, 37), (1, 38), (1, 39), (1, 40), (1, 41), (1, 42), (1, 43), (1, 44), (1, 45), (1, 46), (1, 47), (1, 48), (1, 49), (1, 50), (1, 51), (1, 52), (1, 53), (1, 54), (1, 55), (1, 56), (1, 57), (1, 58), (1, 59), (1, 60), (1, 61), (1, 62), (1, 63), (1, 64), (1, 65), (1, 66), (1, 67), (1, 68), (1, 69), (1, 70), (1, 71), (1, 72), (1, 73), (1, 74), (1, 75), (1, 76), (1, 77), (1, 78), (1, 79), (1, 80), (1, 81), (1, 82), (1, 83), (1, 84), (1, 85), (1, 86), (1, 87), (1, 88), (1, 89), (1, 90), (1, 91), (1, 92), (1, 93), (1, 94), (1, 95), (1, 96), (1, 97), (1, 98), (1, 99), (1, 100), (1, 101), (1, 102), (1, 103), (1, 104), (1, 105), (1, 106), (1, 107), (1, 108), (1, 109), (1, 110), ..., (1, 1101));

간단하면서도 놀라운 방법이라고 할 수  있다. 하지만 세련되어 보이지는 않는다. 

2. Global Temporary Table 사용
다음과 같이 Global Temporary Table에 원하는 Data를 추가한 후 IN (Subquery)를 사용하는 것이다. 

create global temporary table gtt1(c1 int);

insert into gtt1
select level
from dual
connect by level <= 1101
;

select count(*)
from t1 
where c1 in (select c1 from gtt1)
;

3. Collection + Table Function 사용
2번째 방법과 기본적으로 동일하지만 Table이 아닌 PL/SQL Collection과 Table Pseudo Function을 사용한다는 것이 다르다. 

가장 Oracle 다운 방법이라고 할 수 있다. 

create or replace type type1 as table of int;
/

create or replace function func1 
return type1
is 
  v_value type1 := type1();
begin
   for idx in 1 .. 1101 loop
     v_value.extend;
     v_value(idx) := idx;
   end loop;
   
   return v_value;
   
end;
/

    
select count(*) from t1
where c1 in (select * from table(func1))
;

4. Collection + Pipelined Function + Table Function 사용
세번째 방법의 확장판이다. 가장 세련되어 보인다. 하지만 성능 면에서는 세번째 방법에 비해 조금 불리할 수  있다.

create or replace type type1 as table of int;
/

create or replace function func1 
return type1
pipelined
is 
begin
   for idx in 1 .. 1101 loop
    pipe row(idx);
   end loop;
   
   return;
   
end;
/

select count(*) from t1
where c1 in (select * from table(func1))
;

하나의 문제에 대해 여러 가지의 해결책을 탐색하고, 상황에 따라 가장 최적의 솔루션을 찾을 수 있다는 것이 중요하다.

출처:http://ukja.tistory.com/185