Operadores de Conjunto (caso práctico)

En la compañía, se está desarrollando una aplicación para un cliente. Ésta sirve para "clonar" esquemas dentro de una base de datos, con la flexibilidad de decidir los objetos, campos, constraints, etc. que quieres llevarte.

Una problemática que se presentó, fue la siguiente:

¿Cómo saber qué constraints se pueden replicar con una tabla que no se migra con todas las columnas?

Suponiendo que tenemos una tabla original con 3 campos, y la tabla destino, sólo contiene dos campos. Imaginemos un constraint de tipo "check" que valida que el (campo1 = 'true') or (campo2 = 'false') or (campo3 = 'lo que sea'). No podemos replicar ese constraint al nuevo esquema, ya que marcaría un error. Así que ¿cómo pudiéramos revisar, basado en nuestras columnas seleccionadas, los constraints que se pueden migrar sin problema?

La solución se puede lograr fácilmente con lógica de conjuntos

WITH registros AS
  (SELECT --+materialize
  constraint_name,
     column_name
   FROM dba_cons_columns
   WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
SELECT constraint_name,
       COUNT(1) numero
  FROM registros
 WHERE column_name IN('GEOG_CRS_DATUM_ID',
                      'SOURCE_GEOG_SRID')
 GROUP BY constraint_name 
INTERSECT
SELECT constraint_name,
       COUNT(1)
  FROM registros
 GROUP BY constraint_name;

Primero explicaré brevemente sobre la clausula WITH.

La clausula with, que nace con Oracle 9i Release 1, en lugar de repetir código complejo, o pesado, nos permite darle un nombre, y reusar la referencia a través del nombre múltiples veces dentro de una sentencia SQL. Forma parte del estándar SQL-99. Jonathan Lewis, adicionalmente incluye el hint de "materialize" en las cláusulas with, ya que de esta forma, se materializa el resultado en una tabla temporal y al seleccionarse en múltiples partes, el resultado materializado se accede de forma más rápida.

En este ejemplo, como sé que voy a trabajar con los constraints de una sola tabla, mi universo de datos son todos los registros contenidos en "dba_cons_columns" para la tabla en cuestión. De ahí el with

WITH registros AS
  (SELECT --+materialize
  constraint_name,
     column_name
   FROM dba_cons_columns
   WHERE TABLE_NAME = 'NOMBRE_TABLA')

De ahí, necesito obtener los constraints que incluyan mis columnas que voy a replicar, o por lo menos alguna de las columnas. Añado un "count" para saber cuántas de mis columnas son incluidas en el constraint.

 SELECT constraint_name,
       COUNT(1) numero
  FROM registros
 WHERE column_name IN('CAMPOS',
                      'A',
                      'BUSCAR')
 GROUP BY constraint_name 

Finalmente para terminar, necesito saber cuántas columnas en total tiene cada constraint.

SELECT constraint_name,
       COUNT(1)
  FROM registros
 GROUP BY constraint_name

Teniendo esos sets de datos, usamos lógica de conjuntos y hacemos una intersección con la palabra "INTERSECT"

SQL> WITH registros AS
  2    (SELECT --+materialize
  3    constraint_name,
  4       column_name
  5     FROM dba_cons_columns
  6     WHERE TABLE_NAME = 'SDO_COORD_REF_SYS')
  7  SELECT constraint_name,
  8         COUNT(1) numero
  9    FROM registros
 10   WHERE column_name IN('GEOG_CRS_DATUM_ID',
 11                        'SOURCE_GEOG_SRID')
 12   GROUP BY constraint_name
 13  INTERSECT
 14  SELECT constraint_name,
 15         COUNT(1)
 16    FROM registros
 17   GROUP BY constraint_name;

CONSTRAINT_NAME                    NUMERO
------------------------------ ----------
COORD_REF_SYS_FOREIGN_GEOG              1

SQL>

Esto nos da el resultado que si voy a incluir sólo las columnas 'GEOG_CRS_DATUM_ID', 'SOURCE_GEOG_SRID', sólo puedo llevarme el contraint COORD_REF_SYS_FOREIGN_GEOG.

Pasando ya de lleno a la lógica de conjuntos, Oracle cuenta con tres operadores de conjuntos

UNION [ALL]
MINUS
INTERSECT

Se pueden usar para unir varios queries y llevar a cabo la operación de conjunto deseada, llevan orden de precedencia a menos que vengan entre paréntesis.

SQL> SELECT *
  2  FROM
  3    (SELECT 1 FROM dual
  4     UNION ALL
  5     SELECT 1 FROM dual)
  6  INTERSECT
  7    (SELECT 1 FROM dual
  8     UNION ALL
  9     SELECT 2 FROM dual)
 10  ;

         1
----------
         1

Las expresiones correspondientes en las sentencias select, deben de coincidir en número y tipo, si no se sigue esto, oracle genera un error.

Hay algunas observaciones por considerar.

No se pueden usar operadores de conjuntos en columnas de tipo BLOB, CLOB, BFILE, VARRAY, o "nested tables".

No son válidos en columnas de tipo long.

Los operadores MINUS, UNION e INTERSECT terminan agrupando los resultados. Union All no agrupa y sólo junta los resultados de las dos fuentes de datos.

Y la más importante de todas las consideraciones es que Oracle para apegarse a un estándar, cambiará (no sé si el release 11g ya lo tenga) la precedencia del comando INTERSECT para que se evalue primero, por esta razón, Oracle recomienda siempre usar paréntesis cuando se use "INTERSECT".