martes, 30 de septiembre de 2008

Funciones determinísticas y Group by vs. Distinct(2)

Recientemente un comentario sobre la entrada de Group by vs. Distinct, me hizo dudar un poco sobre lo que había escrito en relación a group by vs. distinct, pero a la vez me obligó a investigar un poco más, y estas son mis observaciones.

Intentando plasmar lo que se tiene en el comentario, tenemos lo siguiente:

Comenzamos creando una tabla


SQL> CREATE TABLE valores(valor NUMBER(1))
2 TABLESPACE users;

Tabla creada.


Una vez que se tiene la tabla, insertamos 100 registros con sólo 10 valores distintos


SQL> INSERT
2 INTO valores
3 SELECT MOD(rownum, 10)
4 FROM dba_objects
5 WHERE rownum < 101;

100 filas creadas.



Cramos una función "tardada" para probar los ejemplos


CREATE OR REPLACE FUNCTION tardada(numero IN NUMBER)
RETURN NUMBER IS salida NUMBER;
BEGIN
SELECT COUNT(1)
INTO salida
FROM dba_tables
WHERE MOD(rownum, 10) = numero;
RETURN salida;
END;
/


Primero vamos a evaluar el tiempo y estadísticas sin agrupar la información


SQL> set autot traceonly stat
SQL> set timing on

SQL> SELECT tardada(valor)
2 FROM valores;

100 filas seleccionadas.

Transcurrido: 00:00:05.67

Estadísticas
----------------------------------------------------------
100 recursive calls
246414 consistent gets
100 sorts (memory)
100 rows processed


Se procesó la información en más de 5 segundos consumiendo 246,000 bloques. Se procesan 100 registros, y se hacen 100 sorts

Si lo hacemos con un distinct, estas son las estadísticas

SQL> SELECT DISTINCT tardada(valor)
2 FROM valores;

Transcurrido: 00:00:05.68

Estadísticas
----------------------------------------------------------
100 recursive calls
246407 consistent gets
100 sorts (memory)
2 rows processed


Como vemos, se usan prácticamente los mismos recursos, pero al final nos regresa sólamente 2 registros. La función se ejecuta 100 veces.

Ahora lo intentamos hacer con un group by:


SQL> SELECT tardada(valor)
2 FROM valores
3 GROUP BY tardada(valor);

Transcurrido: 00:00:05.76

Estadísticas
----------------------------------------------------------
100 recursive calls
246407 consistent gets
100 sorts (memory)
2 rows processed


En este caso son las mismas estadísticas que con el distinct. En el siguiente ejemplo, que fue el que me llegó a confundir, se tiene lo siguiente:


SQL> SELECT tardada(valor)
2 FROM valores
3 GROUP BY valor;

10 filas seleccionadas.

Transcurrido: 00:00:00.65

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
10 rows processed


El tiempo de ejecución fue menor a un segundo, la lectura de bloques se redujo a un 10% (24,000) bloques. El resultado nos puede llevar a la conclusión de que de esta forma, sólo se evalúa la función 10 veces debido a que sólo hay 10 valores distintos y no las 100 veces que son los registros totales de la tabla. Esto es correcto de cierta forma, es decir, la función se evalúa 10 veces porque al agrupar por "valor", nos quedamos con 10 registros a evaluar en la función. Aparentemente no hay forma de reescribir de forma simple este ejemplo de group by con un distinct, pero creo que se podría hacer de la siguiente forma:


SQL> SELECT tardada(valor)
2 FROM
3 (SELECT DISTINCT valor valor
4 FROM valores)
5 ;

10 filas seleccionadas.

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
10 rows processed


Como se puede ver, se tiene el mismo consumo de recursos que en la primera situación, sin embargo creo que son dos cosas distintas los primeros ejemplos a estos últimos, ya que los últimos ejemplos, primero agrupan los registros y luego evalúan la función, es por eso que regresan 10 registros; los primeros ejemplos, evalúan la función y después agrupan, por esto, sólo regresan 2 registros.

Para poder comprobar lo que digo, voy a generar una función que siempre regrese un valor distinto:


SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> CREATE OR REPLACE FUNCTION rapida(numero IN NUMBER) RETURN NUMBER IS salida NUMBER;
2 BEGIN
3 SELECT secuencia.nextval
4 INTO salida
5 FROM dual;
6 RETURN salida;
7 END;
8 /

Función creada.


Si nosotros ejecutamos los primeros ejemplos del distinct o del group by, las respuestas serán similares a la siguiente:



SQL> SELECT DISTINCT rapida(valor)
2 FROM valores;

RAPIDA(VALOR)
-------------
1
22
25
30
34
42
43
51
54
57
...
82
92
98

100 filas seleccionadas.

SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
2 FROM valores
3 GROUP BY rapida(valor);

RAPIDA(VALOR)
-------------
1
22
25
30
34
42
43
51
54
57
...
82
92
98

100 filas seleccionadas.


Con esto, vemos que se comportan igual el distinct y el group by de mis primeros ejemplos, e incluso podemos observar que la función hash que se utiliza para evaluar duplicados es la misma, ya que regresa en el mismo orden los registros.

Ahora vamos con el ejemplo que ejecuta solamente 10 veces la función:


SQL> DROP sequence secuencia;

Secuencia borrada.

SQL> CREATE sequence secuencia START WITH 1;

Secuencia creada.

SQL> SELECT rapida(valor)
2 FROM valores
3 GROUP BY valor;

RAPIDA(VALOR)
-------------
1
2
3
4
5
6
7
8
9
10

10 filas seleccionadas.



Esto nos ayuda a entender que la agrupación se realiza antes de entrar a la función hash del group by o del distinct, y pudiera ser que no obtengamos el resultado deseado (aunque es un resultado muy válido, simplemente hay que cuidar el sentido).

Ahora, esto nos pone a pensar en que si tenemos una función que siempre que se introduzca un valor se obtendrá el mismo resultado, oracle debería de ser capaz de optimizar nuestro query para ejecutar menos veces la función.

Es aquí donde se tiene la función determinística:


SQL> CREATE OR REPLACE FUNCTION tardada2(numero IN NUMBER) RETURN NUMBER
2 DETERMINISTIC
3 IS salida NUMBER;
4 BEGIN
5 SELECT COUNT(1)
6 INTO salida
7 FROM dba_tables
8 WHERE MOD(rownum, 10) = numero;
9 RETURN salida;
10 END;
11 /

Función creada.


Y ahora probamos nuestros ejemplos iniciales


SQL> SELECT DISTINCT tardada2(valor)
2 FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
2 rows processed

SQL> SELECT tardada2(valor)
2 FROM valores
3 GROUP BY tardada2(valor);

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
10 recursive calls
24647 consistent gets
10 sorts (memory)
2 rows processed



Como se puede observar, tanto en el distinct como en el group by, oracle puede hacer uso de un "cache" de resultados de funciones.

Ya como último ejemplo, si una función no es determinística, Oracle puede usar un caché. Es algo que alguna vez vi en un simposio de de Hotsos. Desconozco en su totalidad la forma de evaluar si usa o no un cache, pero el ejemplo es el siguiente:


SQL> SELECT DISTINCT
2 (SELECT tardada(valor)
3 FROM dual)
4 FROM valores;

Transcurrido: 00:00:00.64

Estadísticas
----------------------------------------------------------
58 recursive calls
24680 consistent gets
10 sorts (memory)
2 rows processed


Como se puede ver, la llamada a la función se mete dentro de un query, y con eso, oracle puede hacer uso del cache. Hay un poco más de llamadas recursivas, quizá porque oracle debe determinar si puede o no hacer uso del cache de funciones.

Así que de todo esto, me quedo con la misma impresión que en el fondo, group by y distinct son prácticamente iguales, pero lo que sí es importante, es el definir correctamente nuestras funciones, si sabemos que es una función determinística, crearla como tal, en caso de que no, pues simplemente omitir "DETERMINISTIC".

viernes, 12 de septiembre de 2008

Hints Globales (Global Hints)

Como se puede ver en mi entrada de Mi primer tuning, desde hace aproximadamente 10 años he usado hints en Oracle.

Para los que nunca han usado un hint, no son más que instrucciones adicionales al optimizador de costos, que permiten cambiar un plan de ejecución. En pocas palabras, los hints nos permiten decidir en lugar del optimizador.

Existen diferentes tipos de Hints, oracle los suele categorizar de la siguiente forma:

* Single Table (Index, full)
* Multi Table (use_nl, use_hash, leading)
* Query Block (unnest, nomerge)
* Statement (all_rows, first_rows(n))

Pero en este caso me voy a enfocar a una subcategoria, los "Global Hints".

En mi ejemplo de la vida real, me encontré con sentencias SQL similares a las siguientes


SELECT MIN(CLP.CAMPO_FECHA) FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);

SELECT MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);



Estas sentencias SQL tenían un pésimo plan de ejecución, ya que la vista "XX_VISTA_1", contenía un inline view, y oracle decidía combinar el inline view con el outer query en una simple sentencia SQL (esto no siempre es una buena idea).

Existían en realidad unas 6 sentencias SQL similares con el mismo problema.

Tras revisar el código de la vista, me di cuenta de que eso era lo que Oracle estaba realizando, y la solución era sencilla... Cambiar el código de la vista para que no hiciera el merge del inline view:



CREATE OR REPLACE FORCE VIEW "APPS"."XX_VISTA_1"
("ROW_ID", CAMPO_FECHA, ...) AS
SELECT /*+ NO_MERGE(msi) */
pdc.ROWID row_id,
msi.FECHA campo_fecha,
...


La realidad es que esta es una de las peores soluciones y realmente no fue lo que recomendé, pero fue lo que el cliente decidió hacer como primer intento.

Internamente para ellos era más fácil modificar 1 vista que modificar 6 programas.

Lo que sucedió fue que a partir de la modificación de la vista, hubo sentencias SQL como la siguiente


SELECT DECODE (:a1,NULL, 'Normal',(
SELECT SECUENCIA_DSP FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER =
(SELECT MAX (CAMPO_NUMBER)
FROM APPS.XX_VISTA_1
WHERE CAMPO_NUMBER_2 = :a2
AND CAMPO_TEXTO = 'Active'
AND CAMPO_TEXTO_2
IN ('P', 'O', 'I', 'F')))
) FROM DUAL;


Que al cambiar el código en la vista empezó a tener un desempeño muy pobre, cambiando su plan de ejecución a varios Full Table Scans.

Entonces, si el código de la vista no debe de ser cambiado, ¿qué es lo que se puede usar?

Global Hints

Los hints que se suelen usar en select, updates y deletes, generalmente van relacionados a una tabla que existe dentro del mismo query block. Y no a tablas relacionadas con vistas internas.

La solución son los Global Hints, es decir, se puede usar un Global Hint para decirle al optimizador de costos cómo esolver una vista que existe en nuestro query block.

En mi ejemplo, podemos decirle a la vista que no haga el merge del inline view desde el query principal, y de esta forma evitar la modificación de la vista.


SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA) FROM
XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);

SELECT /*+ NO_MERGE(CIR.msi) */ MIN(CLP.CAMPO_FECHA_2)
FROM XX_VISTA_1 CIR, XX_TABLA_1 CLP
WHERE CIR.CAMPO_TEXTO_2 = :b1
AND CIR.CAMPO_NUMBER = CLP.CAMPO_NUMBER
AND CLP.CAMPO_FECHA IS NOT NULL
AND CIR.SECUENCIA = NVL(:b2,CIR.SECUENCIA);



De esta forma se estabilizaron los planes de ejecución de 6 sentencias SQL y no se afectó el desempeño de las demás sentencias que hacían uso de la vista.


En el manual de Oracle de performance tuning, vienen dos ejemplos muy claros sobre el uso de global hints

Ejemplo 1


CREATE OR REPLACE VIEW v AS
SELECT
e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal
FROM employees e1,
( SELECT *
FROM employees e3) e2, job_history j
WHERE e1.employee_id = e2.manager_id
AND e1.employee_id = j.employee_id
AND e1.hire_date = j.start_date
AND e1.salary = ( SELECT
max(e2.salary)
FROM employees e2
WHERE e2.department_id = e1.department_id)
GROUP BY e1.first_name, e1.last_name, j.job_id
ORDER BY total_sal;

SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ *
FROM v;

SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ *
FROM v;

SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ *
FROM v;


Ejemplo 2


CREATE OR REPLACE VIEW v1 AS
SELECT *
FROM employees
WHERE employee_id < 150;

CREATE OR REPLACE VIEW v2 AS
SELECT v1.employee_id employee_id, departments.department_id department_id
FROM v1, departments
WHERE v1.department_id = departments.department_id;

SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk)
FULL(v2.departments) */ *
FROM v2
WHERE department_id = 30;

miércoles, 10 de septiembre de 2008

Roles en Oracle

Generalmente cuando uno toma el curso de Workshop I de 10g, y llega a la parte de Roles (lección 6), se habla muy poco de cómo se pueden manejar los roles, e incluso no hay un ejemplo claro del mismo.

Las veces que me toca dar el curso a mi, me gusta explicar a fondo lo más que puedo acerca de roles.

Se nos dice que un rol, es asignado por default a un usuario. Un rol, puede ser asegurado de manera adicional, que por default, no trae seguridad alguna.

Entonces, partiendo de la teoría, un rol puede ser creado con la siguiente seguridad:

* Ninguna (Default)
* Password
* External
* Global

Por falta de hardware/software, no puedo demostrar la seguridad Global, pero las demás sí.

Empecemos por crear un usuario, solo para no perder la costumbre, crearé un usuario identificado a nivel sistema operativo.


C:\>sqlplus "/ AS SYSDBA"

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> CREATE USER "OPS$HUGO-WIN\HUGO" IDENTIFIED externally;

Usuario creado.

SQL> GRANT CONNECT TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.



Ahora nos conectamos como el usuario creado, identificado de manera externa, y validamos el primer rol asignado "CONNECT"


SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT



Aquí validamos dos cosas, la primera es que el rol de create session viene implícito en el connect, ya que de otra forma no hubieramos podido crear la sesión; y lo segundo es que el rol asignado, está activo por default.

Ahora crearemos un rol identificado por un password


SQL> conn / as sysdba
Conectado.

SQL> CREATE role dba_pass IDENTIFIED BY supersecreto;

Rol creado.

SQL> GRANT DBA TO dba_pass;

Concesión terminada correctamente.

SQL> GRANT dba_pass TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.


Ya que se tiene asignado al usuario el nuevo rol, nos conectamos como el usuario


SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT
DBA_PASS
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

17 filas seleccionadas.


Como vemos, el rol está asignado por default, y no nos pide el password nunca, lo que debemos hacer si es que queremos que el usuario use el password, es quitarlo del default del usuario.


SQL> conn / as sysdba
Conectado.

SQL> ALTER USER "OPS$HUGO-WIN\HUGO"
2 DEFAULT ROLE ALL EXCEPT dba_pass;

Usuario modificado.

SQL> conn /
Conectado.

SQL> show user
USER es "OPS$HUGO-WIN\HUGO"
SQL> select * from session_roles;

ROLE
------------------------------
CONNECT



Ahora intentamos usar el rol, validando que se requiere un password.


SQL> show user
USER es "OPS$HUGO-WIN\HUGO"

SQL> SET role dba_pass;
SET role dba_pass
*
ERROR en línea 1:
ORA-01979: falta la contraseña para el rol 'DBA_PASS' o no es válida


SQL> SET role dba_pass IDENTIFIED BY supersecreto;

Rol definido.

SQL> SELECT COUNT(1)
2 FROM v$session;

COUNT(1)
----------
15


Ahora crearemos un rol identificado a través de un procedimiento.

Lo primero es crear el procedimiento.


SQL> conn / as sysdba
Conectado.

SQL> CREATE OR REPLACE PROCEDURE sec_roles
2 authid CURRENT_USER AS
3 usuario VARCHAR2(50);
4 BEGIN
5 usuario := LOWER((sys_context('userenv', 'session_user')));
6 DBMS_OUTPUT.PUT_LINE(usuario);
7 IF UPPER(usuario) = 'OPS$HUGO-WIN\HUGO' THEN
8 dbms_session.set_role('DBA_PROC');
9 ELSE
10 NULL;
11 END IF;
12 END;
13 /

Procedimiento creado.


Es importante el punto de AUTHID, debe estar en current user si no, no funcionaría. Ya que se tiene el procedimiento creado, continuamos con el resto del rol y grants necesarios al usuario.



SQL> GRANT EXECUTE ON sec_roles TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.

SQL> CREATE role dba_proc IDENTIFIED USING sys.sec_roles;

Rol creado.

SQL> GRANT DBA TO dba_proc;

Concesión terminada correctamente.

SQL> GRANT EXECUTE ON sys.dbms_session TO "OPS$HUGO-WIN\HUGO";

Concesión terminada correctamente.


Es necesario que el usario que queremos que use el rol, tenga un grant al procedimiento de sys.sec_roles, y tambien permisos de ejecución en sys.dbms_session.


SQL> conn /
Conectado.

SQL> select * from session_roles;

ROLE
------------------------------
CONNECT

SQL> exec sys.sec_roles;
ops$hugo-win\hugo

Procedimiento PL/SQL terminado correctamente.

SQL> select * from session_roles;

ROLE
------------------------------
DBA_PROC
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
GATHER_SYSTEM_STATISTICS
SCHEDULER_ADMIN
WM_ADMIN_ROLE
JAVA_ADMIN
JAVA_DEPLOY
XDBADMIN
XDBWEBSERVICES
OLAP_DBA

16 filas seleccionadas.

SQL>



Hay que notar que al usuario "ops$hugo-win\hugo" jamás se le dio un grant sobre el ROL, el ROL lo obtiene con la ejecución del procedimiento.

¿Alguien tendrá un ejemplo de la autenticación global para compartir?

viernes, 5 de septiembre de 2008

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