Oracle IN 절에 1000 개 이상의 값을 입력하는 방법

Oracle IN 절에 1000 개 이상의 값을 입력하는 방법 [중복]

---

이 질문에 이미 답변이 있습니다.

• SQL IN 조항 1000 항목 제한 4 답변

정적 IN 절에서 Oracle 10g의 1000 개 항목 제한을 피할 수있는 방법이 있습니까? IN 절에서 사용하려는 많은 ID의 쉼표로 구분 된 목록이 있습니다. 1000 개 항목을 초과 할 수있는 경우 Oracle에서 오류가 발생합니다. 쿼리는 다음과 유사합니다.

select * from table1 where ID in (1,2,3,4,...,1001,1002,...)

---

임시 테이블에 값을 넣은 다음 id in (temptable에서 id 선택)을 선택하십시오.

---

OR을 사용하여 값을 여러 IN으로 나눌 수 있다고 확신합니다.

select * from table1 where ID in (1,2,3,4,...,1000) or 
ID in (1001,1002,...,2000)

---

다음 양식을 사용합니다.

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)

---

select column_X, ... from my_table
where ('magic', column_X ) in (
        ('magic', 1),
        ('magic', 2),
        ('magic', 3),
        ('magic', 4),
             ...
        ('magic', 99999)
    ) ...

---

처음에 ID 목록은 어디에서 얻었습니까? 데이터베이스의 ID 이전 쿼리에서 것입니까?

그 이유는 다음과 같습니다.

1. 참조 테이블이 사용하는 올바른 방법은 새 테이블을 추가하고 해당 테이블에 속성을 추가 한 다음 조인하는 것입니다.
2. ID 목록은 데이터베이스에서 추출 된 다음 다시 SQL 문에서 사용됩니다 (아마 나중에 또는 다른 서버 또는 기타 서버에서). 이 경우 대답은 데이터베이스에서 추출하지 않는 것입니다. 임시 테이블에 저장하거나 하나의 쿼리 만 작성하십시오.

이 SQL 문이 작동하도록하는 것 보다이 코드를 재 작업하는 것보다 좋은 방법이있을 수 있다고 생각합니다. 더 자세한 정보를 제공하면 몇 가지 아이디어를 얻을 수 있습니다.

---

... from table (... 사용 :

create or replace type numbertype
as object
(nr number(20,10) )
/ 

create or replace type number_table
as table of numbertype
/ 

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select *
    from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs 
    where id = tbnrs.nr; 
end; 
/ 

이것은 힌트가 필요한 드문 경우 중 하나입니다. 확장 된 Oracle은 열 id에 적용을 사용하지 않습니다. 이 접근 방식의 장점 중 하나는 Oracle이 쿼리를 반복해서 분석 할 필요가있는 것입니다. 임시 테이블을 사용하는 대부분의 느립니다.

편집 1 절차 단순화 (jimmyor 덕분에) + 예제

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
  open p_ref_result for
    select /*+ cardinality(tab 10) */ emp.*
    from  employees emp
    ,     table(p_numbers) tab
    where tab.nr = id;
end;
/

예 :

set serveroutput on 

create table employees ( id number(10),name varchar2(100));
insert into employees values (3,'Raymond');
insert into employees values (4,'Hans');
commit;

declare
  l_number number_table := number_table();
  l_sys_refcursor sys_refcursor;
  l_employee employees%rowtype;
begin
  l_number.extend;
  l_number(1) := numbertype(3);
  l_number.extend;
  l_number(2) := numbertype(4);
  tableselect(l_number, l_sys_refcursor);
  loop
    fetch l_sys_refcursor into l_employee;
    exit when l_sys_refcursor%notfound;
    dbms_output.put_line(l_employee.name);
  end loop;
  close l_sys_refcursor;
end;
/

그러면 다음이됩니다.

Raymond
Hans

---

나는 해결을 찾기 위해 여기까지왔다.

쿼리해야하는 최고급 항목 수에 따라 항목이 고유 한 가정하면 쿼리를 1000 개 항목의 일괄 결합으로 분할하고 대신 결과를 할 수 있습니다 (여기에서는 의사 코드).

//remove dupes
items = items.RemoveDuplicates();

//how to break the items into 1000 item batches        
batches = new batch list;
batch = new batch;
for (int i = 0; i < items.Count; i++)
{
    if (batch.Count == 1000)
    {
        batches.Add(batch);
        batch.Clear()
    }
    batch.Add(items[i]);
    if (i == items.Count - 1)
    {
        //add the final batch (it has < 1000 items).
        batches.Add(batch); 
    }
}

// now go query the db for each batch
results = new results;
foreach(batch in batches)
{
    results.Add(query(batch));
}

이것은 일반적으로 1000 개의 개가 여러 항목이없는 시나리오에서 좋은 절충안이 될 수 있습니다. 1000 개가 많은 항목이 있으면 "고급"에지 케이스 시나리오가 될 수 있기 때문입니다. 예를 들어 1500 개의 항목이있는 경우 (1000, 500)의 두 쿼리는 그렇게 나쁘지. 이것은 또한 각 쿼리가 자체로 특별히 비싸지 않다고 가정합니다.

이것은 하지 않을 100000 범위, 말하자면 - - 100 개 쿼리를 필요로 예상되는 항목의 전형적인 수는 훨씬 클 수있어 경우에 해당합니다. 그렇다면 위에 제공된 전역 임시 테이블 솔루션을 가장 "올바른"솔루션으로 사용하는 것을 더 심각하게 살펴 봐야합니다. 또한 항목이 고유하지 않은 경우 배치에서도 중복 결과를 해결해야합니다.

---

예, 오라클에게는 매우 이상한 상황입니다.

IN 절에 2000 개의 ID를 지정하면 실패합니다. 이것은 실패합니다 :

select ... 
where id in (1,2,....2000) 

그러나 단순히 다른 테이블 (예 : 임시 테이블)에 2000 개의 ID를 넣으면 작동합니다.

select ... 
where id in (select userId 
             from temptable_with_2000_ids ) 

여러분이 할 수있는 일은 실제로 레코드를 1000 개의 레코드로 분할하여 그룹별로 실행할 수 있습니다.

---

IN절 을 사용하는 대신 JOINID를 가져 오는 다른 테이블과 함께 사용해 볼 수 있습니다 . 그렇게하면 한계에 대해 걱정할 필요가 없습니다. 내 쪽의 생각.

---

다음은 인라인보기를 만든 다음 선택하여 제한을 해결하려는 Perl 코드입니다. 문 텍스트는 DUAL에서 각 항목을 개별적으로 선택하는 대신 12 개 항목의 행을 사용하여 압축 한 다음 모든 열을 합쳐서 압축을 해제합니다. 압축 해제의 UNION 또는 UNION ALL은 모두 IN 내부로 들어가서 어쨌든 결합하기 전에 고유성을 부과하므로 여기서 차이가 없어야하지만 압축에서 UNION ALL은 많은 불필요한 비교를 방지하기 위해 사용됩니다. 내가 필터링하는 데이터는 모두 정수이므로 인용은 문제가되지 않습니다.

#
# generate the innards of an IN expression with more than a thousand items
#
use English '-no_match_vars';
sub big_IN_list{
    @_ < 13 and return join ', ',@_;
    my $padding_required = (12 - (@_ % 12)) % 12;  
    # get first dozen and make length of @_ an even multiple of 12
    my ($a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l) = splice @_,0,12, ( ('NULL') x $padding_required );

    my @dozens; 
    local $LIST_SEPARATOR = ', '; # how to join elements within each dozen
    while(@_){
        push @dozens, "SELECT @{[ splice @_,0,12 ]} FROM DUAL"
    };  
    $LIST_SEPARATOR = "\n    union all\n    "; # how to join @dozens 
    return <<"EXP";
WITH t AS (
    select $a A, $b B, $c C, $d D, $e E, $f F, $g G, $h H, $i I, $j J, $k K, $l L FROM     DUAL
    union all
    @dozens
 )
select A from t union select B from t union select C from t union
select D from t union select E from t union select F from t union
select G from t union select H from t union select I from t union 
select J from t union select K from t union select L from t
EXP
}

다음과 같이 사용합니다.

my $bases_list_expr = big_IN_list(list_your_bases());
$dbh->do(<<"UPDATE");
    update bases_table set belong_to = 'us'
    where whose_base in ($bases_list_expr)
UPDATE

---

대신에 SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

이것을 사용하십시오 :

SELECT * FROM table1 WHERE ID IN (SELECT rownum AS ID FROM dual connect BY level <= 1000);

* 종속성 인 경우 ID가 다른 외부 ID를 참조하지 않는지 확인해야합니다. 기존 ID 만 사용할 수 있도록하려면 다음을 수행하십시오.

SELECT * FROM table1 WHERE ID IN (SELECT distinct(ID) FROM tablewhereidsareavailable);

건배

참고 URL : https://stackoverflow.com/questions/400255/how-to-put-more-than-1000-values-into-an-oracle-in-clause