Creación de PK sobre tablas grandes

Para analizar las distintas alternativas que tenemos de crear una primary key vamos a usar una tabla particionada de ejemplo con 8M de registros (ver en apendice detalle de la tabla). El equipo de prueba es un SunFire 890 con 16Gb de RAM y 8 procesadores. Para independizar los casos y limpiar el buffer cache se hace un flush del buffer_cache entre cada caso.

Caso 1: Creación de pk en forma directa

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo Total: 69.672s

Esta operacion crea implicitamente un indice unique global de soporte a la pk. Cuando se elimina la pk se elimina implicitamente el indice de soporte.


Caso 2: Pre-Creación de indice non-unique global

create index pk_t_part on t_part(c3,c1,c5,c2);

Tiempo: 56.984s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 76.5s

Tiempo Total: 133.484s

Al eliminar no se elimina el indice ya que fue creado en forma independiente.


Caso 3: Pre-Creación de indice non-unique local

create index pk_t_part on t_part(c3,c1,c5,c2) local;

Tiempo: 58.797s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 79.516s

Tiempo Total: 138.313s

Al eliminar no se elimina el indice ya que fue creado en forma independiente.

Caso 4: Pre-Creación de indice unique global

create unique index pk_t_part on t_part(c3,c1,c5,c2)

Tiempo: 54.156s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 16.938s

Tiempo Total: 71.094s

Es necesario eliminar explicitamente el indice cdo se elimina la pk ya que no se elimina automaticamente.

Caso 5: Pre-Creación de indice unique local

create unique index pk_t_part on t_part(c3,c1,c5,c2) local

Tiempo: 60.436s

alter table t_part
add constraint pk_t_part

Tiempo: 12.093s

Tiempo Total: 72.529s

Caso 6: Creación de pk con especificación de creación de índice

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2))

Tiempo Total: 93.688s

Al eliminar la pk se elimina implicitamente el indice.

Caso 7: Pre-Creacion de indice global no-unique en paralelo y nologging

create index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local

Tiempo: 15.016s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 101.456s


Tiempo Total: 116.472s


Caso 8: Pre-Creacion de indice local no-unique en paralelo y nologging

create index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local

Tiempo: 11.687s.

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 99.5s

Tiempo Total: 105.687s

Caso 9:Pre-Creacion de indice unique global en paralelo y nologging


create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8)

Tiempo: 14.281s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 12.187s

Tiempo Total: 26.468s

Caso 10:Pre-Creacion de indice unique local en paralelo y nologging

create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local

Tiempo: 10.328s

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)

Tiempo: 16.375s

Tiempo Total: 26.703s

Caso 11: Creación de pk con creación de índice global en paralelo y nologging

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8))

Tiempo Total: 103.906s


Caso 12: Creación de pk con creación de índice local en paralelo y nologging

alter table t_part
add constraint pk_t_part
primary key (c3,c1,c5,c2)
using index (create unique index pk_t_part on t_part(c3,c1,c5,c2)
nologging parallel (degree 8) local)

Tiempo Total: 97.391s


Conclusión

Casos Tiempo Total
1 Creación de pk en forma directa 69.672s
2 Creación de indice non-unique global 133.484s
3 Pre-Creación de indice non-unique local 138.313s
4 Pre-Creación de indice unique global 71.094s
5 Pre-Creación de indice unique local 72.529s
6 Creación de pk con especificacion de creación de indice 93.688s
7 Pre-Creacion de indice global no-unique en paralelo y nologging 116.472s
8 Pre-Creacion de indice local no-unique en paralelo y nologging 105.687s
9 Pre-Creacion de indice unique global en paralelo y nologging 26.468s
10 Pre-Creacion de indice unique local en paralelo y nologging 26.703s
11 Creación de pk con creación de índice global en paralelo y nologging 103.906s
12 Creación de pk con creación de índice local en paralelo y nologging 97.391s
Tabla 1: Análisis de tiempos por caso


De los casos evaluados podemos concluir:

- El tiempo que insume la creación de un índice unique comparado con la creación de un índice no-unique es similar.
- El tiempo que insume la creación de un índice global comparado con la creación de un índice local es similar.
- Los casos en los que se pre-crea un índice unique con paralelismo y nologging (casos 9 y 10) son los mas eficientes.
- En los casos 11 y 12 pareciera no tener efecto el paralelismo ni el uso de índice unique dentro de la sentencia de creación de la pk, ya que los tiempos no son buenos.