viernes, 14 de septiembre de 2007

BULK COLLECT con miles de registros y la cláusula LIMIT

Veo muchísimas veces, que cuando se utiliza BULK COLLECT en los códigos PL/SQL, no se coloca la cláusula LIMIT. Cuando no utilizamos ésta cláusula, lo único que ganamos es arruinar la memoria del proceso.
La cláusula LIMIT nos permite definir la cantidad de 'datos' que vamos a colocar en memoria. Cuando utilizamos LIMIT, lo ideal es definirlo en un valor entre 100 a 500. Personalmente, elijo el valor 100 porque en base a mi experiencia suele ser el mejor valor. Pero porque elijo un valor tan chico y no 1000 o 5000 por ejemplo? Bueno, por la simple razón que manejar gran cantidad de datos en memoria es más costoso que manejar poca cantidad.
Si elegimos un valor alto en el LIMIT, puede darse 3 casos:
- Que nuestro código se ejecute más rápidamente (improbable).
- Que nuestro código se ejecute en igual tiempo (improbable).
- Que nuestro código se ejecute más lentamente (probable).

Veamos un ejemplo para entender mejor las consecuencia de no utilizar la cláusula LIMIT.

Primero creamos una tabla TEST con 10.000 registros (para ver la diferencia del uso de la cláusula LIMIT, no hace falta realizar el ejemplo con millones de registros. Con unos miles de registros nos alcanza para entender el tema) y una tabla TEST_2 con la estructura de la tabla TEST pero sin registros:

SQL_9iR2> CREATE TABLE test AS
2 SELECT level id , 'oracle_'||level texto
3 FROM dual
4 CONNECT BY level <= 10000 ;

Table created.

SQL_9iR2> CREATE TABLE test_2 AS
2 SELECT *
3 FROM test
4 WHERE 1 = 2 ;

Table created.

Antes de ejecutar el primer código, voy a liberar la memoria que ya no se está utilizando en mi sesión actual para poder ver claramente la diferencia de las estadísticas tomadas en cada ejecución.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ejecutamos un código PL/SQL con Bulk Collect pero SIN la cláusula LIMIT:

SQL_9iR2> DECLARE
2 TYPE t_array_number IS TABLE OF NUMBER ;
3 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
4 t_array_id t_array_number ;
5 t_array_texto t_array_varchar2 ;
6 CURSOR cur IS
7 SELECT * FROM test ;
8 BEGIN
9 OPEN cur ;
10 LOOP
11
12 FETCH cur BULK COLLECT INTO t_array_id , t_array_texto ;
13
14 FORALL i IN 1 .. t_array_id.COUNT
15 INSERT INTO test_2
16 VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18 EXIT WHEN cur%NOTFOUND ;
19
20 END LOOP ;
21 COMMIT ;
22 CLOSE cur ;
23 END ;
24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Antes de ejecutar el segundo código, voy a liberar nuevamente la memoria que ya no se está utilizando en mi sesión actual.

SQL_9iR2> exec dbms_session.FREE_UNUSED_USER_MEMORY ;

PL/SQL procedure successfully completed.

Ahora ejecutamos el mismo código PL/SQL con Bulk Collect pero CON la cláusula LIMIT:

SQL_9iR2> DECLARE
2 TYPE t_array_number IS TABLE OF NUMBER ;
3 TYPE t_array_varchar2 IS TABLE OF VARCHAR2(50) ;
4 t_array_id t_array_number ;
5 t_array_texto t_array_varchar2 ;
6 CURSOR cur IS
7 SELECT * FROM test ;
8 BEGIN
9 OPEN cur ;
10 LOOP
11
12 FETCH cur BULK COLLECT INTO t_array_id , t_array_texto LIMIT 100 ;
13
14 FORALL i IN 1 .. t_array_id.COUNT
15 INSERT INTO test_2
16 VALUES (t_array_id(i) , t_array_texto(i)) ;
17
18 EXIT WHEN cur%NOTFOUND ;
19
20 END LOOP ;
21 COMMIT ;
22 CLOSE cur ;
23 END ;
24 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

Podemos ver que en la segunda ejecución, estoy cargando en memoria 100 registros en cada Fetch que realizo. A diferencia de la primer ejecución, que carga todos los datos de una vez.

Veamos las estadísticas obtenidas de las 2 ejecuciones anteriores:

Nombre Ejecución_1 Ejecución_2 Diferencia
------------------------------ ----------- ----------- -----------
LATCH.kwqit: protect wakeup ti 1 0 -1
LATCH.simulator lru latch 1 0 -1
LATCH.spilled msgs queues list 1 0 -1
LATCH.transaction allocation 3 0 -3
LATCH.session timer 5 0 -5
LATCH.multiblock read objects 8 0 -8
LATCH.channel operations paren 11 0 -11
LATCH.child cursor hash table 20 8 -12
LATCH.Consistent RBA 56 6 -50
LATCH.lgwr LWN SCN 56 6 -50
LATCH.mostly latch-free SCN 56 6 -50
LATCH.active checkpoint queue 63 7 -56
LATCH.session idle bit 183 45 -138
LATCH.enqueues 219 49 -170
LATCH.redo writing 241 25 -216
LATCH.SQL memory manager worka 337 0 -337
LATCH.messages 427 42 -385
LATCH.simulator hash latch 844 4 -840
LATCH.dml lock allocation 1,428 154 -1,274
LATCH.shared pool 1,586 271 -1,315
LATCH.row cache enqueue latch 1,648 194 -1,454
LATCH.cache buffers lru chain 1,521 5 -1,516
LATCH.library cache pin alloca 2,900 362 -2,538
LATCH.row cache objects 4,438 502 -3,936
LATCH.enqueue hash chains 4,841 508 -4,333
LATCH.checkpoint queue latch 6,296 521 -5,775
LATCH.session allocation 19,333 1,893 -17,440
LATCH.undo global data 30,208 2,945 -27,263
LATCH.redo allocation 30,716 3,231 -27,485
LATCH.sequence cache 42,506 4,124 -38,382
LATCH.library cache pin 60,580 6,241 -54,339
LATCH.library cache 76,555 7,911 -68,644

LATCHES:

Ejecución_1 Ejecución_2 Diferencia Porcentaje
670,187 67,521 -602,666 992.56%


Una de las cosas que me interesa mostrarles acerca de éstas ejecuciones son los LATCHES (loqueos). Si bien la ejecución de los 2 códigos PL/SQL demoraron exactamente lo mismo en ejecutarse (en éste ejemplo procesando solamente 10.000 registros), las estadísticas nos muestran que estamos empleados muchísimos más loqueos en la primer ejecución que en la segunda. Pero porqué sucede ésto? Recordemos que en la segunda ejecución, lo único que modificamos en el código fue el agregado de la cláusula LIMIT. Bien, como dijimos en el comienzo, manejar gran cantidad de memoria es más costoso que manejar poca cantidad, por lo que Oracle tiene que emplear mayor cantidad de loqueos para manejar los 10.000 registros que subimos a memoria en la primer ejecución, que manejar solamente 100 registros en la segunda ejecución.
Este ejemplo lo realizamos con un sólo usuario concurrente.... pero imagínese qué sucedería si tenemos muchos usuarios concurrentes realizando lo mismo que nosotros... y por lo tanto, generando gran cantidad de loqueos....

NOTA: Tenemos que evitar los loqueos a toda costa ya que los loqueos afectan la performance del sistema. Mientras mayor sea la cantidad de loqueos, nuestro sistema se vuelve cada vez menos escalable; y como consecuencia, cada vez soporta menor cantidad de usuarios concurrentes.

22 comentarios:

Anónimo dijo...

Me parece muy interesante tu articulo, pero tengos mis apreciaciones, al colocar LIMIT o no, estamos de acuerdo pero recuerda que no siempre es valido por lo siguiente : al realizar una carga Masiva sobre los 10.000.000 de registros yo debo contar con el LMT para poder liberar la memoria , sino la incluyo este termina con error porque nocuenta con el espacio suficiente, hemos probado con 40.000.000 de carga incorporando el LMT y te dire que funcionando muy bien. en 2:00 estamos cargando....
favor tus comentarios... Graicas
Claudio (Chile)
cmacayto@hotmail.com

Leonardo Horikian dijo...

Claudio, no entiendo del todo tu post. Cuando procesamos gran cantidad de datos en un procedimiento, debemos utilizar Bulk Collect con la cláusula LIMIT para no perjudicar la performance del sistema. Claro está, que si no colocamos el LIMIT, vamos a estar afectando la memoria.

Saludos.

Camilo Aguilar dijo...

Excelente artículo.

Soy muy nuevo en PL/SQL, actualmente me encuentro realizando un procedimiento para copiar información entre tablas y me gustaría tener este mismo procedimiento que expones pero con la tabla origen y destino como parámetros así como el LIMIT, lo más eficiente posible. Agradecería inmensamente si me puede dar algunas indicaciones al respecto.

Unknown dijo...

Leonardo, tengo una pregunta para el uso de FORALL - INSERT, cuando realizo esta operación, agarrando las execpciones con: sql%bulk_exceptions, tengo la iteracción con: sql%bulk_exceptions(i).error_index y el error con: sql%bulk_exceptions(i).error_code, pero para el caso de tener un error cualquiera, como conozco los valores a insertar que provocaron la excepción?
para mi caso particular, estoy insertando 1.000.000 de registros (leídos de una tabla X con LIMIT 100 en el bulk), como se que registro específicamente provocó la excepción en el forall-insert?, ya que el número de la iteración no me ayuda en conocer los campos del registro con problemas.
Gracias

Leonardo Horikian dijo...

Hola,
Supongamos que tenes una colección llamada TEST_COL del tipo NUMBER en donde guardamos los ID de uan determinada tabla.
Si se produjeron errores en el FORALL-INSERT, podrías recuperar los valores muy fácilmente.

Por ejemplo, en el EXCEPTION tendrías algo como lo siguiente:

EXCEPTION
WHEN bulk_errores THEN
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
dbms_output.put_line
( 'Error en ID: '||test_col(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX)
||' --> '||SQL%BULK_EXCEPTIONS(i).ERROR_CODE
||' - '||SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE) );
END LOOP;

Veamos cada una de las líneas que estoy colocando allí:
1) Con test_col(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX) obtengo el ID del índice de la colección que produjo el error.
2) Con SQL%BULK_EXCEPTIONS(i).ERROR_CODE obtengo el código de error que se produjo.
3) Con SQLERRM(-1 * SQL%BULK_EXCEPTIONS(i).ERROR_CODE) obtengo el mensaje de error que se produjo.

La ejecución de mi ejemplo mostraría ésto por pantalla...

Error en ID: 20 --> 1438 - ORA-01438: value larger than specified precision allows for this column
Error en ID: 21 --> 1438 - ORA-01438: value larger than specified precision allows for this column
Error en ID: 22 --> 1438 - ORA-01438: value larger than specified precision allows for this column
Error en ID: 23 --> 1438 - ORA-01438: value larger than specified precision allows for this column
Error en ID: 24 --> 1438 - ORA-01438: value larger than specified precision allows for this column
Error en ID: 25 --> 1438 - ORA-01438: value larger than specified precision allows for this column

Saludos.

Anónimo dijo...

como obtienes las estadisticas de
2 ejecuciones anteriores,
donde te muestra los LATCH

Luis

Leonardo Horikian dijo...

Hola Luis, para ver esa información, una de las herramientas que uso es RUNSTATS (http://asktom.oracle.com/tkyte/runstats.html).

Saludos

CaЯloS dijo...

Hola q tal me sirvio mucho el analisis que posteaste acerca del LIMIT eso ya lo tengo claro, yo actualmente estoy diseniando un proceso similar pero para grandes cantidades de regitros aproximandamente 5,000,000 por tabla y las tablas de destino tienen de 150 a mas campos. Mi SP me falla en compilacion me dice que el tipo no es soportado en el FECTH para ese INTO yo basicamente hago lo mismo declarar un type como una tabla luego una variable del type que defini, luego un cursor donde obtengo el monton de registros pero me falla en ese punto que te comento en este momento no tengo el codigo pero lo voy a postear mas tarde y te agradeceria tu ayuda,

Saludos, gracias.

Leonardo Horikian dijo...

Hola CaЯloS,

Te muestro un ejemplo que quizas te ayude a solucionar el problema que tienes...


test@test10gr2> CREATE TABLE test AS
2 SELECT level id , 'oracle_'||level texto
3 FROM dual
4 CONNECT BY level <= 10000 ;

Tabla creada.

test@test10gr2> CREATE TYPE test_obj AS OBJECT
2 (
3 id NUMBER,
4 texto VARCHAR2(4000)
5 );
6 /

Tipo creado.

test@test10gr2> CREATE TYPE test_tab IS TABLE OF test_obj;
2 /

Tipo creado.

test@test10gr2> DECLARE
2 t_test_array test_tab := test_tab();
3 CURSOR cur IS
4 SELECT * FROM test ;
5 BEGIN
6 OPEN cur ;
7 LOOP
8 FETCH cur BULK COLLECT INTO t_test_array LIMIT 100;
9 EXIT WHEN cur%NOTFOUND ;
10 END LOOP ;
11 CLOSE cur ;
12 END ;
13 /
FETCH cur BULK COLLECT INTO t_test_array LIMIT 100;
*
ERROR en lÝnea 8:
ORA-06550: lÝnea 8, columna 31:
PLS-00386: los tipos no coinciden en 'T_TEST_ARRAY' entre cursor FETCH y variables INTO
ORA-06550: lÝnea 8, columna 3:
PL/SQL: SQL Statement ignored

test@test10gr2> DECLARE
2 t_test_array test_tab := test_tab();
3 CURSOR cur IS
4 SELECT test_obj(id, texto) FROM test ;
5 BEGIN
6 OPEN cur ;
7 LOOP
8 FETCH cur BULK COLLECT INTO t_test_array LIMIT 100;
9 EXIT WHEN cur%NOTFOUND ;
10 END LOOP ;
11 CLOSE cur ;
12 END ;
13 /

Procedimiento PL/SQL terminado correctamente.

CaЯloS dijo...

Gracias por la ayuda !!! Pero te comento tuve varios problemas uno de ellos fue que la base de datos que utilizo como fuente es una SQL Server en windows, por lo que me vi obligado a configurar los Heterogeneous services para Oracle, ya que mi base de datos esta en un Server Linux como debe de ser !!! jeje pero bueno te comento que el query que utilizo para llenar el cursor me esta echando a perder todo me traba el server por lo que tengo que reiniciarlo. No se que estoy haciendo mal, por lo que te quiero enseniar mi codigo:


CREATE OR REPLACE PROCEDURE LOAD_OFSA_TABLES
IS
TYPE TABLE_ARRAY IS TABLE OF FEM_CHECKING%ROWTYPE INDEX BY PLS_INTEGER;
V_DATA TABLE_ARRAY;
V_DATE VARCHAR2(20);
N_LIMIT NUMBER := 100;
ERRORES NUMBER;
ERR NUMBER;
DMLERROR_MSG EXCEPTION;
SQLERROR_MSG VARCHAR2(120):= NULL;
ERROR_COUNTER NUMBER := 0;
PRAGMA EXCEPTION_INIT(DMLERROR_MSG, -24381);
CURSOR C_TBL_SELECT IS SELECT * FROM OFSA_FEM_CHECKING@MSHS;
BEGIN
----CREATING TABLES TO SAVE THE BAD RECORDS
EXECUTE IMMEDIATE 'DROP TABLE TARGET_ERR';
EXECUTE IMMEDIATE 'CREATE TABLE TARGET_ERR AS (SELECT * FROM FEM_CHECKING WHERE ROWNUM = 0)';
EXECUTE IMMEDIATE 'COMMIT';
OPEN C_TBL_SELECT;
LOOP
FETCH C_TBL_SELECT BULK COLLECT INTO V_DATA LIMIT N_LIMIT;
BEGIN
FORALL i IN V_DATA.FIRST..V_DATA.LAST
SAVE EXCEPTIONS
INSERT INTO FEM_CHECKING VALUES V_DATA(i);
EXECUTE IMMEDIATE 'COMMIT';
EXCEPTION
WHEN DMLERROR_MSG THEN
ERRORES := SQL%BULK_EXCEPTIONS.COUNT;
ERROR_COUNTER := ERROR_COUNTER + ERRORES;
FOR i IN 1..ERRORES LOOP
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE ('The following errors were found: '||SQL%BULK_EXCEPTIONS(i).ERROR_INDEX||':'||SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
ERR:= SQL%BULK_EXCEPTIONS(I).ERROR_INDEX;
INSERT INTO TARGET_ERR VALUES V_DATA(ERR);
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE(SQL%BULK_EXCEPTIONS(i).ERROR_INDEX || ', ' ||SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
WHEN OTHERS THEN
SQLERROR_MSG := SQLERRM;
DBMS_OUTPUT.PUT_LINE ('The following errors were found '||CHR(13)||SQLERROR_MSG);
DBMS_OUTPUT.PUT_LINE('');
END;
EXIT WHEN C_TBL_SELECT%NOTFOUND;
END LOOP;
COMMIT;
CLOSE C_TBL_SELECT;
V_DATE := TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS');
DBMS_OUTPUT.PUT_LINE ('Load process complete at: '||CHR(9)||V_DATE);
DBMS_OUTPUT.PUT_LINE ('Total errors'||ERROR_COUNTER);
END LOAD_OFSA_TABLES;
SPOOL OFF;


La tabla fuente se llama OFSA_FEM_CHECKING esta tiene 174 campos al igual que la de destino, por lo que probe solo seleccionando 25 de ellos y funciono bien se tarda como 0.0123 segundos, pero ya con mas como te cuento traba el server, te agradeceria mucho tu ayuda.

Saludos y gracias por todo

Leonardo Horikian dijo...

Hola CaЯloS,

El problema de performance seguramente es causado por la consulta...

SELECT *
FROM ofsa_fem_checking@mshs;

ya que esta consulta se ejecuta a traves de un DBLINK seguramente estan teniendo un problema alli.

Intenta hacer lo siguiente:

1) Ejecuta el código en SQL*Plus (con el volumen de datos que estás necesitando), pero antes de ejecutarlo, ejecuta la siguiente instrucción para generar el archivo de trace...

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

2) Ejecuta la herramienta TKPROF en el sistema operativo para conventir el archivo de trace generado a otro archivo más legible.

3) Enviame el contenido del archivo legible para ver con claridad la causa del problema.

4) Si la ofsa_fem_checking@mshs es una vista, enviame la consulta que se encuentra en esa vista junto con su respectivo plan de ejecución.

Saludos

Roberto PZ dijo...

Hola, excelente artículo. Yo tengo unas inquietuds relacionada con el uso de Bulk Collect en sentencias Select y Associative Arrays.

Por ejemplo:

create or replace package pack_sample
is
type ArrayNumber is Table of Number index by pls_integer;

procedure sel_data(arreglo out ArrayNumber);

end;

create or replace package
body pack_sample is

procedure sel_data(arreglo out ArrayNumber)is
begin
select employee_id bulk collect into arreglo
from employees;

end;

¿Puedo usar la sentencia LIMIT obteniendo el mismo beneficio que indicas en este artículo?

¿Recuperar datos mediante un RefCursor en lugar de Associative Arrays mejora el rendimiento?

Gracias por su ayuda.

Anónimo dijo...

hola,soy novato y me ha servido mucho el ejemplo. Ahora yo quisiera saber como definir mas facil cuando una tabla tiene muchos campos o el insert es resultado de un join con muchos campos, desde ya muchas gracias

Leonardo Horikian dijo...

Hola,

Cuando una tabla tiene muchos campos y queremos definir el tipo de la colección de manera más fácil, podemos utilizar el siguiente código (este código es el utilizado como ejemplo en el post pero con algunas partes modificadas)...


SQL_11gR1> CREATE TABLE test AS
2 SELECT level id , 'oracle_'||level texto
3 FROM dual
4 CONNECT BY level <= 10000 ;

Table created.

SQL_11gR1> CREATE TABLE test_2 AS
2 SELECT *
3 FROM test
4 WHERE 1 = 2 ;

Table created.

SQL_11gR1> DECLARE
2 TYPE t_array_tab IS TABLE OF test%ROWTYPE ;
3 t_array_col t_array_tab ;
4 CURSOR cur IS
5 SELECT * FROM test ;
6 BEGIN
7 OPEN cur ;
8 LOOP
9
10 FETCH cur BULK COLLECT INTO t_array_col LIMIT 100 ;
11
12 FORALL i IN 1 .. t_array_col.COUNT
13 INSERT INTO test_2(id, texto)
14 VALUES (t_array_col(i).id, t_array_col(i).texto) ;
15
16 EXIT WHEN cur%NOTFOUND ;
17
18 END LOOP ;
19 COMMIT ;
20 CLOSE cur ;
21 END ;
22 /

PL/SQL procedure successfully completed.

SQL_11gR1> SELECT count(*) FROM test_2;

COUNT(*)
----------
10000


Saludos

Lucy dijo...

hola,
al correr mi bulk collect, me sale este error:
cannot reference fields of bulk in- bind table of records.

mi codigo es este
OPEN PREVIO_ANEXO;
LOOP
FETCH PREVIO_ANEXO BULK COLLECT
INTO V_ANEXO LIMIT 100;
FORALL I IN 1 .. V_ANEXO.COUNT SAVE EXCEPTIONS
INSERT INTO SG_D_ANEXPADRONMEM
(AP_ANOVIG,
CG_CODIGO,
PR_NOMDER,
TE_TIPOEX,
EE_ESTAEX,
SE_SITUEX,
PR_HECFOR,
HE_CANHEC,
PR_METALI,
DE_CODDEM,
DE_DESDEP,
DE_DESPRV,
DE_DESDIS,
DV_CALIFI,
RE_CODRES,
RD_RESOLU,
RD_FECRES,
RD_FECCON,
RE_DESRES,
EE_ESTACT,
SE_SITACT,
AN_INFRSL,
RD_FECTIT,
RD_FECCOT,
AN_FECPRO,
AN_FECFOR)
VALUES
(V_ANEXO(I).AP_ANOVIG,
V_ANEXO(I).CG_CODIGO,
V_ANEXO(I).PR_NOMDER,
V_ANEXO(I).TE_TIPOEX,
V_ANEXO(I).EE_ESTAEX,
V_ANEXO(I).SE_SITUEX,
V_ANEXO(I).PR_HECFOR,
V_ANEXO(I).HE_CANHEC,
V_ANEXO(I).PR_METALI,
V_ANEXO(I).DE_CODDEM,
V_ANEXO(I).DE_DESDEP,
V_ANEXO(I).DE_DESPRV,
V_ANEXO(I).DE_DESDIS,
V_ANEXO(I).DV_CALIFI,
V_ANEXO(I).RE_CODRES,
V_ANEXO(I).RD_RESOLU,
V_ANEXO(I).RD_FECRES,
V_ANEXO(I).RD_FECCON,
V_ANEXO(I).RE_DESRES,
V_ANEXO(I).EE_ESTACT,
V_ANEXO(I).SE_SITACT,
V_ANEXO(I).AN_INFRSL,
V_ANEXO(I).RD_FECTIT,
V_ANEXO(I).RD_FECCOT,
V_ANEXO(I).AN_FECPRO,
V_ANEXO(I).AN_FECFOR);
COMMIT;
EXIT WHEN PREVIO_ANEXO%NOTFOUND;
END LOOP;


haber si me puedes dar unas luces, aplique lo de tu blog, pero nada

Muchas gracias de antemano


Lucy

Leonardo Horikian dijo...

Hola Lucy,

Supongo que la versión de base de datos que estas utilizando es 9i, cierto? El problema que tenes es que en 9i no podes utilizar FORALL sobre una colección del tipo records como la que vos tenes.

Para solucionar esto, tenes 2 simples soluciones (existen más...):

1) Crear una colección por cada uno de los campos que queres recuperar en el BULK COLLECT e insertar con el FORALL (como en mi ejemplo).

Fijate que en mi ejemplo no estoy utilizando una colección del tipo records sino 2 colecciones separadas.

2) Crear un tipo objecto con todos los campos que necesitás y luego declarar la colección del tipo objeto.

Ej:

SQL> create table tabla(x number, y number)
2 /

Table created.

SQL> create or replace type coleccion_obj as object(x number, y number)
2 /

Type created.

SQL> create or replace type coleccion_tab as table of coleccion_obj
2 /

Type created.

SQL> declare
2 coleccion_col coleccion_tab := coleccion_tab();
3 begin
4 coleccion_col.extend(2);
5 coleccion_col(1) := coleccion_obj(10, 100);
6 coleccion_col(2) := coleccion_obj(20, 200);
7
8 forall i in coleccion_col.first .. coleccion_col.last
9 insert into tabla values(coleccion_col(i).x, coleccion_col(i).y);
10
11 for i in coleccion_col.first .. coleccion_col.last loop
12 dbms_output.put_line('X: '||coleccion_col(i).x||' - Y: '||coleccion_col(i).y);
13 end loop;
14 end;
15 /

X: 10 - Y: 100
X: 20 - Y: 200

PL/SQL procedure successfully completed.


Saludos

Lucy dijo...

muchas gracias, al fin salio mi Bulk collect...

QuirozReynaldo dijo...

Hola Leonardo, primero felicitarte por el artículo, bueno mi problema es similar, tengo un par de tablas de 20 millones de registros cada una, en lugar de hacer inserts tengo la necesidad de hacer updates sobre las columnas para modificar los datos usando algunos criterios para seleccion los mismos. El proceso esta muy lento. Para probar estoy haciendo el update sobre la totalidad de los registros, quité los indices la tabla la creé, con nologging y pctfree 0 el valor LIMIT 100 para una prueba y 1000 para una segunda prueba, pero la lentitud persiste.
Saludos:
Reynaldo (Bolivia)

Anónimo dijo...

Leonardo.
Gracias por el foro, muy interesante.
Además te tomas el tiempo para responder las preguntas. Tienes vocación de explicar.
Sigue adelante;
Saludos desde CR;
GErardo gbarquero@gandgcr.com

Anónimo dijo...

Hola Leonardo, que pasaria si aumento el valor de limit 100 a 500 por ejemplo. Y me parece muy excelente que compartas tus conocimientos

Juan pablo Borrero

Leonardo Horikian dijo...

Hola Juan Pablo Borrero,

La idea es probar qué valor es el ideal para tu proceso. Podes probar en definirlo en 100, ir subiendo el valor y ver si el tiempo de respuesta cambia o permanece estático.

Recuerda lo que hablé sobre colocar valores muy altos.

Saludos

Leonardo Horikian dijo...

Hola QuirozReynaldo,

Para ayudarte mejor necesitaría que me muestres el update que estas ejecutando junto con el plan de ejecución.

Por otro lado, de los 20 millones de registros que tiene cada una de las tablas, cuantos registros estas modificando? Pregunto esto porque a lo mejor (dependiendo del porcentaje de registros que estes modificando) te convenga utilizar la estrategia de volver a insertar los registros (con la modificación en el insert) en vez de modificarlos ya que el insert es una operación menos costosa que el update.

Saludos

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.