T1.1: Designs a SQL Schema

¿Qué es un Índice de Extensión Bitmap?

Los índices de extensión bitmap son índices especializados que InterSystems IRIS utiliza para optimizar operaciones a nivel de tabla, particularmente consultas COUNT(*). Cuando creas una tabla usando CREATE TABLE, InterSystems IRIS define automáticamente un índice de extensión bitmap con el nombre SQL MapName %%DDLBEIndex. Este índice mantiene una representación bitmap comprimida de qué filas existen en la tabla, permitiendo que la base de datos determine rápidamente los conteos de filas sin escanear toda la tabla.

Cómo Funciona

El índice de extensión bitmap rastrea la extensión de la tabla misma - el conjunto completo de filas que existen en la tabla. El índice usa posiciones de bits para representar IDs de filas, con cada bit indicando si una fila existe (1) o no (0). A diferencia de los índices regulares que rastrean valores específicos de columnas, el índice de extensión bitmap rastrea solo la existencia.

Beneficios de Rendimiento

El índice de extensión bitmap es particularmente valioso para tablas grandes donde contar filas requeriría de otro modo un escaneo completo de la tabla. Las consultas COUNT(*) pueden resolverse completamente desde el índice sin acceder a los datos de la tabla.

Restricciones y Limitaciones

• Uno por tabla: Máximo de un índice de extensión bitmap permitido por tabla
• SQLCODE -400: Intentar crear múltiples índices de extensión bitmap resulta en este error
• No se crea automáticamente cuando:
• La tabla es una tabla temporal global
• La tabla define un índice IDKEY explícito
• La tabla contiene una columna IDENTITY con MINVAL diferente de 1
• La opción de configuración del sistema DDLDefineBitmapExtent está deshabilitada

Requisitos de RowID

El índice de extensión bitmap funciona solo con:

• Tablas que usan valores RowID asignados por el sistema con enteros positivos
• Tablas que usan una clave primaria IDKEY basada en %Integer con MINVAL > 0
• Tablas que usan una clave primaria IDKEY basada en %Numeric con SCALE = 0 y MINVAL > 0

Esta restricción asegura que el bitmap pueda mapear eficientemente las posiciones de bits a identificadores de filas.

Equilibrando Rendimiento y Mantenimiento

Determinar casos de uso apropiados para índices requiere equilibrar los beneficios de rendimiento de consultas contra los costos de mantenimiento. Los índices mejoran dramáticamente el rendimiento de consultas al proporcionar rutas de acceso rápido a los datos, pero cada índice agrega sobrecarga a las operaciones INSERT, UPDATE y DELETE porque InterSystems IRIS debe mantener cada índice cuando los datos cambian.

Principios de Selectividad

Los índices más efectivos se crean en columnas con alta selectividad - lo que significa columnas que tienen muchos valores distintos en relación con el número total de filas.

• Ejemplos de alta selectividad: ID de Empleado, Número de Seguro Social, Número de Pedido
• Ejemplos de baja selectividad: Género (M/F), Códigos de estado con pocos valores
• Recomendación: Usar índices estándar para alta selectividad; considerar índices bitmap para baja selectividad

Identificando Candidatos para Índices

Analiza los patrones de consulta de tu aplicación para identificar candidatos para índices:

• Cláusulas WHERE: Las columnas usadas frecuentemente en predicados son candidatos principales
• Operaciones JOIN: Tanto la columna de unión como la columna referenciada se benefician de índices
• ORDER BY/GROUP BY: Los índices pueden eliminar operaciones de ordenamiento costosas
• Evitar sobre-indexación: Crear índices en cada columna desperdicia recursos y degrada el rendimiento de escritura

Índices Compuestos y de Cobertura

Considera índices compuestos cuando las consultas filtran frecuentemente en múltiples columnas juntas:

• El orden de las columnas importa: Coloca la columna más selectiva primero, seguida por columnas menos selectivas
• Cláusula WITH DATA: Crea índices de cobertura que incluyen datos de columnas adicionales
• Consultas de cobertura: Pueden satisfacerse completamente desde el índice sin acceder al mapa de datos maestro
• Compromiso: Aumenta el tamaño del índice y la sobrecarga de mantenimiento

Índice Estándar (Type=index)

Los índices estándar usan una estructura B-tree tradicional adecuada para columnas con muchos valores distintos.

• Mejor para: Claves primarias, claves foráneas, columnas de alta cardinalidad
• Soporta: Restricciones únicas y no únicas
• Operaciones: Búsqueda eficiente, escaneo de rango y ordenamiento
• Elección predeterminada: La mayoría de escenarios de indexación

Índice Bitmap (Type=bitmap)

Los índices bitmap sobresalen al indexar columnas con un número pequeño de valores distintos (baja cardinalidad).

• Mejor para: Género, códigos de estado, datos categóricos
• Cómo funciona: Cadenas de bits comprimidas donde posición de bit = ID de fila, valor de bit = presencia/ausencia
• Eficiente para: Operaciones AND/OR en cláusulas WHERE
• Limitaciones:
• Solo debe usarse para < 10,000 valores distintos
• Requiere tablas con RowID asignado por el sistema usando enteros positivos
• No puede especificar restricciones UNIQUE

Índice Bitslice (Type=bitslice)

Los índices bitslice son altamente especializados para cálculos agregados rápidos en datos numéricos.

• Mejor para: Operaciones SUM, condiciones de rango en datos numéricos
• Cómo funciona: Representa cada valor numérico como una cadena de bits binarios
• Limitaciones:
• Rendimiento significativamente más lento de INSERT, UPDATE, DELETE
• No usado por el optimizador de consultas para filtrado de cláusulas WHERE
• No puede especificar restricciones UNIQUE

Índice Columnar (Type=columnar)

Los índices columnares permiten consultas muy rápidas en datos almacenados en columnas.

• Mejor para: Cargas de trabajo analíticas (OLAP), filtrado y agregación
• Escenario ideal: Consulta frecuente de columnas específicas a través de muchas filas
• No ideal para: Cargas de trabajo transaccionales (OLTP) que acceden a filas completas

Columna RowID

La columna RowID es una columna generada automáticamente (nombre predeterminado "ID") que contiene enteros positivos únicos y no modificables asignados por el sistema a cada registro.

• Siempre existe: Proporciona un identificador único garantizado incluso sin una clave primaria
• Inmutable: Los valores no pueden modificarse después de la creación
• Asignado por el sistema: Asignado automáticamente por InterSystems IRIS

Clave Primaria

Una clave primaria es una restricción definida por el usuario que designa una o más columnas como el identificador de registro primario de la tabla.

• Requisitos: Debe contener valores únicos, no puede aceptar NULL
• Opcional pero recomendada: Proporciona identificación significativa a nivel empresarial
• Ejemplos: ID de Empleado, Número de Pedido, SSN

Configuración DDLPKeyNotIDKey

La relación entre claves primarias y claves ID depende de la configuración del sistema DDLPKeyNotIDKey:

• DDLPKeyNotIDKey=1 (predeterminado): La clave primaria NO es la IDKEY
• Crea índice separado para unicidad
• Los valores de clave primaria PUEDEN modificarse
• DDLPKeyNotIDKey=0: La clave primaria se convierte en la IDKEY
• Acceso a datos más eficiente
• Los valores de clave primaria NO PUEDEN modificarse después de la creación
• Nota: Si existe una columna IDENTITY, la clave primaria nunca puede ser IDKEY independientemente de la configuración

Restricciones Únicas

Las restricciones únicas aplican unicidad sin designar la clave primaria.

• Múltiples permitidas: La tabla puede tener múltiples restricciones únicas pero solo una clave primaria
• Crea índice: Para aplicar unicidad
• Tablas fragmentadas: Puede impactar significativamente el rendimiento a menos que la clave de fragmento sea un subconjunto de la clave única

IDKEY

La IDKEY es el índice único a nivel del sistema usado internamente para identidad de objetos.

• Requerida: Cada clase persistente debe tener exactamente una IDKEY
• Usada para: Identidad de objetos, relaciones padre-hijo, referencias de objetos
• Lógica de respaldo: Si no hay clave primaria y RowID está oculto, usa columna IDENTITY o crea IDKEY interna

Qué Hacen las Claves Foráneas

Las claves foráneas establecen relaciones referenciales entre tablas al designar una columna (o combinación de columnas) en una tabla que referencia valores únicos en otra tabla.

• Propósito: Crear enlaces lógicos entre tablas
• Integridad de datos: Asegura que los valores referenciados realmente existan
• Previene: Registros huérfanos

Requisitos de Definición

Al definir una clave foránea:

• Nombre CONSTRAINT requerido: Necesario para futuras operaciones ALTER TABLE
• Nombres de columnas: Pueden diferir entre columnas de clave foránea y columnas referenciadas
• Tipos de datos: Deben ser compatibles
• La columna referenciada debe ser única: Clave primaria, restricción única, RowID o IDENTITY

Claves Foráneas Compuestas

Las claves foráneas soportan múltiples columnas especificando listas separadas por comas:

• Deben coincidir en número y orden
• Ejemplo: `FOREIGN KEY (CustomerNum, SalesPersonNum) REFERENCES Customers (CustID, SalespID)`
• CustomerNum → CustID
• SalesPersonNum → SalespID

Jerarquía de Referencia Predeterminada

Si omites la columna referenciada, la clave foránea predetermina a: 1. Columna de clave primaria (si está definida) 2. Columna IDENTITY (si no hay clave primaria) 3. Columna RowID (si no existe ninguna)

Nota: RowID solo puede ser referenciado si está definido como público (SqlRowIdPrivate=0 o palabra clave %PUBLICROWID)

Conversión de Propiedad de Referencia

InterSystems IRIS puede convertir claves foráneas de una sola columna en propiedades de referencia si:

• La clave foránea referencia la IDKEY de la tabla padre
• La tabla no contiene datos
• La propiedad no es ya una propiedad de referencia
• Los tipos de datos coinciden exactamente
• El campo de clave foránea no es un campo IDENTITY

Consideraciones de Tablas Fragmentadas

• Soportado: Las claves foráneas funcionan para cualquier combinación de tablas fragmentadas/no fragmentadas
• Restricción: Solo se soporta la acción referencial NO ACTION
• Privilegio requerido: REFERENCES en la tabla o columnas referenciadas

Propósito de Acciones Referenciales

Las acciones referenciales definen cómo InterSystems IRIS mantiene la consistencia de datos cuando los registros en tablas referenciadas son modificados o eliminados. Las cláusulas ON DELETE y ON UPDATE especifican qué acción tomar al cambiar o eliminar una fila referenciada por restricciones de clave foránea.

NO ACTION (Predeterminado)

NO ACTION es el predeterminado para ON DELETE y ON UPDATE.

• Comportamiento: Previene eliminación/modificación si existen referencias de clave foránea
• Resultado: La operación falla con error, preservando la integridad referencial
• Excepción: No se aplica a claves foráneas autorreferenciales

CASCADE

CASCADE propaga cambios a través de la relación.

• ON DELETE CASCADE: Elimina automáticamente todas las filas que referencian
• ON UPDATE CASCADE: Actualiza automáticamente valores de clave foránea para coincidir con la nueva clave primaria
• Advertencia: Eliminar una fila puede propagarse a muchas filas relacionadas a través de tablas

SET NULL

SET NULL rompe la relación al anular columnas de clave foránea.

• Requisito: Las columnas de clave foránea deben permitir NULL (sin restricción NOT NULL)
• Comportamiento: Mantiene filas hijas pero elimina relación padre
• Caso de uso: Cuando la relación es opcional

SET DEFAULT

SET DEFAULT reasigna registros huérfanos a un padre predeterminado.

• Comportamiento: Establece columnas de clave foránea a sus valores predeterminados
• No hay predeterminado definido: Establece a NULL
• Requisito: Debe existir una fila que coincida con el valor predeterminado en la tabla referenciada
• Caso de uso: Reasignar a padre "Desconocido" o "No Asignado"

Advertencias Importantes

• No hay claves foráneas contradictorias: No definir dos FKs en las mismas columnas con diferentes acciones
• Ejemplo: Una con ON DELETE CASCADE, otra con ON DELETE SET NULL
• IRIS no previene esto en la definición, pero ocurren errores en tiempo de ejecución
• Tablas fragmentadas: Solo NO ACTION soportado
• CASCADE, SET NULL, SET DEFAULT resultan en error SQLCODE -400

Beneficios de Rendimiento de Consultas

Agregar índices acelera dramáticamente las operaciones de consulta al proporcionar rutas de acceso directo a los datos.

• Escaneo completo de tabla: Segundos o minutos
• Búsqueda por índice: Milisegundos
• Beneficio: Mejora de órdenes de magnitud para consultas selectivas

Costos de Rendimiento de Escritura

Cada operación de modificación de datos debe actualizar la tabla base Y cada índice.

• INSERT: Debe agregar entradas a todos los índices aplicables
• UPDATE: Debe actualizar entradas de índice para columnas indexadas modificadas
• DELETE: Debe eliminar entradas de todos los índices
• Impacto: Múltiples índices degradan significativamente el rendimiento de escritura en aplicaciones con muchas transacciones

Sobrecarga de Almacenamiento

Cada índice requiere espacio en disco para almacenar la estructura del índice y los datos.

• Índices estándar: Almacenan valores de columnas indexadas y punteros de filas
• Cláusula WITH DATA: Almacena datos de columnas adicionales (más grande pero permite consultas de cobertura)
• Índices bitmap: Usan cadenas de bits comprimidas (eficiente pero puede ser sustancial para tablas grandes)

Complejidad de Mantenimiento

Los índices introducen requisitos operacionales:

• Corrupción/ineficiencia de índice: Puede requerir reconstrucción usando BUILD INDEX o %BuildIndices()
• Cambios de intercalación: Requieren recompilar clases y reconstruir todos los índices
• Creación inicial: Construir datos de índice puede llevar tiempo para tablas grandes
• Múltiples índices: Multiplican todos los requisitos de mantenimiento

Estrategia Óptima de Indexación

Equilibra compromisos indexando selectivamente:

• SÍ indexar: Columnas usadas frecuentemente en WHERE, JOIN, ORDER BY
• NO indexar: Cada columna "por si acaso" - desperdicia recursos
• Columnas de baja selectividad: Usar índices bitmap en lugar de índices estándar
• Monitorear e iterar: Agregar índices basados en patrones de uso reales, no especulación
• Usar planes de consulta: Identificar consultas que se beneficiarían de nuevos índices

¿Qué son los Índices Compuestos?

Los índices compuestos (también llamados índices multi-columna o concatenados) son índices definidos en dos o más columnas.

• Sintaxis: `CREATE INDEX idx ON Table (col1, col2, col3)`
• Punto clave: El orden de las columnas es significativo y afecta el rendimiento

Regla de Izquierda a Derecha

El optimizador de consultas sigue la regla "de izquierda a derecha" para utilización de índices compuestos.

Para índice (A, B, C):

• PUEDE usar eficientemente: Consultas filtrando en (A), (A,B), o (A,B,C)
• NO PUEDE usar eficientemente: Consultas filtrando solo en (B), (C), o (B,C)

Principio de diseño: Poner columna más selectiva (mayor cardinalidad) primero, luego columnas menos selectivas.

Consultas Multi-Condición

Los índices compuestos sobresalen para consultas con múltiples condiciones de filtro AND.

• Consulta de ejemplo: `WHERE State='CA' AND City='San Francisco'`
• Beneficio de índice: Índice en (State, City) proporciona acceso directo a filas coincidentes
• Sin índice: La consulta usa índice de una sola columna en State, luego filtra por City

Optimización de GROUP BY

Las operaciones GROUP BY se benefician cuando las columnas coinciden con el orden del índice.

• Índice (State, City): Permite agrupación eficiente solo por State o por State+City
• Beneficio: Los datos pre-ordenados eliminan operaciones de ordenamiento costosas

Índices de Cobertura con WITH DATA

La cláusula WITH DATA incluye columnas adicionales en la estructura del índice.

• Sintaxis: `CREATE INDEX idx ON Orders (CustomerID, OrderDate) WITH DATA (OrderTotal)`
• Beneficio: Las consultas que seleccionan CustomerID, OrderDate y OrderTotal se satisfacen completamente desde el índice
• Impacto: Reduce dramáticamente I/O al evitar acceso a la tabla base
• Compromiso: Aumenta tamaño de índice y sobrecarga de mantenimiento
• Usar para: Consultas ejecutadas frecuentemente en datos estables

Cuándo Crear Índices Compuestos

Considera índices compuestos cuando:

• Múltiples columnas se consultan frecuentemente juntas
• Las consultas combinan múltiples condiciones de filtro con AND
• GROUP BY u ORDER BY opera en múltiples columnas
• Existen oportunidades de índices de cobertura para consultas críticas

Advertencia: Evita índices compuestos excesivos - consumen almacenamiento y ralentizan escrituras.