使用TUPLES在SQL IN子句中放置1000多个条目

问题描述

这篇文章是关于在Oracle的IN子句中放置1000多个条目.

This post is about putting more than 1000 entries in an IN clause in Oracle.

通常，当您在IN子句中放置1000个以上的条目时，Oracle会引发错误，因为Oracle不会在IN子句中处理此类条目.

Generally, when you put more than 1000 entries in the IN clause, Oracle will throw an error, as Oracle does not handle such number of entries in the IN clause.

如何解决此问题?

推荐答案

为什么不只是

SELECT ..
FROM my_table
WHERE (
     my_column IN ( 'val1', 'val2', 'val3', ... , 'val1000' )
  OR my_column IN ( 'val1001', 'val1002', 'val1003', ... , 'val2000' )
  OR ... etc
      )
AND other_conditions...

?

至少在12c中有效.

CREATE TABLE matt_tuple_test2 ( a number, b varchar2(2000) );

insert into matt_tuple_test2 (a, b) select rownum, lpad('.',2000,'.') from dual connect by rownum <= 300000;

create index matt_tuple_test_n1 on matt_tuple_test2 (a);


SELECT a, b
FROM   matt_tuple_test2
WHERE  ( a in ( 3,
6,
9,
...
2997,
3000 )
OR a IN (
3003,
3006,
3009,
...
5997,
6000)
OR a IN (
6003,
6006,
6009,
...
8994,
8997,
9000)
)
and b like '.%'
;

说明计划:

Plan                Query Block Name    Object Alias
4 SELECT STATEMENT  ALL_ROWS
Cost: 1,073  Bytes: 1,612,835  Cardinality: 1,589  CPU Cost: 0  IO Cost: 0  Time: 00:00:00
Partition #: 0
    3 INLIST ITERATOR
    Projection: "A"[NUMBER,22], "B"[VARCHAR2,2000]
    Cost: 0  Bytes: 0  Cardinality: 0  CPU Cost: 0  IO Cost: 0
    Partition #: 0              SEL$1
        2 TABLE ACCESS BY INDEX ROWID BATCHED APPS.MATT_TUPLE_TEST2
        Filter: "B" LIKE '.%'
        Projection: "A"[NUMBER,22], "B"[VARCHAR2,2000]
        Cost: 1,073  Bytes: 1,612,835  Cardinality: 1,589  CPU Cost: 0  IO Cost: 0  Time: 00:00:00
        Partition #: 0          SEL$1
            1 INDEX RANGE SCAN APPS.MATT_TUPLE_TEST_N1 [Analyzed]
            Access: "A"=3 OR "A"=6 OR "A"=9 OR "A"=12 OR "A"=15 OR "A"=18 OR "A"=21 OR "A"=24 OR "A"=27 OR "A"=30 OR "A"=33 OR "A"=36 OR "A"=39 OR "A"=42 OR "A"=45 OR "A"=48 OR "A"=51 OR "A"=54 OR "A"=57 OR "A"=60 OR "A"=63 OR "A"=66 OR "A"=69 OR "A"=72 OR "A"=75 OR "A"=78 OR "A"=81 OR "A"=84 OR "A"=87 OR "A"=90 OR "A"=93 OR "A"=96 OR "A"=99 OR "A"=102 OR "A"=105 OR "A"=108 OR "A"=111 OR "A"=114 OR "A"=117 OR "A"=120 OR "A"=123 OR "A"=126 OR "A"=129 OR "A"=132 OR "A"=135 OR "A"=138 OR "A"=141 OR "A"=144 OR "A"=147 OR "A"=150 OR "A"=153 OR "A"=156 OR "A"=159 OR "A"=162 OR "A"=165 OR "A"=168 OR "A"=171 OR "A"=174 OR "A"=177 OR "A"=180 OR "A"=183 OR "A"=186 OR "A"=189 OR "A"=192 OR "A"=195 OR "A"=198 OR "A"=201 OR "A"=204 OR "A"=207 OR "A"=210 OR "A"=213 OR "A"=216 OR "A"=219 OR "A"=222 OR "A"=225 OR "A"=228 OR "A"=231 OR "A"=234 OR "A"=237 OR "A"=240 OR "A"=243 OR "A"=246 OR "A"=249 OR "A"=252 OR "A"=255 OR "A"=258 OR "A"=261 OR "A"=264 OR "A"=267 OR "A"=270 OR "A"=273 OR "A"=276 OR "A"=279 OR "A"=282 OR "A"=285 OR "A"=288 OR "A"=291 OR "A"=294 OR "A"=297 OR "A"=300 OR "A"=303 OR "A"=306 OR "A"=309 OR "A"=312 OR "A"=315 OR "A"=318 OR "A"=321 OR "A"=324 OR "A"=327 OR "A"=330 OR "A"=333 OR "A"=336 OR "A"=339 OR "A"=342 OR "A"=345 OR "A"=348 OR "A"=351 OR "A"=354 OR "A"=357 OR "A"=360 OR "A"=363 OR "A"=366 OR "A"=369 OR "A"=372 OR "A"=375 OR "A"=378 OR "A"=381 OR "A"=384 OR "A"=387 OR "A"=390 OR "A"=393 OR "A"=396 OR "A"=399 OR "A"=402 OR "A"=405 OR "A"=408 OR "A"=411 OR "A"=414 OR "A"=417 OR "A"=420 OR "A"=423 OR "A"=426 OR "A"=429 OR "A"=432 OR "A"=435 OR "A"=438 OR "A"=441 OR "A"=444 OR "A"=447 OR "A"=450 OR "A"=453 OR "A"=456 OR "A"=459 OR "A"=462 OR "A"=465 OR "A"=468 OR "A"=471 OR "A"=474 OR "A"=477 OR "A"=480 OR "A"=483 OR "A"=486 OR "A"=489 OR "A"=492 OR "A"=495 OR "A"=498 OR "A"=501 OR "A"=504 OR "A"=507 OR "A"=510 OR "A"=513 OR "A"=516 OR "A"=519 OR "A"=522 OR "A"=525 OR "A"=528 OR "A"=531 OR "A"=534 OR "A"=537 OR "A"=540 OR "A"=543 OR "A"=546 OR "A"=549 OR "A"=552 OR "A"=555 OR "A"=558 OR "A"=561 OR "A"=564 OR "A"=567 OR "A"=570 OR "A"=573 OR "A"=576 OR "A"=579 OR "A"=582 OR "A"=585 OR "A"=588 OR "A"=591 OR "A"=594 OR "A"=597 OR "A"=600 OR "A"=603 OR "A"=606 OR "A"=609 OR "A"=612 OR "A"=615 OR "A"=618 OR "A"=621 OR "A"=624 OR "A"=627 OR "A"=630 OR "A"=633 OR "A"=636 OR "A"=639 OR "A"=642 OR "A"=645 OR "A"=648 OR "A"=651 OR "A"=654 OR "A"=657 OR "A"=660 OR "A"=663 OR "A"=666 OR "A"=669 OR "A"=672 OR "A"=675 OR "A"=678 OR "A"=681 OR "A"=684 OR "A"=687 OR "A"=690 OR "A"=693 OR "A"=696 OR "A"=699 OR "A"=702 OR "A"=705 OR "A"=708 OR "A"=711 OR "A"=714 OR "A"=717 OR "A"=720 OR "A"=723 OR "A"=726 OR "A"=729 OR "A"=732 OR "A"=735 OR "A"=738 OR "A"=741 OR "A"=744 OR "A"=747 OR "A"=750 OR "A"=753 OR "A"=756 OR "A"=759 OR "A"=762 OR "A"=765 OR "A"=768 OR "A"=771 OR "A"=774 OR "A"=777 OR "A"=780 OR "A"=783 OR "A"=786 OR "A"=789 OR "A"=792 OR "A"=795 OR "A"=798 OR "A"=801 OR "A"=804 OR "A"=807 OR "A"=810 OR "A"=813 OR "A"=816 OR "A"=819 OR "A"=822 OR "A"=825 OR "A"=828 OR "A"=831 OR "A"=834 OR "A"=837 OR "A"=840 OR "A"=843 OR "A"=846 OR "A"=849 OR "A"=852 OR "A"=855 OR "A"=858 OR "A"=861 OR "A"=864 OR "A"=867 OR "A"=870 OR "A"=873 OR "A"=876 OR "A"=879 OR "A"=882 OR "A"=885 OR "A"=888 OR "A"=891 OR "A"=894 OR "A"=897 OR "A"=900 OR "A"=903 OR "A"=906 OR "A"=909 OR "A"=912 OR "A"=915 OR "A"=918 OR "A"=921 OR "A"=924 OR "A"=927 OR "A"=930 OR "A"=933 OR "A"=936 OR "A"=939 OR "A"=942 OR "A"=945 OR "A"=948 OR "A"=951 OR "A"=954 OR "A"=957 OR "A"=960 OR "A"=963 OR "A"=966 OR "A"=969 OR "A"=972 OR "A"=975 OR "A"=978 OR "A"=981 OR "A"=984 OR "A"=987 OR "A"=990 OR "A"=993 OR "A"=996 OR "A"=999 OR "A"=1002 OR "A"=1005 OR "A"=1008 OR "A"=1011 OR "A"=1014 OR "A"=1017 OR "A"=1020 OR "A"=1023 OR "A"=1026 OR "A"=1029 OR "A"=1032 OR "A"=1035 OR "A"=1038 OR "A"=1041 OR "A"=1044 OR "A"=1047 OR "A"=1050 OR "A"=1053 OR "A"=1056 OR "A"=1059 OR "A"=1062 OR "A"=1065 OR "A"=1068 OR "A"=1071 OR "A"=1074 OR "A"=1077 OR "A"=1080 OR "A"=1083 OR "A"=1086 OR "A"=1089 OR "A"=1092 OR
            Projection: "MATT_TUPLE_TEST2".ROWID[ROWID,10], "A"[NUMBER,22]
            Cost: 673  Bytes: 0  Cardinality: 858  CPU Cost: 0  IO Cost: 0  Time: 00:00:00
            Partition #: 0      SEL$1

这篇关于使用TUPLES在SQL IN子句中放置1000多个条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！