Guía de migración de Oracle a SQL Server y de SQL Server a Oracle - Pt. 2

La primera parte de esta serie discutió las diferencias entre Oracle Database y Microsoft SQL Server en su implementación de transacciones, con un enfoque en las trampas que uno puede encontrar durante las migraciones de Oracle a SQL Server y viceversa. Esta próxima entrega cubrirá una serie de elementos de sintaxis SQL de uso común que no coinciden o tienen un significado o uso completamente diferente en la división Oracle-SQL Server.

Secuencias en Oracle y columnas de identidad en SQL Server

Existe una división de larga data en la comunidad de bases de datos entre dos campos: los patriotas de las claves naturales y los defensores de las claves artificiales (o "sustitutas").

Yo mismo defiendo las claves naturales, pero a menudo me encuentro creando sustitutos por una u otra razón. Pero dejando a un lado la sustancia de este debate, veamos los mecanismos estándar para generar claves artificiales: secuencias de Oracle y columnas de identidad de SQL Server.

Una secuencia de Oracle es un objeto de nivel de base de datos de primera clase. Por el contrario, una columna de identidad de SQL Server es un tipo de columna, no un objeto.

Cuando se usa una secuencia de Oracle para generar una clave de tabla, generalmente una clave principal, se garantiza que se incrementará y, por lo tanto, será única. Pero no se garantiza que sea consecutivo. De hecho, incluso en implementaciones bien diseñadas, lo más probable es que haya algunas lagunas. Por lo tanto, ninguna implementación de Oracle debería confiar en que los valores generados por secuencias sean consecutivos.

Además, una secuencia se administra a través del diccionario de datos de una base de datos de Oracle, por lo que sería demasiado costoso (y engorroso) crear una secuencia dedicada para admitir todas y cada una de las claves sustitutas. Un objeto de secuencia única puede admitir varias o incluso todas las claves sustitutas.

Por otro lado, cuando varios procesos necesitan acceder a NEXTVAL (el siguiente valor incremental) de una secuencia, la secuencia se convertirá en un recurso crítico de acceso único. Efectivamente, hará que todos los procesos que accedan a él sean estrictamente secuenciales, convirtiendo cualquier implementación de varios subprocesos (único o multiservidor) en un proceso de un solo subproceso, con largos tiempos de espera y uso elevado de memoria y bajo uso de CPU.

Tales implementaciones realmente suceden. La solución para este problema es definir el objeto de secuencia en cuestión con un valor de caché razonable, lo que significa que se selecciona un rango definido de valores (ya sea 100 o 100 mil) en un caché para un proceso de llamada, registrado en el diccionario de datos tal como se usa , y estar disponible para este proceso en particular sin necesidad de acceder al diccionario de datos cada vez que se llama a NEXTVAL .

Pero esta es exactamente la razón por la que se crearán espacios, ya que es probable que no se utilicen todos los valores almacenados en caché. También significa que a través de múltiples procesos en sesiones paralelas, algunos valores de secuencia registrados pueden invertirse cronológicamente. Esta inversión no puede ocurrir dentro de un solo proceso a menos que un valor de secuencia se haya restablecido o retrocedido. Pero este último escenario equivale a buscar problemas: debería ser innecesario y, si se implementa incorrectamente, puede generar valores duplicados.

Por lo tanto, la única forma correcta de usar las secuencias de Oracle es para la generación de claves sustitutas: claves que son únicas pero que no se supone que contienen ninguna otra información utilizable de manera confiable.

Columnas de identidad en SQL Server

¿Qué pasa con el servidor SQL? Si bien en SQL Server 2012 se introdujeron secuencias con una funcionalidad e implementación muy similares a las de Oracle, no son una técnica de primer nivel. Al igual que otras funciones adicionales, tienen sentido para la conversión desde Oracle, pero cuando se implementan claves sustitutas desde cero en SQL Server, IDENTITY es una opción mucho mejor.

IDENTITY es un objeto "hijo" de una tabla. No accede a recursos fuera de una tabla y se garantiza que sea secuencial a menos que se manipule deliberadamente. Y está diseñado específicamente para esta misma tarea, en lugar de compatibilidad semántica con Oracle.

Dado que Oracle implementó la funcionalidad IDENTITY en la versión 12.1, es natural preguntarse cómo se las arregló antes sin ella, por qué la implementó ahora y por qué SQL Server la necesitó desde el principio (desde sus orígenes de Sybase SQL Server).

La razón es que Oracle siempre tuvo una característica clave de identidad: la pseudocolumna ROWID , que tiene un tipo de datos de ROWID o UROWID . Este valor no es numérico ( ROWID y UROWID son tipos de datos patentados de Oracle) e identifica de forma exclusiva un registro de datos.

A diferencia de IDENTITY de SQL Server, el ROWID de Oracle no se puede manipular fácilmente (se puede consultar, pero no insertar ni modificar) y se crea en segundo plano para cada fila de cada tabla de Oracle. Además, la forma más eficiente de acceder a cualquier fila de datos en una base de datos de Oracle es mediante su ROWID , por lo que se utiliza como una técnica de optimización del rendimiento. Por último, define el orden de clasificación de salida de consulta predeterminado, ya que indexa de manera efectiva el almacenamiento de bajo nivel de datos de fila.

Si el ROWID de Oracle es tan importante, ¿cómo sobrevivió SQL Server todos esos años y lanzamientos sin él? Mediante el uso de columnas IDENTITY como claves primarias (sustitutas).

Es importante tener en cuenta la diferencia en la implementación de la estructura del índice entre Oracle y SQL Server.

En SQL Server, el primer índice, la clave principal, la mayoría de las veces, está agrupado; esto significa que, por lo general, los datos del archivo de datos principal se ordenan según esta clave. En el lado de Oracle, el equivalente de un índice agrupado es una tabla organizada por índice. Esta es una construcción opcional en Oracle que se usa esporádicamente, solo cuando es necesario, por ejemplo, para tablas de búsqueda de solo lectura.

Todos los patrones de diseño en Oracle que se basan en el uso de ROWID (como la deduplicación de datos) deben implementarse en función de las columnas IDENTITY al migrar a SQL Server.

Si bien la migración del uso de IDENTITY en SQL Server al uso de IDENTITY en Oracle podría producir un código funcionalmente correcto, no es óptimo, porque en el lado de Oracle, ROWID funcionará de manera mucho más eficiente.

Lo mismo ocurre cuando se realiza una conversión de sintaxis SQL simple para mover secuencias de Oracle a SQL Server: el código se ejecutará, pero usar IDENTITY es la opción preferida en términos de simplicidad y rendimiento del código.

Índices filtrados en Microsoft SQL Server

Hace años, Microsoft SQL Server 2008 introdujo una serie de características importantes que lo convirtieron en una base de datos empresarial verdaderamente de primer nivel. Uno que me ha salvado el día más de una vez ha sido el de los índices filtrados.

Un índice filtrado es un índice no agrupado (es decir, uno que existe como su propio archivo de datos) que tiene una cláusula WHERE . Significa que el archivo de índice contiene solo registros de datos relevantes para la cláusula. Para aprovechar al máximo los índices filtrados, también debe tener una cláusula INCLUDE que enumere todas las columnas que se necesitan al devolver un conjunto de datos. Cuando su consulta está optimizada para usar un índice filtrado específico que incluye todos los puntos de datos necesarios, el motor de la base de datos solo necesita acceder a un archivo de índice (pequeño) sin siquiera mirar el archivo de datos de la tabla principal.

Esto fue particularmente valioso para mí hace unos años cuando trabajaba con una tabla del tamaño de un terabyte. El cliente en cuestión frecuentemente necesitaba acceder solo a una fracción de un porcentaje de los registros activos en un momento dado. La implementación inicial de este acceso (activada por las acciones de la interfaz de usuario del usuario final) no solo fue terriblemente lenta, sino simplemente inutilizable. Cuando agregué un índice filtrado con los INCLUDE necesarios, se convirtió en una búsqueda de submilisegundos. El tiempo que dediqué a esta tarea de optimización fue solo una hora.

Claro, los índices filtrados tienen algunas limitaciones. No pueden incluir columnas LOB, hay límites sobre qué condiciones pueden incluir las cláusulas WHERE que los índices mismos pueden incluir, y se suman a la huella de almacenamiento de una base de datos. Pero siempre que un caso de uso se ajuste a estos parámetros, las compensaciones de almacenamiento suelen ser bastante menores en comparación con el aumento significativo del rendimiento que pueden proporcionar los índices filtrados.

¿Qué pasa con los índices filtrados en Oracle Database?

Más tarde me encontré en un gran equipo en una empresa Fortune 500 como desarrollador/DBA en un proyecto de migración de SQL Server a Oracle. El código que rodeaba la base de datos de origen, SQL Server 2008, se implementó de manera deficiente, con un rendimiento escaso que hizo que la conversión fuera imperativa: el trabajo diario de sincronización de back-end se ejecutaba durante más de 23 horas. No tenía índices filtrados, pero en el nuevo sistema Oracle 11g, vi varios casos en los que los índices filtrados serían muy beneficiosos. ¡Pero Oracle 11g no tiene índices filtrados!

Tampoco se implementan índices filtrados en el último Oracle 18c.

Pero nuestra tarea como profesionales técnicos es hacer el mejor uso de lo que tenemos. Así que implementé el equivalente de índices filtrados en mi sistema Oracle 11g (y la misma técnica que usé más tarde en 12c). La idea se basa en cómo Oracle maneja NULL y se puede usar en cualquier versión de Oracle.

Oracle no trata un valor NULL de la misma manera que los datos regulares. Un NULL en Oracle no es nada, no existe. Como resultado, si define su columna indexada como NULLABLE y está buscando por valores que no sean NULL , su archivo de datos de índice solo contendrá registros de interés. Como una definición de índice de Oracle no tiene una cláusula INCLUDE , deberá crear un índice compuesto con todas las columnas que deben incluirse en un conjunto de resultados. (Esta técnica tiene cierta sobrecarga en comparación con la cláusula INCLUDE de SQL Server, pero es razonablemente insignificante).

Esta implementación alternativa agrega una limitación: la columna de índice principal debe permitir valores NULL y, por lo tanto, no puede ser la clave principal de la tabla. Sin embargo, puede ser una columna derivada o calculada creada específicamente para admitir este método de optimización del rendimiento. En cierto sentido, la columna inicial del índice es lógicamente binaria: valores no NULL para los datos incluidos en su búsqueda y NULL para cualquier dato que deba ser "invisible".

La otra opción posible para migrar la lógica de índice filtrado de SQL Server a Oracle es implementar un índice (o la tabla completa) como particionado. En este caso, el motor de la base de datos solo accederá a la partición de índice relevante, siempre que las consultas se implementen correctamente mediante el uso de la condición de partición exacta en sus cláusulas WHERE .

Esto funcionará bien, incluso a escala, en datos relativamente estáticos, pero puede suponer una gran carga de mantenimiento para un equipo de DBA si se aplica a datos que cambian con frecuencia. Un ejemplo sería al optimizar el acceso a los datos actuales en una aplicación centrada en el tiempo: el equipo de DBA necesitará redefinir las particiones diariamente. Si bien esta redefinición se puede programar en un trabajo de mantenimiento nocturno, hace que su sistema sea más complejo e introduce nuevos puntos potenciales de falla sistémica.

Por lo tanto, se debe ser muy específico y cuidadoso siempre que la lógica de índice filtrado de SQL Server deba migrarse a Oracle.

Cómo manejar las conversiones

Con una migración de Oracle a SQL Server, busque oportunidades de optimización utilizando índices filtrados. No verá índices filtrados en Oracle, pero puede ver índices que incluyen valores NULL . No los copie tal cual: puede ser el mejor lugar donde puede obtener un aumento del rendimiento y una mejora del diseño en su conversión.

Para las migraciones de SQL Server a Oracle, si ve índices filtrados, busque cómo evitar un cuello de botella de rendimiento en su código de Oracle correspondiente. Vea cómo puede rediseñar el flujo de datos para compensar el aumento de rendimiento faltante que los índices filtrados habían proporcionado en la implementación de origen.

Desafíos de migración de SQL Server a Oracle / Oracle a SQL Server Desmitificados

Para proyectos de migración entre Oracle y SQL Server en cualquier dirección, es importante tener una comprensión más profunda de la mecánica involucrada. Cuando las versiones actuales de las respectivas bases de datos (Oracle 18c y Microsoft SQL Server 2017*) contienen equivalentes léxicos de la funcionalidad de cada uno, por ejemplo, en secuencias e identidad, puede parecer una victoria fácil. Pero copiar un buen diseño en un RDBMS directamente al otro puede resultar en un código innecesariamente complicado y de bajo rendimiento.

En la siguiente y última parte de esta serie, cubro la coherencia de lectura y el uso de herramientas de migración. ¡Manténganse al tanto!

* SQL Server 2019 (o "15.x") no ha estado disponible el tiempo suficiente para una adopción empresarial generalizada.