martes, 28 de agosto de 2007

APPEND hint

El hint APPEND permite que Oracle comience a escribir bloques nuevos luego de la HWM (high water mark) de la tabla.

Veo a menudo a desarrolladores y DBA's usar este hint, y en la mayoría de los casos, el hint no es usado por Oracle. Pero porque?

Veamos un ejemplo:

SQL_9iR2> CREATE TABLE test ( a number, b number ) ;

Table created.

SQL_9iR2> SET AUTOTRACE TRACEONLY STATISTICS

SQL_9iR2> INSERT INTO test
2 SELECT level, level
3 FROM dual
4 CONNECT BY level <= 1000000 ;

1000000 rows created.

Statistics
---------------------------------------------------
9 recursive calls
10713 db block gets
4128 consistent gets
2 physical reads
20461776 redo size

626 bytes sent via SQL*Net to client
835 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)

1000000 rows processed

Como podemos ver, se está generando aprox. 19 MB de Redo para insertar en la tabla 1.000.000 de registros. Qué sucedería si necesitamos realizar una carga masiva de datos? Estaríamos consumiendo gran cantidad de Redo.
Para reducir la cantidad de Redo que se genera, podemos usar el hint APPEND.

SQL_9iR2> INSERT /*+ APPEND */ INTO test
2 SELECT level, level
3 FROM dual
4 CONNECT BY level <= 1000000 ;

1000000 rows created.

Statistics
---------------------------------------------------
688 recursive calls
242 db block gets
242 consistent gets
0 physical reads
17120808 redo size

612 bytes sent via SQL*Net to client
849 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)

1000000 rows processed

Con el hint APPEND estamos utilizando aprox. 16 MB de Redo. Pero realmente se está utilizando el hint? No hubo una gran diferencia en la generación del Redo. Porque? Esto es debido a que existen 2 condiciones para que se utilice el hint, y debemos cumplir alguna de ellas:

- La base de datos debe estar en modo NOARCHIVELOG.
- La tabla que estamos utilizando debe estar en modo NOLOGGING.

SQL_9iR2> SELECT log_mode from v$database ;

LOG_MODE
------------
ARCHIVELOG

1 row selected.

En la base de datos que estoy utilizando para éste ejemplo estoy utilizando ARCHIVELOG, por lo cual vamos a colocar la tabla en modo NOLOGGING para ver el efecto de utilizar el hint.

SQL_9iR2> ALTER TABLE test NOLOGGING ;

Table altered.

SQL_9iR2> INSERT /*+ APPEND */ INTO test
2 SELECT level, level
3 FROM dual
4 CONNECT BY level <= 1000000 ;

1000000 rows created.

Statistics
---------------------------------------------------
153 recursive calls
18 db block gets
23 consistent gets
1 physical reads
5944 redo size

614 bytes sent via SQL*Net to client
849 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)

1000000 rows processed

Claramente podemos ver que ahora estamos utilizando sólo 5 KB de Redo. Esto nos permite optimizar las operaciones de carga masiva de datos.

NOTA: El APPEND sólo podemos utilizarlo en sentencias del tipo INSERT AS SELECT.

9 comentarios:

Anónimo dijo...

Muchas gracias desde España!!!

Anónimo dijo...

Hola, me queda una duda, si por ejemplo yo estoy creando una tabla T1 a la que le voy a insertar datos de una tabla T2, cuál de estas debe estar NOLOGGING en el momento de efectuar el INSERT??? La T1 o la T2???

Leonardo Horikian dijo...

Hola, la tabla T1.

Anónimo dijo...

Hola, otra pregunta, quería saber si la sentencia Append se puede incluir al crear una tabla de la forma:
CREATE TABLE NOMBRE_TABLA NOLOGGING AS SELECT * FROM...
De qué forma se usaría si es posible???
Muchas gracias, este blog es excelente.

Leonardo Horikian dijo...

Hola, el hint APPEND no se puede utilizar junto con la sentencia "CREATE TABLE". De todas formas, cuando se crea una tabla desde una sentencia SQL, la carga a esa tabla se hace en forma directa al igual que cuando se utiliza el hint APPEND.

[ [EBP]] dijo...

Hola Leonardo, una consulta...

Si no se escriben las transacciones en RedoLog, quiere decir que tampoco se escribirán en el ArchiveLog???

Leonardo Horikian dijo...

Hola [ [EBP]],

Si no se escriben las transacciones en los RedoLog, quiere decir que tampoco se escribirán en el ArchiveLog. Es por eso, que luego de utilizar el hint APPEND con NOLOGGING debemos realizar un Backup!

Saludos

David Lozano dijo...

Esto sería correcto?
INSERT /*+ append */ INTO tabla NOLOGGING select...

Leonardo Horikian dijo...

Hola David Lozano,

Si, la sentencia es correcta.

Ejemplo:


SQL> create table a(id number);

Table created.

SQL> create table b(id number);

Table created.

SQL> INSERT /*+ APPEND */ INTO a NOLOGGING SELECT * FROM b;

0 rows created.


Saludos