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.
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 INDEXLa 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
CLUSTERpara 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
NONCLUSTEREDdentro 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.
