ejemplos - pl sql select in

Usando TUPLES para poner más de 1000 entradas en la cláusula SQL IN (5)

El límite no se aplica a la cantidad de valores devueltos por una subconsulta SELECT. Entonces sería mucho más simple hacerlo así:

select * from table_name where column_name in ( select value_1 from dual union all select value_2 from dual union all select value_3 from dual union all ..... )

Es de suponer que eso no tiene que hacerse a mano; los valores ya están en una tabla (en cuyo caso pueden seleccionarse directamente de esa tabla, sin necesidad de "dual" y "union all"), o son el resultado de una subconsulta más compleja, o se producen en el código de la aplicación - donde un bucle simple puede crear la cadena necesaria para la subconsulta.

Esta pregunta ya tiene una respuesta aquí:

SQL IN Clause 1000 item limit 4 respuestas

Esta publicación trata de poner más de 1000 entradas en una cláusula IN en Oracle.

Generalmente, cuando coloca más de 1000 entradas en la cláusula IN, Oracle arrojará un error, ya que Oracle no maneja ese número de entradas en la cláusula IN.

¿Cómo resolver este problema?

Si desea poner más de 1000 valores codificados de forma separada por comas, use el concepto llamado "Tuplas".

Una sintaxis simple del uso de tuplas es la siguiente:

SELECT * FROM TABLE_NAME WHERE (1, COLUMN_NAME) IN ((1, VALUE_1), (1, VALUE_2), ... ... ... ... (1, VALUE_1000), (1, VALUE_1001));

Este enfoque ayudará a enmarcar una consulta SQL con más de 1000 entradas en la cláusula IN.

Espero que esto ayude. Por favor, agregue a este hilo de que hay otro enfoque para este tipo de escenario; eso sería útil.

Gracias
Mariscal

¿Por qué no solo

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

Eso funciona en 12c al menos.

Respuesta a los comentarios sobre el rendimiento

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 ''.%'' ;

Explicar el plan:

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

Use sub selección en la cláusula IN.

también puede hacer una combinación como esta (solo para Oracle):

WITH T(A, B) AS ( SELECT 1 AS A, VALUE_1 AS B UNION ALL SELECT 1 AS A, VALUE_2 AS B UNION ALL -- ... SELECT 1 AS A, VALUE_1000 AS B UNION ALL SELECT 1 AS A, VALUE_1001 AS B ) SELECT * FROM TABLE_NAME JOIN T ON 1 = T.A AND COLUMN_NAME = T.B

para todo lo demás:

WITH T(A, B) AS ( VALUES (1, VALUE_1), (1, VALUE_2), --... (1, VALUE_1000), (1, VALUE_1001) ) SELECT * FROM TABLE_NAME JOIN T ON 1 = T.A AND COLUMN_NAME = T.B

Tenga en cuenta que, en este caso, no tiene que tener los valores en la consulta SQL, también pueden estar en una tabla a la que se une ... para este ejemplo, se muestra como un CTE.

Si los valores no funcionan con CTE en Oracle, también podría hacerlo así

SELECT * FROM TABLE_NAME JOIN ( VALUES (1, VALUE_1), (1, VALUE_2), --... (1, VALUE_1000), (1, VALUE_1001) ) AS T(A,B) ON 1 = T.A AND COLUMN_NAME = T.B