Comment mettre plus de 1000 valeurs dans un Oracle dans la clause [dupliquer]

je suis presque sûr que vous pouvez diviser les valeurs entre plusieurs INs en utilisant ou:

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

vous pouvez essayer d'utiliser le formulaire suivant:

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

Où trouve-t-on la liste des pièces d'identité? Puisque ce sont des identifiants dans votre base de données, proviennent-ils d'une requête antérieure?

quand j'ai vu cela dans le passé, c'est parce que: -

1. une table de référence est manquante et la bonne façon serait d'ajouter la nouvelle table, de mettre un attribut sur cette table et de s'y joindre
2. une liste d'id est extraite de la base de données, puis utilisé dans des déclaration SQL ultérieure (peut-être plus tard ou sur un autre serveur ou autre). Dans ce cas, la réponse est de ne jamais l'extraire de la base de données. Stocker dans une table temporaire ou tout simplement écrire une requête.

je pense qu'il pourrait y avoir de meilleures façons de retravailler ce code qui vient de faire fonctionner cette instruction SQL. Si vous donnez plus de détails, vous pourriez avoir quelques idées.

utiliser ...à partir de la 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; 
/ 

c'est l'un des rares cas où vous avez besoin d'un indice, sinon Oracle n'utilisera pas l'index sur la colonne id. L'un des avantages de cette approche est Qu'Oracle n'a pas besoin d'analyser la requête encore et encore. L'utilisation d'une table temporaire est la plupart du temps plus lente.

edition 1 simplifié la procédure (merci à jimmyorr) + exemple

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;
/

exemple:

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;
/

cette sortie:

Raymond
Hans

j'ai fini ici à la recherche d'une solution.

selon le nombre haut de gamme d'articles que vous devez interroger, et en supposant que vos articles sont uniques, vous pouvez diviser votre requête en lots de requêtes de 1000 articles, et combiner les résultats sur votre fin à la place (pseudocode ici):

//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));
}

Cela peut être un bon compromis dans le scénario où vous n'avez généralement pas plus de 1000 points - plus de 1000 articles serait votre "haut de gamme" bord de scénario. Par exemple, dans le cas où vous avez 1500 articles, deux requêtes de (1000, 500) ne serait pas si mal. Cela suppose également que chaque requête n'est pas particulièrement coûteux en soi.

ce ne serait pas approprié si votre nombre typique d'éléments attendus est beaucoup plus grand - disons, dans la gamme de 100000 - nécessitant 100 requêtes. Si oui, alors vous devriez probablement regarder plus sérieusement en utilisant le global temporaire solution de tableaux fournie ci-dessus comme la solution la plus "correcte". De plus, si vos articles ne sont pas uniques, vous devrez résoudre les résultats en double dans vos lots ainsi.

Oui, situation très étrange pour oracle.

si vous spécifiez 2000 ids à l'intérieur de la clause IN, il échouera. cela échoue:

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

mais si vous mettez simplement l'ids 2000 dans une autre table (table temp par exemple), il fonctionnera cela fonctionne:

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

ce que vous pouvez faire, pourrait en fait diviser les enregistrements en un lot de 1000 enregistrements et les exécuter groupe par groupe.

au lieu d'utiliser IN clause, pouvez-vous essayer d'utiliser JOIN avec l'autre table, qui va chercher l'id. comme ça, on n'aura pas à s'inquiéter de limit. juste une pensée de mon côté.

voici un code Perl qui essaie de contourner la limite en créant une vue inline puis en sélectionnant à partir de celle-ci. Le texte de la déclaration est comprimé en utilisant des lignes de douze éléments chacun au lieu de sélectionner chaque élément du DUAL individuellement, puis non compressé en unionant ensemble toutes les colonnes. L'UNION ou L'UNION tout en décompression ne devrait faire aucune différence ici car tout va à l'intérieur d'un dans lequel imposera l'unicité avant de se joindre contre elle de toute façon, mais dans la compression, L'UNION tout est utilisé pour éviter beaucoup de comparaisons inutiles. Comme les données que je filtre sont toutes des nombres entiers, citer n'est pas un problème.

#
# 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
}

on l'utiliserait comme ça:

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

au lieu de SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

utilisez ceci:

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

*notez que vous devez vous assurer que L'ID ne renvoie aucun autre ID étranger s'il s'agit d'une dépendance. Pour s'assurer que seuls les identificateurs existants sont disponibles, alors:

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

Cheers