태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

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

오라클 2008.12.18 15:16
아쉽게도 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 CollectionTable 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))
;


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


신고
Trackbacks 6 : Comments 2
  1. 강정식 2008.12.18 17:49 신고 Modify/Delete Reply

    Function 성능 테스트할 때 실제 IN으로 공급해서 SQL을 많이 만드는데
    도움이 많이 될것 같습니다.
    감사합니다. ^^

  2. 호야 2008.12.19 09:40 신고 Modify/Delete Reply

    실제로 1000개 이상의 값을 in에 넣어서 사용하는 경우가 있어서 Global Temporary Table을 사용했었는데 다양한 방법이 있네요.

Write a comment

티스토리 툴바