de Martin LE TARNEC.

Tablas ordenadas según el índice (del inglés, Index-Organized Tables) y agrupaciones de índices


El escaneado limitado al índice ejecuta una sentencia SQL usando solamente los datos redundantes almacenados dentro del índice. El dato original dentro del heap de la tabla no es necesario. Si se parte de este concepto para la siguiente etapa y se ponen todas las columnas dentro del índice, se podría cuestionar para qué se necesita el heap de la tabla.

De hecho, algunas bases de datos pueden utilizar un índice como tabla primaria de almacenamiento. La base de datos Oracle llama a este concepto tabla ordenada según el índice (IOT). Otras bases de datos usan la expresión índice agrupado. En esta sección, se usan ambos términos para destacar la importancia de las características de la tabla o del índice según sea necesario.

Una tabla ordenada según el índice es un índice B-tree sin heap de la tabla. Eso tiene dos ventajas: (1) se ahorra el espacio de la estructura del heap; (2) cada acceso sobre un índice agrupado es automáticamente un único escaneo el índice. Ambas ventajas suenan prometedoras pero son muy difíciles de poner en práctica.

Las desventajas de una tabla ordenada según el índice se producen cuando se crea otro índice sobre la misma tabla. Para hacer una analogía con un índice habitual, un denominado índice secundario hace referencia a los datos de la tabla original, que son almacenadas dentro del índice agrupado. Ahí, los datos no se almacenan estáticamente como en el heap de la tabla pero pueden ser desplazados en cualquier momento para mantener el orden del índice. Por tal motivo, no es posible almacenar la ubicación física de las filas dentro de la tabla ordenada según el índice en el índice secundario. En su lugar, la base de datos debe utilizar una clave lógica.

El siguiente gráfico muestra una búsqueda LOOKUP sobre un índice para hallar todas las ventas del 23 de mayo de 2012. En comparación, primero veremos la Figura 5.2 que muestra el proceso cuando se usa el heap de la tabla. La ejecución involucra dos etapas: (1) un INDEX RANGE SCAN; (2) un TABLE ACCESS BY INDEX ROWID.

Figura 5.2 Acceso basado en índice sobre el heap de la tabla

Aunque el acceso a la tabla podría llegar a ser un cuello de botella, se limita a una operación de lectura por fila porque el índice tiene el ROWID que tiene un puntero directo hacia la fila de la tabla. La base de datos puede cargar de inmediato la fila desde el heap de la tabla porque el índice tiene su posición exacta. Sin embargo, la gráfica cambia cuando se utiliza un índice secundario sobre una tabla ordenada según el índice. Un índice secundario no almacena un puntero físico (ROWID) sino que almacena solamente los valores de las claves del índice agrupado; la denominada clave de agrupación. Generalmente es la primera clave de una tabla ordenada según el índice.

¿Porqué el índice secundario no tiene ROWID?

Un puntero directo hacia la fila de la tabla podría ser conveniente para el índice secundario. Pero eso es posible sólo si las filas de la tabla permanecen en una ubicación fija del almacenamiento. Desafortunadamente, no es posible si las filas son parte de una estructura de un índice que los conserva en orden. Y conservar el orden de un índice implica mover las filas de forma ocasional. Eso es también efectivo para operaciones que no afectan a la propia fila. Por ejemplo, el comando insert podría dividir un nodo hoja para ahorrarse espacio para la nueva entrada. Eso significa que algunas entradas se desplazan hacia un nuevo bloque en una ubicación diferente.

Por otra parte, el heap de la tabla no conserva las filas en ningún orden. La base de datos guarda las nuevas entradas donde se encuentra suficiente espacio. Una vez escrito en el disco, los datos no se mueven de los heap de las tablas.

Tener acceso a un índice secundario no genera un ROWID, pero sí una clave lógica para buscar dentro del índice agrupado. Sin embargo, un acceso único es insuficiente para buscar sobre el índice agrupado; se requiere una lectura completa del árbol. Eso significa que tener acceso a la tabla a través de un índice secundario implica buscar sobre dos índices: una vez sobre el índice secundario (INDEX RANGE SCAN), y después sobre el índice agrupado por cada fila encontrada dentro del índice secundario (INDEX UNIQUE SCAN).

Figura 5.3 Índice secundario sobre una IOT

La Figura 5.3 lo deja claro: el B-tree del índice agrupado permanece entre el índice secundario y los datos de la tabla.

Tener acceso a una tabla ordenada según el índice a través de un índice secundario resulta muy ineficiente y se puede ahorrar de la misma manera un acceso a la tabla sobre el heap de la tabla; usando un escaneado limitado al índice; en este caso mejor descrito como un escaneado limitado al índice secundario. El beneficio de rendimiento de un escaneado limitado al índice es aun mayor porque no sólo evita un único acceso sino también un INDEX UNIQUE SCAN entero.

Importante

Tener acceso a una tabla ordenada según el índice a través de un índice secundario es muy ineficiente.

Siguiendo este ejemplo, se puede observar que las bases de datos explotan todas las redundancias que tienen. Se ha de tener en cuenta que el índice secundario almacena la clave de agrupación por cada entrada del índice. En consecuencia, se puede seleccionar la clave de agrupación de un índice secundario sin tener acceso a la tabla ordenada según el índice:

SELECT sale_id
  FROM sales_iot
 WHERE sale_date = ?
-------------------------------------------------
| Id | Operation        | Name           | Cost |
-------------------------------------------------
|  0 | SELECT STATEMENT |                |    4 |
|* 1 |  INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
-------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)

La tabla SALES_IOT es una tabla ordenada según el índice que usa SALE_ID como clave de agrupación. Aunque el índice SALE_IOT_DATE se refiere únicamente a la columna SALE_DATE, tiene todavía una copia de la clave de agrupación SALE_ID, así que se puede cubrir la sentencia usando solamente el índice secundario.

Cuando se seleccionan las otras columnas, la base de datos tiene que ejecutar un INDEX UNIQUE SCAN sobre el índice agrupado por cada fila:

SELECT eur_value
  FROM sales_iot
 WHERE sale_date = ?
---------------------------------------------------
| Id  | Operation         | Name           | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT  |                |   13 |
|*  1 |  INDEX UNIQUE SCAN| SALES_IOT_PK   |   13 |
|*  2 |   INDEX RANGE SCAN| SALES_IOT_DATE |    4 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SALE_DATE"=:DT)
   2 - access("SALE_DATE"=:DT)

Las tablas organizadas como índice y los índices agrupados no son, después de todo, muy útiles como podría parecer a primera vista. Las ventajas de rendimiento sobre el índice agrupado se pierden fácilmente cuando se usa un índice secundario. La clave de agrupación suele ser más larga que un ROWID, así que los índices secundarios son más grandes de lo que podría ser el heap de la tabla, lo que generalmente neutraliza el ahorro de haber quitado el heap de la tabla. La ventaja de las tablas organizadas como índice y de los índices agrupados está limitada a las tablas que no necesitan un segundo índice. El heap de las tablas tiene el beneficio de proveer una copia maestra estática que puede ser fácilmente referenciada.

Importante

Las tablas con un único índice se implementan mejor como índices agrupados o tablas organizadas como índice.

Las tablas con más índices se pueden generalmente beneficiar del heap de las tablas. Todavía se puede utilizar un escaneado limitado al índice para evitar el acceso a la tabla. Eso nos da un rendimiento del select de un índice agrupado sin la lentitud de los otros índices.

El soporte de la base de datos para las tablas organizadas como índice y los índices agrupados es muy incoherente. La siguiente perspectiva general explica los detalles más importantes.

DB2

DB2 no tiene tablas organizadas como índice pero utiliza el termino "índice agrupado" para una característica diferente. Utiliza el heap de la tabla pero intenta insertar las nuevas filas dentro del mismo bloque tales como las filas dentro del índice.

MySQL

El motor MyISAM usa solamente el heap de las tablas a pesar de que el motor InnoDB emplea siempre los índices agrupados. Eso significa que no existe la posibilidad de escoger.

Oracle

La base de datos Oracle usa el heap de las tablas por defecto. Las tablas organizadas como índice (IOT) pueden crearse usando la cláusula ORGANIZATION INDEX:

CREATE TABLE (
   id    NUMBER NOT NULL PRIMARY KEY,
   [...]
) ORGANIZATION INDEX

La base de datos Oracle usa siempre la clave primaria como clave de agrupación.

PostgreSQL

PostgreSQL usa solamente el heap de las tablas.

Sin embargo, se puede usar la cláusula CLUSTER para ordenar los contenidos del heap de la tabla según un índice.

SQL Server

Por defecto, SQL Server usa los índices agrupados (tablas organizadas como índice) usando la clave primaria como clave de agrupación. Sin embargo, se pueden utilizar de forma arbitraria columnas para la clave de agrupación; incluso columnas no únicas.

Para crear el heap de una tabla, se debe emplear la cláusula NONCLUSTERED dentro de la primera declaración:

CREATE TABLE (
   id    NUMBER NOT NULL,
   [...]
   CONSTRAINT pk PRIMARY KEY NONCLUSTERED (id)
)

Elimina un índice agrupado cambia la tabla a un heap de la tabla.

El comportamiento por defecto de SQL Server causa generalmente problemas de rendimiento cuando se tiene acceso a los índices secundarios.

Si te gusta mi manera de explicar, te encantará mi libro.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia