Problemas con un insert

lemf
03 de Septiembre del 2004
Que tal,
Estoy realizando un operación de copia de una tabla a otra con una estructura igual (9 campos numericos y un date), de exactamente 23 839 823 registros, usando la siguiente instrucción

INSERT INTO TABLA_DESTINO
(CAMPO1,CAMPO2,CAMPO3,CAMPO4,CAMPO5,CAMPO6,CAMPO7,CAMPO8,CAMPO9,CAMPO10)
SELECT CAMPO1,CAMPO2,CAMPO3,CAMPO4,CAMPO5,CAMPO6,CAMPO7,CAMPO8,CAMPO9,CAMPO10
FROM TABLA_ORIGINAL WHERE CAMPO1=14;

existe en la tabla origen mas registros, la busqueda utiliza un indice unico

EL PROBLEMA consiste que luego de un tiempo de ejecución el sistema ya no permite realizar ninguna operación por otros usuarios u otras aplicaciones, es decir, ya no procesa nada y este insert nunca termina. En la ultima prueba que se hizo, la base se la tuvo que bajar y subir para continuar con las transacciones cotidianas

Que debo hacer para que no ocurra esto????

Si la respuesta es que debo aumentar mi espacio SGA, como puedo calcular el espacio necesario y optimo para estas transacciones

Gracias de antemano

Jorge-DBA
03 de Septiembre del 2004
Que tal:
Antes de modificar la SGA...
Hay varias formas las cuales puedas hacer que un insert se agilice como por ejemplo:

1)
-- Verifica si tu base de datos esta en modo ArchiveLog, si es asi, altera la tabla poniendola en NOLOGGING, esto hará que no demande contienda los procesos de Background LGWR y ARCH sobre la escritura a los archivos que estan archivando la base de datos.

2)
-- Verifica cuantos índices tiene tu tabla destino, te recomiendo que antes de realizar tu insert, verifiques que tengas espacio suficiente disponible en el Tablespace "Temporal" del usuario, exportes la tabla destino y posteriormente remuevas todos los índices de dicha tabla, una vez hecho esto, realizas el insert de sentencia, intenta importar los índices con la utilería import, esto ayudará mucho, es muy rápido.

3)
Si tienes mas de un procesador en el Database Server, y si tu base de datos esta configurada en Parallel
A)Utiliza Hints de parelelismo + el hint APPEND
si no tienes paralelismo
b)Solo append ejemplo:
INSERT /*+ APPEND */ INTO TABLA_DESTINO...
Recuerda que Cuando Oracle inserta en una tabla, verifica los bloques de datos y los administra insertando la información según como este configurado el parámetro PCTUSED y PCTFREE, Append ignora estos parámetros y no validará los bloques donde pueda administrarlos para almacenarlos, va insertar en el último bloque de la última extensión datos dode no existan registros.

4)
Verifica la contienda de los RBS o Undotablespace

5) LA FORMA COMO TE RECOMENDARÍA, es un tanto laboriosa y tienes que tener un poco de conocimiento y experiencia en el comportamiento del EXPORT/IMPORT si lo conoces será facil para ti y se hará más rápido que usar una setencia Insert tradicional, la desventaja de esta recomendación es que la base de datos estará no disponible unos momentos mientras realizas esta tarea.

Al ver tu insert estoy viendo que tienes el mismo numero de campos origen que en el destino, te
recomiendo que realices lo siguiente:

Si estas en la version 8.1.x o posterior se puede aprobechar un export con la cláusula WHERE:

PASO A) Verificar que exista un respaldo de toda la base de datos, y verifica la definición de la tabla destino, indices grants, integridad constraints ETC, y la mantienes en script (solo de esta tabla), y la base de datos se pone en modo restringido.


Paso B)

Remover tabla destino:

SQL>Drop table TABLA_DESTINO;

Paso C)

Renombrar la tabla origen de TABLA_ORIGINAL a TABLA_ORIGINAL_TEMP

Paso D)
Export con Where a la tabla TABLA_ORIGINAL_TEMP
Ejemplo:

archivo de parámetros del export:

file=TABLA_ORIGINAL_TEMP.dmp
log=TABLA_ORIGINAL_TEMP.log
query="WHERE CAMPO1=14"
tables=(TABLA_ORIGINAL_TEMP)
userid=demo/[email protected]
indexes=n
constraints=n
grants=n

(Solo exportará los 23 839 823)

Paso E)

Renombras tu tabla Origen de TABLA_ORIGINAL_TEMP a TABLA_ORIGINAL (como estaba inicialmente)

Paso F)
importas la tabla que se exportó en el paso D
(Creará una tabla llamada: TABLA_ORIGINAL_TEMP, no tendras problemas por que la tabla origen se ha renombrado al nombre que tenia inicialmente )

Paso G)
Esta tabla importada la renombras a TABLA_DESTINO.

Paso H)

Reconstruyes la definicion de la tabla con tu script del paso A (índices, privilegios, integrdad, etc), y recompilas los strore procedures de la base de datos

Listo.!

Espero que te haya servido esta información.

Saludos.

Jorge-DBA