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

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

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

SQL IN 1000 조항 항목 제한 (5개의 답변)

닫힘8년 전.

정적 IN 절에서 Oracle 10g의 1000개 항목 제한을 피할 수 있는 방법이 있습니까?IN 절에서 사용할 많은 ID 목록이 쉼표로 구분되어 있습니다. 경우에 따라 이 목록이 1000개를 초과할 수 있으며, 이때 Oracle에서 오류가 발생합니다.쿼리는 이와 유사합니다...

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

값을 임시 테이블에 넣은 후 id 위치를 선택합니다(유혹에서 id 선택).

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

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,...)

애초에 ID 목록은 어디서 얻습니까?데이터베이스에 있는 ID이므로 이전 쿼리에서 가져온 ID입니까?

내가 과거에 이것을 본 이유는 다음과 같다.

1. 참조 테이블이 없습니다.정확한 방법은 새 테이블을 추가하고 해당 테이블에 속성을 추가한 후 테이블에 결합하는 것입니다.
2. ID 목록이 데이터베이스에서 추출된 후 후속 SQL 문(아마 나중에 또는 다른 서버에서)에서 사용됩니다.이 경우 정답은 데이터베이스에서 추출하지 않는 것입니다.임시 테이블에 저장하거나 쿼리를 하나만 작성합니다.

이 SQL 문을 작동시키는 것만으로 이 코드를 수정할 수 있는 더 좋은 방법이 있을 것 같습니다.자세한 내용을 알려주시면 아이디어를 얻을 수 있을 것입니다.

테이블에서 ...을 사용합니다(...).:

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이 쿼리를 몇 번이고 하드 해석할 필요가 없다는 것입니다.임시 테이블을 사용하는 것은 대부분의 경우 속도가 느립니다.

edit 1은 절차를 간소화(jimorr 덕분에) + 예

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)의 2개의 쿼리가 나쁘지 않습니다.또한 각 쿼리는 그 자체로 특별히 비용이 많이 들지 않는다고 가정합니다.

일반적인 예상 항목 수(예: 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개의 레코드로 분할하여 그룹별로 실행할 수 있습니다.

다음은 인라인 뷰를 만들고 그 중에서 선택하여 제한을 회피하려는 Perl 코드입니다.문장 텍스트는 DUAL에서 각 항목을 개별적으로 선택하는 대신 각각 12개의 항목으로 이루어진 행을 사용하여 압축된 다음 모든 열을 하나로 통합하여 압축 해제됩니다.압축 해제 시 UNION 또는 UNION ALL은 IN 내부에 모두 들어가므로 여기서 차이가 없습니다. 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 id in ($bases_list_expr)
UPDATE

사용하는 대신INclause를 사용해 볼 수 있습니까?JOIN다른 테이블, 즉 ID를 가져오고 있습니다. 그렇게 하면 한계에 대해 걱정할 필요가 없습니다.그냥 내 생각일 뿐이야

대신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