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".
