Cláusulas de almacenamiento (INITIAL EXTENTS) en ‘SYSTEM LMT’s’

Cláusulas de almacenamiento (INITIAL EXTENTS) en ‘SYSTEM LMT’s’

Oracle presentó los LMT’s (‘Locally Managed Tablespaces’, Tablespaces Gestionados Localmente) a partir de la versión 8. Al principio los DBA’s eran remisos a utilizarlos, pero a partir de la versión 9 su uso no sólo se ha extendido, sino que parece ser casi obligado por el gran número de ventajas que supone (y la ausencia de inconvenientes -al menos que yo conozca-).

Su funcionamiento se basa en ‘bitmaps’ en los datafiles -en vez de tablas en el diccionario de datos como se hacía anteriormente- para localizar los ‘extents’ libres donde alojar nuevos datos. Esto hace que el espacio adyacente pueda verse como ‘uno’ y no fragmentado, evitando así la necesidad de ‘COALESCE’.

En el caso de los LMT’s gestionados por el sistema (‘SYSTEM MANAGED’), a diferencia de los LMT de extensiones de tamaño fijo (‘UNIFORM SIZE’), Oracle tiene un algoritmo propio que calcula el tamaño de los ‘extents’ según se van necesitando.

De esta manera los primeros 16 ‘extents’ serán de 64Kb, sumando así 1Mb en total. En el siguiente escalón se utilizarán hasta 63 ‘extents’ de 1Mb, llegando así a 64 Mb (contando con los 16 de 64Kb). Finalmente los siguientes ‘extents’ serán de 8Mb. y se utilizarán ya todos de este tamaño según vaya creciendo el segmento.

El objetivo parece claro: para segmentos pequeños, se utilizan ‘extents’ pequeños. Cuando el segmento crece los extents se van haciendo más grandes (1Mb), y sólo cuando el tamaño alcanza proporciones mayores (más de 64Mb) se utilizarán ‘extents’ de 8Mb. Es una especie de funcionamiento mejorado del viejo ‘PCTINCREASE’.

Aunque el uso de LMT’s ha dejado obsoleto el uso de INITIAL, NEXT, y MINEXTENTS, resulta que dichos parámetros de almacenamientos SÍ TIENEN REPERCUSIÓN en el momento de crear un objeto (segmento). Oracle reservará el espacio requerido en la creación del objeto, si bien los trasladará a ‘sus números’ utilizando los ‘extents’ necesarios según su algoritmo.

Lo curioso del caso es que Oracle puede decidir saltarse tramos según esas condiciones iniciales de creación.

Para verlo, vamos a utilizar esta vez un índice, para variar (aunque lo mismo valdría para una tabla).

Primero creamos una tabla:

carlosal@db01.xxxxxx> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production


carlosal@db01.xxxxxx> create table prueba07 as select * from dba_objects a, prueba01 b;

Tabla creada.

Transcurrido: 00:00:05.04
carlosal@db01.xxxxxx> select  count('x') from prueba07;

COUNT('X')
----------
    144781

carlosal@db01.xxxxxx>

carlosal@db01.xxxxxx> SELECT
  2     TABLESPACE_NAME,
  3     BLOCK_SIZE,
  4     STATUS,
  5     EXTENT_MANAGEMENT,
  6     ALLOCATION_TYPE
  7   FROM DBA_TABLESPACES
  8  WHERE TABLESPACE_NAME='TBS1'
  9  /

TABLESPACE_NAME BLOCK_SIZE STATUS    EXTENT_MAN ALLOCATIO
--------------- ---------- --------- ---------- ---------
TBS1                  8192 ONLINE    LOCAL      SYSTEM

Si creamos un índice ‘tal cual’, Oracle utilizará su algoritmo sin novedad aparente empezando con los 16 ‘extents’ de 64Kb.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2      ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3      TABLESPACE TBS1
  4  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1';

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     2233   65536      8            4
PRUEBA07_IDX1 INDEX                1       4     2241   65536      8            4
PRUEBA07_IDX1 INDEX                2       4     2249   65536      8            4
PRUEBA07_IDX1 INDEX                3       4     2257   65536      8            4
PRUEBA07_IDX1 INDEX                4       4     2265   65536      8            4
PRUEBA07_IDX1 INDEX                5       4     2273   65536      8            4
PRUEBA07_IDX1 INDEX                6       4     2281   65536      8            4
PRUEBA07_IDX1 INDEX                7       4     2289   65536      8            4
PRUEBA07_IDX1 INDEX                8       4     2297   65536      8            4
PRUEBA07_IDX1 INDEX                9       4     2305   65536      8            4
PRUEBA07_IDX1 INDEX               10       4     4361   65536      8            4
PRUEBA07_IDX1 INDEX               11       4     4369   65536      8            4
PRUEBA07_IDX1 INDEX               12       4     4377   65536      8            4
PRUEBA07_IDX1 INDEX               13       4     4385   65536      8            4
PRUEBA07_IDX1 INDEX               14       4     4393   65536      8            4
PRUEBA07_IDX1 INDEX               15       4     4401   65536      8            4
PRUEBA07_IDX1 INDEX               16       4     4489 1048576    128            4
PRUEBA07_IDX1 INDEX               17       4     4617 1048576    128            4
PRUEBA07_IDX1 INDEX               18       4     4745 1048576    128            4
PRUEBA07_IDX1 INDEX               19       4     4873 1048576    128            4
PRUEBA07_IDX1 INDEX               20       4     5001 1048576    128            4
PRUEBA07_IDX1 INDEX               21       4     5129 1048576    128            4

22 filas seleccionadas.

carlosal@carlosal@db01.xxxxxx>

Lo mismo ocurre si creamos el índice con un ‘INITIAL’ por debajo del primer tramo (512k):

carlosal@db01.xxxxxx> DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2      ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3      STORAGE( INITIAL 512k)
  4      TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1';

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     2233   65536      8            4
PRUEBA07_IDX1 INDEX                1       4     2241   65536      8            4
PRUEBA07_IDX1 INDEX                2       4     2249   65536      8            4
PRUEBA07_IDX1 INDEX                3       4     2257   65536      8            4
PRUEBA07_IDX1 INDEX                4       4     2265   65536      8            4
PRUEBA07_IDX1 INDEX                5       4     2273   65536      8            4
PRUEBA07_IDX1 INDEX                6       4     2281   65536      8            4
PRUEBA07_IDX1 INDEX                7       4     2289   65536      8            4
PRUEBA07_IDX1 INDEX                8       4     2297   65536      8            4
PRUEBA07_IDX1 INDEX                9       4     2305   65536      8            4
PRUEBA07_IDX1 INDEX               10       4     4361   65536      8            4
PRUEBA07_IDX1 INDEX               11       4     4369   65536      8            4
PRUEBA07_IDX1 INDEX               12       4     4377   65536      8            4
PRUEBA07_IDX1 INDEX               13       4     4385   65536      8            4
PRUEBA07_IDX1 INDEX               14       4     4393   65536      8            4
PRUEBA07_IDX1 INDEX               15       4     4401   65536      8            4
PRUEBA07_IDX1 INDEX               16       4     4489 1048576    128            4
PRUEBA07_IDX1 INDEX               17       4     4617 1048576    128            4
PRUEBA07_IDX1 INDEX               18       4     4745 1048576    128            4
PRUEBA07_IDX1 INDEX               19       4     4873 1048576    128            4
PRUEBA07_IDX1 INDEX               20       4     5001 1048576    128            4
PRUEBA07_IDX1 INDEX               21       4     5129 1048576    128            4

22 filas seleccionadas.

carlosal@carlosal@db01.xxxxxx>

Pero si intentamos in INITIAL mayor de 1M tendremos que:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2      ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3      STORAGE( INITIAL 1032k)
  4      TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 1048576    128            4
PRUEBA07_IDX1 INDEX                1       4     4489 1048576    128            4
PRUEBA07_IDX1 INDEX                2       4     4617 1048576    128            4
PRUEBA07_IDX1 INDEX                3       4     4745 1048576    128            4
PRUEBA07_IDX1 INDEX                4       4     4873 1048576    128            4
PRUEBA07_IDX1 INDEX                5       4     5001 1048576    128            4
PRUEBA07_IDX1 INDEX                6       4     5129 1048576    128            4

7 filas seleccionadas.

Ahora Oracle ha elegido ‘extents’ de 1Mb, saltándose los de 64Kb.

Otra vez, lo mismo ocurre si nos mantenemos por debako de 64 Mb:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2  ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3  STORAGE( INITIAL 20m)
  4  TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 1048576    128            4
PRUEBA07_IDX1 INDEX                1       4     4489 1048576    128            4
PRUEBA07_IDX1 INDEX                2       4     4617 1048576    128            4
PRUEBA07_IDX1 INDEX                3       4     4745 1048576    128            4
PRUEBA07_IDX1 INDEX                4       4     4873 1048576    128            4
PRUEBA07_IDX1 INDEX                5       4     5001 1048576    128            4
PRUEBA07_IDX1 INDEX                6       4     5129 1048576    128            4
PRUEBA07_IDX1 INDEX                7       4     5257 1048576    128            4
PRUEBA07_IDX1 INDEX                8       4     5385 1048576    128            4
PRUEBA07_IDX1 INDEX                9       4     5513 1048576    128            4
PRUEBA07_IDX1 INDEX               10       4     5641 1048576    128            4
PRUEBA07_IDX1 INDEX               11       4     5769 1048576    128            4
PRUEBA07_IDX1 INDEX               12       4     5897 1048576    128            4
PRUEBA07_IDX1 INDEX               13       4     6025 1048576    128            4
PRUEBA07_IDX1 INDEX               14       4     6153 1048576    128            4
PRUEBA07_IDX1 INDEX               15       4     6281 1048576    128            4
PRUEBA07_IDX1 INDEX               16       4     6409 1048576    128            4
PRUEBA07_IDX1 INDEX               17       4     6537 1048576    128            4
PRUEBA07_IDX1 INDEX               18       4     6665 1048576    128            4
PRUEBA07_IDX1 INDEX               19       4     6793 1048576    128            4

20 filas seleccionadas.

carlosal@db01.xxxxxx>

Ahora, para satisfacer los 20Mb solicitados, ha optado por utilizar 20 ‘extents’ de 1Mb, obviando de nuevo los 16 de 64Kb.

Si solicitamos un ‘initial’ de 64 Mb, utilizará 64 ‘extents’ de 1 Mb cada uno:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2      ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3      STORAGE( INITIAL 64m)
  4      TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx>
carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 1048576    128            4
PRUEBA07_IDX1 INDEX                1       4     4489 1048576    128            4
PRUEBA07_IDX1 INDEX                2       4     4617 1048576    128            4
PRUEBA07_IDX1 INDEX                3       4     4745 1048576    128            4
[...]
PRUEBA07_IDX1 INDEX               61       4    12169 1048576    128            4
PRUEBA07_IDX1 INDEX               62       4    12297 1048576    128            4
PRUEBA07_IDX1 INDEX               63       4    12425 1048576    128            4

64 filas seleccionadas.

carlosal@db01.xxxxxx>

Pero si sobrepasamos, aunque sea un poquito, los 64mB, vemos que se vuelve a producir un salto de tramo:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2      ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3      STORAGE( INITIAL 65544k)
  4      TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 8388608   1024            4
PRUEBA07_IDX1 INDEX                1       4     5385 8388608   1024            4
PRUEBA07_IDX1 INDEX                2       4     6409 8388608   1024            4
PRUEBA07_IDX1 INDEX                3       4     7433 8388608   1024            4
PRUEBA07_IDX1 INDEX                4       4     8457 8388608   1024            4
PRUEBA07_IDX1 INDEX                5       4     9481 8388608   1024            4
PRUEBA07_IDX1 INDEX                6       4    10505 8388608   1024            4
PRUEBA07_IDX1 INDEX                7       4    11529 8388608   1024            4
PRUEBA07_IDX1 INDEX                8       4    12553 1048576    128            4

9 filas seleccionadas.

carlosal@db01.xxxxxx>

Ahora se han utilizado directamente los ‘extents’ de 8Mb. Pero curiosamente el último extent (el que pasa de los 64 Mb) es de 1Mb!!

Veamos qué pasa si vamos un poco más allá:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx>  CREATE INDEX PRUEBA07_IDX1
  2  ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3  STORAGE( INITIAL 65m)
  4  TABLESPACE TBS1
  5  /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 8388608   1024            4
PRUEBA07_IDX1 INDEX                1       4     5385 8388608   1024            4
PRUEBA07_IDX1 INDEX                2       4     6409 8388608   1024            4
PRUEBA07_IDX1 INDEX                3       4     7433 8388608   1024            4
PRUEBA07_IDX1 INDEX                4       4     8457 8388608   1024            4
PRUEBA07_IDX1 INDEX                5       4     9481 8388608   1024            4
PRUEBA07_IDX1 INDEX                6       4    10505 8388608   1024            4
PRUEBA07_IDX1 INDEX                7       4    11529 8388608   1024            4
PRUEBA07_IDX1 INDEX                8       4    12553 1048576    128            4

9 filas seleccionadas.

Exactamente lo mismo. Si excedemos hasta 1Mb de un múltiplo de 8Mb, se utilizará un último ‘extent’ de 1Mb.

Si vamos más allá, volvemos a los ‘extents’ de 8Mb:

carlosal@db01.xxxxxx>  DROP INDEX PRUEBA07_IDX1
  2  /

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2  ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3  STORAGE( INITIAL 66M)
  4   TABLESPACE TBS1
  5   /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 8388608   1024            4
PRUEBA07_IDX1 INDEX                1       4     5385 8388608   1024            4
PRUEBA07_IDX1 INDEX                2       4     6409 8388608   1024            4
PRUEBA07_IDX1 INDEX                3       4     7433 8388608   1024            4
PRUEBA07_IDX1 INDEX                4       4     8457 8388608   1024            4
PRUEBA07_IDX1 INDEX                5       4     9481 8388608   1024            4
PRUEBA07_IDX1 INDEX                6       4    10505 8388608   1024            4
PRUEBA07_IDX1 INDEX                7       4    11529 8388608   1024            4
PRUEBA07_IDX1 INDEX                8       4    12553 8388608   1024            4

9 filas seleccionadas.

carlosal@db01.xxxxxx>

Este comportamiento se repite en todos los múltiplos de 8Mb:

carlosal@db01.xxxxxx> drop index PRUEBA07_IDX1;

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2  ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3  STORAGE( INITIAL 73M)
  4   TABLESPACE TBS1
  5   /

Índice creado.


carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 8388608   1024            4
PRUEBA07_IDX1 INDEX                1       4     5385 8388608   1024            4
PRUEBA07_IDX1 INDEX                2       4     6409 8388608   1024            4
PRUEBA07_IDX1 INDEX                3       4     7433 8388608   1024            4
PRUEBA07_IDX1 INDEX                4       4     8457 8388608   1024            4
PRUEBA07_IDX1 INDEX                5       4     9481 8388608   1024            4
PRUEBA07_IDX1 INDEX                6       4    10505 8388608   1024            4
PRUEBA07_IDX1 INDEX                7       4    11529 8388608   1024            4
PRUEBA07_IDX1 INDEX                8       4    12553 8388608   1024            4
PRUEBA07_IDX1 INDEX                9       4    13577 1048576    128            4

10 filas seleccionadas.

carlosal@db01.xxxxxx> drop index PRUEBA07_IDX1;

Índice borrado.

carlosal@db01.xxxxxx> CREATE INDEX PRUEBA07_IDX1
  2  ON PRUEBA07(OWNER, OBJECT_NAME, CREATED)
  3  STORAGE( INITIAL 76554240) -- 73M + 8K!!
  4  TABLESPACE TBS1
  5   /

Índice creado.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2         SEGMENT_TYPE,
  3         EXTENT_ID,
  4         FILE_ID,
  5         BLOCK_ID,
  6         BYTES,
  7         BLOCKS,
  8         RELATIVE_FNO
  9    FROM DBA_EXTENTS
 10   WHERE OWNER='CARLOSAL'
 11     AND SEGMENT_NAME='PRUEBA07_IDX1'
 12  /

SEGMENT_NAME  SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------- ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07_IDX1 INDEX                0       4     4361 8388608   1024            4
PRUEBA07_IDX1 INDEX                1       4     5385 8388608   1024            4
PRUEBA07_IDX1 INDEX                2       4     6409 8388608   1024            4
PRUEBA07_IDX1 INDEX                3       4     7433 8388608   1024            4
PRUEBA07_IDX1 INDEX                4       4     8457 8388608   1024            4
PRUEBA07_IDX1 INDEX                5       4     9481 8388608   1024            4
PRUEBA07_IDX1 INDEX                6       4    10505 8388608   1024            4
PRUEBA07_IDX1 INDEX                7       4    11529 8388608   1024            4
PRUEBA07_IDX1 INDEX                8       4    12553 8388608   1024            4
PRUEBA07_IDX1 INDEX                9       4    13577 8388608   1024            4

10 filas seleccionadas.

carlosal@db01.xxxxxx>

En resumen: dejando a un lado el mito de ‘todo en un extent’, Oracle parece evitar los tramos más pequeños a la hora de reservar espacio si utilizamos parámetros de almacenamiento (‘INITIAL’) en la creación de segmentos.

Por último: la curiosidad de utilizar un ‘extent’ de 1Mb para satisfacer los requisitos ‘INITIAL’, no supone que se sigan utilizando ‘extents’ de 1Mb para almacenar subsiguientes necesidades de espacio. Si se necesita más espacio, se utilizarán de nuevo ‘extents’ de 8Mb, dejando una ‘isla’ de un ‘extent’ de 1Mb para seguir creciendo en ‘extents’ de 8Mb.

Para verlo, (y de paso probar que con tablas el comportamiento descrito hasta ahora es idéntico) recrearemos la tabla PRUEBA07 de manera que ocupe más de los 65Mb solicitados en el ‘INITIAL’ y observaremos qué ocurre:

carlosal@db01.xxxxxx> CREATE TABLE PRUEBA07
  2            STORAGE( INITIAL 65m)
  3            TABLESPACE TBS1
  4            AS SELECT a.*, b.* FROM DBA_OBJECTS a, PRUEBA01 b, PRUEBA02 c
  5   /

Tabla creada.

carlosal@db01.xxxxxx> SELECT SEGMENT_NAME,
  2  SEGMENT_TYPE,
  3  EXTENT_ID,
  4  FILE_ID,
  5  BLOCK_ID,
  6  BYTES,
  7  BLOCKS,
  8  RELATIVE_FNO
  9  FROM DBA_EXTENTS
 10  WHERE OWNER='CARLOSAL'
 11  AND SEGMENT_NAME='PRUEBA07'
 12  /

SEGMENT_NAME SEGMENT_TYPE EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
------------ ------------ --------- ------- -------- ------- ------ ------------
PRUEBA07     TABLE                0       4     2185 8388608   1024            4
PRUEBA07     TABLE                1       4     3209 8388608   1024            4
PRUEBA07     TABLE                2       4     4233 8388608   1024            4
PRUEBA07     TABLE                3       4     5257 8388608   1024            4
PRUEBA07     TABLE                4       4     6281 8388608   1024            4
PRUEBA07     TABLE                5       4     7305 8388608   1024            4
PRUEBA07     TABLE                6       4     8329 8388608   1024            4
PRUEBA07     TABLE                7       4     9353 8388608   1024            4
PRUEBA07     TABLE                8       4    10377 1048576    128            4
PRUEBA07     TABLE                9       4    10505 8388608   1024            4
PRUEBA07     TABLE               10       4    11529 8388608   1024            4
PRUEBA07     TABLE               11       4    12553 8388608   1024            4
PRUEBA07     TABLE               12       4    13577 8388608   1024            4
PRUEBA07     TABLE               13       4    14601 8388608   1024            4
PRUEBA07     TABLE               14       4    15625 8388608   1024            4
PRUEBA07     TABLE               15       4    16649 8388608   1024            4
PRUEBA07     TABLE               16       4    17673 8388608   1024            4
PRUEBA07     TABLE               17       4    18697 8388608   1024            4
PRUEBA07     TABLE               18       4    19721 8388608   1024            4
PRUEBA07     TABLE               19       4    20745 8388608   1024            4
PRUEBA07     TABLE               20       4    21769 8388608   1024            4
PRUEBA07     TABLE               21       4    22793 8388608   1024            4

22 filas seleccionadas.

carlosal@db01.xxxxxx>

El ‘extent’ número 8 es de 1Mb (para satisfacer el ‘INITIAL’), pero los siguientes vuelven a ser de 8Mb.

Este comportamiento es un poco ‘particular’, pero es como Oracle gestiona los ‘extents’ para LMT’s de tipo ‘SYSTEM MANAGED’ con cláusulas de almacenamiento.

Saludos.

Carlos.

Índices y ‘FOREIGN KEYs’

Mucha gente aun no es muy consciente de los problemas que pueden provocarse en una base de datos cuando existen tablas con ‘foreign keys’ que no tengan índices asociados.

Este es un asunto que se ha repetido una y mil veces en diferentes foros, pero que sigue apareciendo en multitud de bases de datos.

Para resumir: si tenemos tablas que tienen ‘foreign keys’ y éstas no están indexadas, aparte de los problemas de rendimiento (gran cantidad de las ‘joins’ entre las tablas se harán basándose en las columnas correspondientes a las ‘foreign keys’), aparecerán problemas de concurrencias y bloqueos que degradarán enormemente el rendimiento de la actividad de actualizaciones de la base de datos.

Los SGBDR más conocidos (al menos los que yo conozco: Oracle, SQL Server, Sybase…) no fuerzan la creación de índices sobre las ‘foreign keys’, pero recomiendan encarecidamente su uso. El propio Oracle Designer genera automáticamente los índices para las ‘foreign keys’ en sus herramientas de diseño.

El ‘quid’ de la cuestión (o más castizo: la madre del cordero) está en que cualquier actualización sobre la tabla ‘padre’ deberá bloquear TODA la tabla ‘hija’ si no hay un índice sobre las columnas que corresponden a la ‘foreign key’. Esto es necesario para asegurar la integridad.

Vamos con un ejemplo para ver el comportamiento descrito:

carlos@db01.xxxxxxxx> SELECT * FROM V$VERSION
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> SELECT * FROM PRUEBA01 ORDER BY ID_N
  2  /

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS
        11 ONCE
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO
        30 TREINTA

13 filas seleccionadas.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> CREATE TABLE PRUEBA04 AS
  2                 SELECT ID_N,
  3                        LOWER(C_TXT) C_TXT
  4                   FROM PRUEBA01 WHERE ID_N < 10
  5  /

Tabla creada.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> ALTER TABLE PRUEBA04 ADD CONSTRAINT PRUEBA04_PRUEBA01_FK
  2               FOREIGN KEY(ID_N) REFERENCES PRUEBA01(ID_N)
  3  /

Tabla modificada.

carlos@db01.xxxxxxxx>
carlos@db01.xxxxxxxx> SELECT * FROM PRUEBA04 ORDER BY ID_N
  2  /

      ID_N C_TXT
---------- -------------------------
         1 uno
         2 dos
         3 tres
         4 cuatro
         5 cinco
         6 seis

6 filas seleccionadas.

carlos@db01.xxxxxxxx>

Partiendo de nuestra vieja conocida ‘PRUEBA01’, que tiene su ‘primary key’ sobre ID_N, creamos la tabla PRUEBA04 como un subconjunto de ella.

Lo siguiente es fácil: abrimos una nueva sesión e insertamos una fila en PRUEBA04:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (11,'once')
  2  /

1 fila creada.

carlos@db01.xxxxxxxx>

Si ahora vamos a la primera sesión e intentamos, por ejemplo, borrar una fila, tendremos que la sesión se bloquea esperando el ‘COMMIT’ o ‘ROLLBACK’ en la segunda sesión:

carlos@db01.xxxxxxxx> DELETE FROM PRUEBA01 WHERE ID_N=30
  2  /
_

Nótese que estamos intentando borrar una fila en PRUEBA01 que no tiene filas ‘hijas’ en PRUEBA04. Esto es porque la primera sesión está intentando bloquear toda la tabla PRUEBA04, pero la sesión 2 tiene una transacción pendiente sobre ella.

Una vez que terminamos la transacción en la segunda sesión:

carlos@db01.xxxxxxxx> COMMIT
  2  /

Confirmación terminada.

carlos@db01.xxxxxxxx>

La primera sesión puede continuar con su transacción:

1 fila suprimida.

carlos@db01.xxxxxxxx>

Ahora vamos a reproducir el proceso, pero con un índice sobre la columna correspondiente a la FK en PRUEBA04:

carlos@db01.xxxxxxxx> CREATE INDEX PRUEBA04_IDX01 ON PRUEBA04(ID_N)
  2  /

Índice creado.

carlos@db01.xxxxxxxx>

En la sesión 2 volvemos a hacer:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (21, 'veintiuno')
  2  /

1 fila creada.
carlos@db01.xxxxxxxx>

Pero ahora la primera sessión no esperará si borramos una fila:

carlos@db01.xxxxxxxx> DELETE FROM PRUEBA01 WHERE ID_N=30
  2  /

1 fila suprimida.

carlos@db01.xxxxxxxx>

Una cosa más: en ausencia de índice, intentamos un ‘UPDATE’ sobre una fila en PRUEBA01 que no tiene ‘hijas’ en PRUEBA04 en el que actualizamos el valor del PK con su mismo valor como hacen muchas aplicaciones (que actualizan TODAS LAS COLUMNAS DE UNA FILA en cada ‘UPDATE’) o como hacen ciertos ‘modelos de persistencia’ (Hibernate creo que también lo hace en sus clases ‘mapeadas’ a tablas de la base de datos)

Primero ‘tiramos’ el índice en la primera sesión:

carlos@db01.xxxxxxxx> drop index prueba04_idx01;

Índice borrado.

La segunda sesión hace:

carlos@db01.xxxxxxxx> INSERT INTO PRUEBA04 VALUES (21, 'veintiuno')
  2  /

1 fila creada.
carlos@db01.xxxxxxxx>

Y la primera intenta:

carlos@db01.xxxxxxxx> UPDATE PRUEBA01 SET ID_N = ID_N WHERE ID_N = 30
2 /
_

Como esperábamos, se queda esperando al fin de la transacción de la segunda sesión, aunque no tenga filas ‘hijas’ y el valor actualizado tenga el mismo valor que tenía.

Si imaginamos una base de datos con miles de transacciones actualizando datos sobre tablas con FK’s sin índices asociados podremos darnos cuenta de las consecuencias sobre el rendimiento que ello puede producir.

Después de esto, espero que si alguien decide seguir manteniendo FK’s sin índices asociados, tenga poderosas razones para hacerlo.

Saludos.

Carlos.

Segment Space Management y High Watermark en Oracle 10g

Cualquiera que haya trabajado con tablas que tienen gran cantidad de ‘INSERTs’ y ‘DELETEs’ -la típica base de datos ‘OLTP’- habrá podido observar que el High Watermark (HWM) es algo que hay que tener muy en cuenta. Cuando el acceso a una tabla se hace por medio de un ‘FULL SCAN’ Oracle lee todos los bloques que están por debajo de dicho ‘HWM’. El típico caso de una tabla de la cual se han se han borrado una gran cantidad de filas es el ejemplo más claro. No importa cuantas filas puede tener en el momento en el que hacemos la consulta, si en el pasado llegó a ser muy grande (y por tanto su HWM es muy alto) en el caso de un ‘FULL SCAN’ Oracle leerá todos y cada uno de los bloques que alguna vez fueron ocupados por la tabla.

Cualquier DBA sabe que esto se puede corregir de una de las dos siguientes maneras:

·Reconstruyendo la tabla ‘desde cero’. (Con una tabla auxuliar, TRUNCATE, etc…)
·Haciendo un ALTER TABLE … MOVE

Pero Oracle 10g incorpora una nueva funcionalidad que alivia la tarea de compactar los datos y bajar el HWM mediante las cláusulas ENABLE ROW MOVEMENT, SHRINK SPACE COMPACT y SHRINK SPACE.

Veamos como:

Primero crearemos una tabla que ocupe varios ‘extents’. Luego borraremos un gran número de filas y veremos cómo la ocupación del segmento de la tabla no varía.

carlos@bd01.xxxxxxxx> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

carlos@bd01.xxxxxxxx> CREATE TABLE PRUEBA09 AS
  2  SELECT OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  3    FROM ALL_OBJECTS
  4  /

Tabla creada.

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME     BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- --------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1               458752         56          7          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- --------- ---------- ----------
PRUEBA09     TABLE        TBS1                    0      65536          8
PRUEBA09     TABLE        TBS1                    1      65536          8
PRUEBA09     TABLE        TBS1                    2      65536          8
PRUEBA09     TABLE        TBS1                    3      65536          8
PRUEBA09     TABLE        TBS1                    4      65536          8
PRUEBA09     TABLE        TBS1                    5      65536          8
PRUEBA09     TABLE        TBS1                    6      65536          8

7 filas seleccionadas.

Como vemos, la tabla ocupa 56 bloques (‘blocks’) en siete ‘extents’. Esto fija su HWM.

El ‘tablespace’ es un LMT gestionado por el sistema:

carlos@bd01.xxxxxxxx> SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TBS1'
  2  /

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
--------------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
TBS1                  8192          65536                       1  2147483645                65536 ONLINE    PERMANENT LOGGING   NO  LOCAL         SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO

Vamos a seleccionar una fila, que nos servirá luego para verificar cierto aspecto del proceso (Hay que fijarse en el ROWID de la fila).

carlos@bd01.xxxxxxxx> SELECT ROWID, OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  2    FROM PRUEBA09
  3   WHERE OBJECT_ID = 5000
  4  /

ROWID               OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
------------------ ---------- ------------------------------ -------------------
AAAKJPAAEAAAAg5AB3       5000 USER_BASE_TABLE_MVIEWS         SYNONYM

Ahora procedemos a borrar una gran cantidad de las filas:

carlos@bd01.xxxxxxxx> DELETE FROM PRUEBA09
  2   WHERE MOD(OBJECT_ID,10) != 0
  3  /

9138 filas suprimidas.

carlos@bd01.xxxxxxxx> COMMIT
  2  /

Confirmación terminada.

carlos@bd01.xxxxxxxx>

Verificamos que el segmento de la tabla sigue ocupando lo mismo que antes del borrado:

carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE       TABLESPACE_NAME                 EXTENT_ID      BYTES     BLOCKS
------------ ------------------ ------------------------------ ---------- ---------- ----------
PRUEBA09     TABLE              TBS1                                    0      65536          8
PRUEBA09     TABLE              TBS1                                    1      65536          8
PRUEBA09     TABLE              TBS1                                    2      65536          8
PRUEBA09     TABLE              TBS1                                    3      65536          8
PRUEBA09     TABLE              TBS1                                    4      65536          8
PRUEBA09     TABLE              TBS1                                    5      65536          8
PRUEBA09     TABLE              TBS1                                    6      65536          8

7 filas seleccionadas.

Ahora utilizamos la nueva técnica de Oracle 10g:

Primero hay que habilitar el ‘row movement’ para la tabla:

carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 ENABLE ROW MOVEMENT
  2  /

Tabla modificada.

Luego, como primera fase, efectuamos el ‘shrink space compact’. Esto compactará los bloques: las filas se agruparán en los bloques más ‘bajos’ llenándolos completamente y dejando vacíos los más ‘altos’, pero NO BAJARÁ el HWM:

carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 SHRINK SPACE COMPACT
  2  /

Tabla modificada.

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1                458752         56          7          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx> SELECT * FROM USER_EXTENTS WHERE SEGMENT_NAME='PRUEBA09'
  2  /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- ---------- ---------- ----------
PRUEBA09     TABLE        TBS1                     0      65536          8
PRUEBA09     TABLE        TBS1                     1      65536          8
PRUEBA09     TABLE        TBS1                     2      65536          8
PRUEBA09     TABLE        TBS1                     3      65536          8
PRUEBA09     TABLE        TBS1                     4      65536          8
PRUEBA09     TABLE        TBS1                     5      65536          8
PRUEBA09     TABLE        TBS1                     6      65536          8

7 filas seleccionadas.

Lo que ha ocurrido es que los bloques ocupados se han compactado, dejando los bloques libres en los ‘extents’ más ‘altos’ justo por debajo del HWM. Se ve así que el segmento de la tabla no ha variado ‘cuantitativamente’, aunque sí ‘cualitativamente’.

Ahora viene el asunto del HWM. Esto lo hacemos con:

carlos@bd01.xxxxxxxx> ALTER TABLE PRUEBA09 SHRINK SPACE
  2  /

Tabla modificada.

La verdad es que aquí Oracle podría haber elegido otra sintaxix un poco más clara, ya que ‘SHRINK SPACE’ y ‘SHRINK SPACE COMPACT’ pueden dar pie a confusión.
En cualquier caso, ahora vemos los resultados:

carlos@bd01.xxxxxxxx> SELECT * FROM USER_SEGMENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME      BYTES     BLOCKS    EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE  FREELISTS FREELIST_GROUPS BUFFER_
------------ ------------ --------------- ---------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------------- -------
PRUEBA09     TABLE        TBS1                 65536          8          1          65536                       1  2147483645                                         DEFAULT

carlos@bd01.xxxxxxxx>  SELECT * FROM USER_EXTENTS
  2 WHERE SEGMENT_NAME='PRUEBA09'
  3 /

SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME  EXTENT_ID      BYTES     BLOCKS
------------ ------------ --------------- ---------- ---------- ----------
PRUEBA09     TABLE        TBS1                     0      65536          8

Ahora sí tenemos los bloques ‘compactados’ y además el HWM ha bajado. (Sólo hay un ‘extent’, donde antes había siete). Los ‘FULL SCANs’ serán ahora más efectivos para esta tabla.

Por último veamos la ‘SELECT’ sobre el objeto 5000:

carlos@bd01.xxxxxxxx> SELECT ROWID, OBJECT_ID, OBJECT_NAME, OBJECT_TYPE
  2    FROM PRUEBA09
  3   WHERE OBJECT_ID = 5000
  4  /

ROWID               OBJECT_ID OBJECT_NAME                    OBJECT_TYPE
------------------ ---------- ------------------------------ -------------------
AAAKJPAAEAAAAgmAC2       5000 USER_BASE_TABLE_MVIEWS         SYNONYM

carlos@bd01.xxxxxxxx>

Vemos que su ROWID ha cambiado (lo cual es lógico, dada la naturaleza del ROWID). Esto no deja de ser otra razón más en contra de utilizar el ROWID como identificador único e inmutable de una fila.

Nota: Esta funcionalidad sólo está disponible en segmentos que estén ubicados en ‘tablespaces’ con ‘ASSM’ (automatic segment storage management – mantenimiento automático de almacenamiento de segmento).

Saludos.

Carlos.

Vistas actualizables: WITH CHECK OPTION

La utilización de vistas es un mecanismo muy útil si se quiere filtrar, preparar o incluso encapsular la información que existe en las tablas subyacentes.

Las vistas pueden suponer un método fácil de implementar un grado de abstracción sobre cierta parte de los datos que existen en las tablas. La creación de vistas actualizables que ‘compartimenten’ los datos es algo fácil de hacer, pero sobre lo que hay que tener unas mínimas precauciones.

Veamos un caso: Tenemos una tabla y queremos implementar una regla de negocio que filtre los datos según cierto criterio.

SQL*Plus: Release 10.2.0.1.0 - Production on Mié Abr 11 12:08:57 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@bd01.xxxxxxxx
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning and Data Mining options

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

      ID_N C_TXT
---------- -------------------------
        30 TREINTA
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO

11 filas seleccionadas.

Un método sencillo es crear una vista que filtre los datos según el criterio requerido:

carlos@bd01.xxxxxxxx> CREATE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N <= 10;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO

Hasta aquí todo fácil. Podemos insertar filas en esa vista sin ningún problema y los datos se almacenarán en la tabla subyacente:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (6, 'SEIS');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

      ID_N C_TXT
---------- -------------------------
        30 TREINTA
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO
         6 SEIS

12 filas seleccionadas.

Pero aquí pueden empezar los problemas, porque a través de la vista se podrían hacer modificaciones a la tabla de una manera descontrolada:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (11,'ONCE');

1 fila creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS

6 filas seleccionadas.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01;

      ID_N C_TXT
---------- -------------------------
        30 TREINTA
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
        21 VEINTIUNO
        22 VEINTIDOS
        23 VEINTITRES
        24 VEINTICUATRO
        25 VEINTICINCO
         6 SEIS
        11 ONCE

13 filas seleccionadas.

Aquí hemos visto que se puede hacer un ‘INSERT’ ‘a ciegas’: Hemos insertado en la vista una fila que no podemos ver, pero que sí fue insertada en la tabla base. Esto da pie a que puedan hacerse operaciones que pasen inadvertidas.

Incluso más: se pueden dar errores incomprensibles:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO');
INSERT INTO PRUEBA01_VW VALUES (21,'VEINTIUNO')
*
ERROR en línea 1:
ORA-00001: unique constraint (CARLOS.PRUEBA01_PK) violated

En este caso y en el anterior (la inserción ‘a ciegas’ y el error) puede ocurrir que el nombre de la vista no nos dé pistas de que no se trata de una tabla ‘ordinaria’, haciendo el comportamiento más extraño a ojos de un observador.

Estos problemas se solicionarán fácilmente si utilizamos la opción ‘WITH CHECK OPTION’ en la creación de la vista. Esta opción hace que las operaciones DML sobre dicha vista pasen un control de integridad que corresponda a los criterios de definición de la propia vista:

carlos@bd01.xxxxxxxx> CREATE OR REPLACE VIEW PRUEBA01_VW AS
2> SELECT ID_N, C_TXT FROM PRUEBA01
3> WHERE ID_N  <= 10
4> WITH CHECK OPTION;

Vista creada.

carlos@bd01.xxxxxxxx> SELECT * FROM PRUEBA01_VW;

      ID_N C_TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO
         6 SEIS

6 filas seleccionadas.

Si ahora intentamos hacer una inserción (o modificación) que salga de los criterios de definición de la vista tendremos que:

carlos@bd01.xxxxxxxx> INSERT INTO PRUEBA01_VW VALUES (12,'DOCE');
INSERT INTO PRUEBA01_VW VALUES (12,'DOCE')
            *
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>

Por supuesto, esto también es válido para ‘UPDATE’:

carlos@bd01.xxxxxxxx> UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1;
UPDATE PRUEBA01_VW SET ID_N=50 WHERE ID_N=1
       *
ERROR en línea 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

carlos@bd01.xxxxxxxx>

En resumen, si se utilizan vistas actualizables como mecanismo de ‘encapsulamiento de visibilidad de datos’ se debería siempre considerar la opción ‘WITH CHECK OPTION’ para evitar resultados no deseados de inconsistencia y reforzar además las referidas políticas de visibilidad (y más importante: de actualización) de los datos.

Saludos.

Carlos.

Writers block readers…

Una de las diferencias más notables entre SQL Server y Oracle es el comportamiento con los bloqueos y los modelos de concurrencia y niveles de aislamiento (isolation levels).

Lo que nunca nos dejará de sorprender a los acostumbrados a trabajar con Oracle es un comportamiento como este (el servidor es SQL Server 2005):

En una primera sesión abierta con SQLCMD hacemos:

1> SELECT * FROM PRUEBA01;
2> GO
id_n        c_txt
----------- --------------------------------------------------
          1 UNO
          2 DOS
          3 THREE

En una segunda sesión abierta con SQLCMD hacemos:

1> BEGIN TRANSACTION;
2>     UPDATE PRUEBA01
3>        SET C_TXT = 'TRES'
4>      WHERE ID_N = 3;
5>     WAITFOR DELAY '00:02';
6> COMMIT TRANSACTION;
7> GO
_

Y de vuelta a la primera sesión, vemos que se queda esperando hasta que la segunda sesión termine su transacción antes de poder efectuar un sencillo y simple ‘SELECT’:

1> SELECT * FROM PRUEBA01;
2> GO
_

Sólo cuando la segunda sesión libera el bloqueo (mediante el COMMIT TRANSACTION) tenemos:

1> SELECT * FROM PRUEBA01;
2> GO
id_n        c_txt
----------- --------------------------------------------------
          1 UNO
          2 DOS
          3 TRES

(3 filas afectadas)

Por otra parte, incluso los ‘readers’ pueden bloquear a los ‘writers’, por lo que es frecuente ver ‘SELECTs’ del tipo:

'SELECT * FROM PRUEBA01 WITH(NOLOCK)'

No obstante, aunque este es el comportamiento normal, SQL Server 2005 implementa dos nuevos niveles de aislamiento:

Read Committed Isolation (Statement-Level Read Consistency) Es similar al ‘READ COMMITED’ de Oracle (que es su funcionamiento por defecto). Según la documentación, se eliminan los bloqueos sobre las filas modificadas y los ‘writers’ no bloquean a los ‘readers’ y viceversa.

Snapshot Isolation (Transaction-Level Read Consistency) Esto es parecido al modo ‘READ ONLY’ de las transacciones Oracle: los datos son vistos tal y como estaban al principio de la transacción, sin tener en cuenta ninguna modificación que otros usuarios pudieran haber realizado en el transcurso de la misma.

Ambos son implementados utilizando un mecanismo de versionado (row versioning) de datos. La diferencia está en que donde Oracle utiliza los segmentos de ‘rollback’ para obtener los estados consistentes de los datos, SQL Server genera copias de las versiones de los datos en ‘TEMPDB’ y las consulta cuando lo necesita, en vez de ir al dato ‘real’. Esto hace que TEMPDB’ deba ser mucho más grande de lo habitual, con lo que se pueden encontrar problemas de rendimiento con la I/O.

Así:

1> ALTER DATABASE db01
2>    SET READ_COMMITTED_SNAPSHOT ON;
3> go
1> BEGIN TRANSACTION;
2>     UPDATE PRUEBA01
3>        SET C_TXT = 'TRES'
4>      WHERE ID_N = 3;
5>     WAITFOR DELAY '00:02';
6> COMMIT TRANSACTION;
_

Y ahora la otra sesión no espera a que termine la transacción, sino que devuelve los resultados inmediatamente y ‘a la Oracle’ (esto es, en modo ‘read commited’: tal y como están antes de producirse el ‘commit’):

1> SELECT * FROM PRUEBA01;
2> GO
id_n        c_txt
----------- --------------------------------------------------
          1 UNO
          2 DOS
          3 THREE

(3 filas afectadas)
1>

Y una vez finaliza la transacción:

1> SELECT * FROM PRUEBA01;
2> GO
id_n        c_txt
----------- --------------------------------------------------
          1 UNO
          2 DOS
          3 TRES

(3 filas afectadas)
1>

Saludos.

Carlos.