Añadir Columnas con Valores Por Defecto Oracle 11g v/s Predecedores.

March 4th, 2009 por Mauricio Diaz
gravatar
Compartir este post:
  • Google Reader
  • Google Plus
  • Identi.ca
  • Meneame
  • PDF
  • Print
  • Twitter
  • Facebook
  • Email
  • LinkedIn

En versiones anteriores a Oracle 11g, al añadir columnas con propiedad not null con valor default a una tabla con muchos registros suele ser costoso para la base de datos ya que al realizar esta acción se producen ciertos eventos que afectan a la performance de esta, como por ejemplo la generación de redologs switch, undo, aumento excesivo del SCN de la bd.

He aquí una demostración realizada en una base de datos versión 10gR2, sobre una tabla con una cantidad de registros más o menos considerable.

SQL> select count(1) from datos_prueba;

COUNT(1)

———-

2000000

Transcurrido: 00:00:00.07

  • · Verificando la secuencia de redolog switch de la instancia.

SQL> archive log list

Modo log de la base de datos Modo de Archivado

Archivado automático Activado

Destino del archivo USE_DB_RECOVERY_FILE_DEST

Secuencia de log en línea más antigua 139

Siguiente secuencia de log para archivar 141

Secuencia de log actual 141

  • · Verificando el SCN de la base de datos.

SQL> select name,current_scn from v$database;

NAME CURRENT_SCN

——— ———– ———

BISE1DB 34542913

  • · Alterar la tabla, Añadiendo una columna a la tabla con un valor por defecto

SQL> alter table datos_prueba add col4 varchar2(5) DEFAULT ‘ABC0’ NOT NULL ;

Tabla modificada.

Transcurrido: 00:02:33.62

  • · Verificando la secuencia de redolog switch de la instancia después de de alterar la tabla.

SQL> archive log list

Modo log de la base de datos Modo de Archivado

Archivado automático Activado

Destino del archivo USE_DB_RECOVERY_FILE_DEST

Secuencia de log en línea más antigua 162

Siguiente secuencia de log para archivar 164

Secuencia de log actual 164

  • · Verificando nuevamente el valor del scn de la base datos.

SQL> select name,current_scn from v$database;

NAME CURRENT_SCN

——— — ——- ————

BISE1DB 34556846

Tal como muestran las métricas luego de alterar la tabla se nota un considerable aumento de las secuencias de redolog switch y por ende el lapso de tiempo que lleva a cabo en realizar dicha acción, y el valor del SCN.

Ahora, haciendo una demostración sobre una base de datos 11gR1 sobre una tabla similar.

SQL> select count(1) from datos_prueba;

COUNT(1)

———-

2000000

Elapsed: 00:00:00.65

  • · Verificando la secuencia de redolog switch de la instancia.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 21

Next log sequence to archive 23

Current log sequence 23

  • · Verificando el SCN de la base de datos.

SQL> select name,current_scn from v$database;

NAME CURRENT_SCN

——— ———–

ORCL 645617

Elapsed: 00:00:00.03

  • · Alterar la tabla, Añadiendo una columna a la tabla con un valor por defecto

SQL> alter table datos_prueba add col4 varchar2(5) default ‘ABC0’ not null;

Table altered.

Elapsed: 00:00:01.86

  • · Verificando la secuencia de redolog switch de la instancia después de de alterar la tabla.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 21

Next log sequence to archive 23

Current log sequence 23

  • ·Verificando nuevamente el valor del scn de la base datos.

SQL> select name,created,current_scn from v$database;

NAME CURRENT_SCN

——— ———–

ORCL 645730

Elapsed: 00:00:00.02

Verificando las métricas anteriores, se nota una observación muy importante al verificar las secuencias de redolog antes y después de alterar la tabla, no se produce redolog switch se nota que las secuencias se mantienen y por ende el tiempo que lleva a cabo en realizar esta acción es de menos de 2 segundos comparado con la instancia 10gR2 que demora mas de dos minutos y produce redolog switch ya que las secuencias aumentan.