In vs. Exists
Antes de desglosar la ejecución de IN y EXISTS para una su optimización, menciono la forma de implementarse.
IN
SELECT campo1, campo2, …, campoN
FROM tabla1
WHERE campoX IN (–subconsulta
SELECT campo1
FROM tabla2
WHERE condicion);
Cierto.
Esta consulta podría implementarse como una natural join. Se trata de un ejemplo. Oracle en ambos casos, tratándose de subconsultas simples, las interpreta como joins y resuelven el mismo plan de ejecución.
SELECT t1.campo1, t1.campo2, …, t1.campoN
FROM tabla1 t1, tabla2 t2
WHERE t1.campoX = t2.campo1
AND condicion;
Es decir, ambas consultas combinan los resultados de Tabla1 y Tabla2, unidos por las columnas t1.campoX y t2.campo1 de modo que visualicen únicamente los resultados de la tabla1 que cumplen la condición de combinación.
La interpretación de la consulta, realizada con IN, es la siguiente:
Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente para las filas cuyo campoX se encuentre en la siguiente lista de valores campo1 de la tabla2, que cumplan con la condición condicion.
La forma de resolver esta petición, para el servidor, es consultar la tabla2 en la subconsulta y delimitar después qué campos de la tabla1 cumplen la condición de join.
EXISTS
SELECT campo1, campo2, …, campoN
FROM tabla1
WHERE EXISTS (–subconsulta
SELECT NULL
FROM tabla2
WHERE t1.campoX=t2.campo1
AND condicion);
No existe en esta forma una comparación de campos, salvo en la subconsulta. Ésta devuelve NULL (ni siquiera tendría que devolver un valor concreto para ser válida) y su interpretación semántica es la siguiente:
Selecciona el campo1, campo2, … hasta campoN
de la tabla tabla1
unicamente cuando la subconsulta devuelva un registro (aunque éste sea NULL).
La subconsulta, por tanto, devolverá un registro para aquellos elementos de la tabla2 cuyo campo1 sea igual que el campoX de la tabla principal.
Por lo que Oracle entiende que la consulta más idónea a resolverse antes es la principal, resolver los valores de campoX, y posteriormente compararlos con tabla2.
Es decir, en la ejecución con EXISTS, Oracle entiende que la consulta principal debe resolverse primero, y la subconsulta después. Justo al contrario que IN, lo que puede producir diferentes planes de ejecución.
Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.
(…continuará…)
Comments
Anónimo
Muchas gracias por tus post, estoy aprendiendo mucho!!
Javier Morales
Gracias a ti, por leerme!
Si quieres que publique algo concreto, siéntete libre en pedir (muchas veces no sé qué publicar!) 🙂
¡Gracias!
Anónimo
Hola Javier, quería felicitarte por tu blog tanto por su contenido, como con la sencillez con la que expones tus artículos.
Un Saludo.
Anónimo
Hola tengo la siguiente consulta en SQL SERVER 2000 desde VISUAL .NET 2005
Dim cSql As String = «Select top 40 apell1,apell2,nom,nhc From PACIENTE WHERE apell1 NOT IN (SELECT TOP 0 apell1 From PACIENTE Order By NHC,apell1) Order By NHC»
Para que me muestre sólo 40 registros , y que no los repita
ESTO FUNCIONA EN SQL-SERVER 2000
e intento hacerlo para ORACLE 9i
Dim cSql As String = «Select apell1,apell2,nom,nhc From PACIENTE WHERE rownum =40 and apell1 NOT IN (ROWNUM=0 apell1 From PACIENTE Order By NHC,apell1) Order By NHC»
PERO ME DA ERROR EN LA CONSULTA DE ORACLE
HE PROBADO ESTO Y SI FUNCIONA
Dim cSql As String = «Select apell1,apell2,nom,nhc From PACIENTE WHERE rownum =40 Order By NHC»
¿Como podría añadir NOT IN para que no me repita los 40 registros y me muestre los 40 siguientes de la tabla ?
MUCHAS GRACIAS
Javier Morales
Hola Anónimo:
Entiendo que lo que dices que te ha funcionado es:
Select apell1,apell2,nom,nhc
From PACIENTE
WHERE rownum <=40
Order By NHC;
ya que rownum=40 no retornaría ninguna fila.
Bien. Ésto devuelve las primeras 40 filas que Oracle quiere. En tu caso, para que devuelva las primeras (por ejemplo, por orden alfabético), la consulta podría ser ésta:
Select apell1,apell2,nom,nhc
From (select * from PACIENTE order by apell1) p
WHERE rownum <=40
Order By NHC;
No obstante, para sacar los siguientes cuarenta tendrías que hacer algo tan poco profesional como ésto:
Select apell1,apell2,nom,nhc
From (select paciente.*, rownum numero_fila from PACIENTE order by apell1) p
WHERE rownum <=40
AND numero_fila not between 1 and 40
Order By NHC;
lo cual es peor aun.
Mi recomendación es que te crees tres procedimientos dentro de un paquete: uno que abra el cursor de pacientes, otro que haga fetch de 40 filas (dejando el cursor abierto para que una nueva invocación retorne las 40 siguientes, y así sucesivamente), y otro que te cierre el cursor.
Desde .NET gestionarás los resultados de forma igualmente transparente y mejorarás muchísimo en rendimiento y «limpieza» de código (=mejor mantenimiento, etc).
Suerte y un saludo!
Javier Morales
Anónimo
Hola Javier… primero que nada, felicidades por tu blog, esta muy completo.
Quisiera saber si me puedes ayudar con esta sentencia:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Emp_Articulo]’) and OBJECTPROPERTY(id, N’IsForeignKey’) = 1)
ALTER TABLE [dbo].[Articulo] DROP CONSTRAINT Emp_Articulo
GO
Necesito pasarla de sql server 2000 a oracle 9i…. pero desconosco las funciones y sintaxis de object_id y OBJECTPROPERTY… asi como tmb el uso del EXIST en oracle.
Gracias!!
Javier Morales
Hola Anónimo,
entiendo que lo que quieres hacer es deshabilitar una constraint de foreign key sobre una columna únicamente si esta existe. ¿es correcto?
Pues algo tan fácil (y distinto) como ejecutar ésto:
begin
for x in (select c.constraint_name
from all_cons_columns cc, all_constraints c
where c.constraint_name=cc.constraint_name
and c.owner=’USUARIO ‘
and c.table_name=’DBO’
and cc.column_name=’EMP_ARTICULO’
and c.constraint_type=’R’)
begin
execute immediate ‘alter table DBO drop constraint ‘||x.constraint_name;
end;
end;
/
aunque, personalmente, si se trata de desactivar las constraints, ejecutaría simplemente:
alter table DBO.EMP_ARTICULO disable constraint nombre_constraint;
Un saludo,
Javier
Cristian.
Muy buen blog, me ha quedado completamente clara la diferencia entre el in y el exists.
Anónimo
Hola me parece excelente tu blog y quiero felicitarte, tengo una duda ojala puedas ayudarme tengo una consulta usando IN y quisiera saber cual seria su equivalente en exists
Select cliente,cuenta,deposito,fecha
from transacciones
where deposito>=10000 and fecha=’01/01/2007′ and
cuenta in (select cuenta from transacciones
where deposito>=10000 and fecha=’01/01/2007′
group by cuenta
having count(*)>4)
Ya lo eh intentado pero no me trae los mismo resultados, y la tabla no contiene valores nulos ojala puedas ayudarme, te felicito mucho y te mando saludos Atte. Marco
Anónimo
Respecto a tu conclusión:
«Generalmente, IN resulta más ventajoso cuando la ejecución de la subconsulta tiene poco coste respecto a la principal, y EXISTS cuando sucede al revés.»
¿Cabe esperar que los resultados varien dependiendo de la versión de oracle que se utilice?
Un saludo
Anónimo
Necesito migrar unos sp’s de SQL2000 a SQL2005 y tengo sentencias de forzado de indices como:
select count(*)
from cj_alarma (2),tm_cajero,tm_user_sector
where al_visto is null
Que al compilar en SQL2005 me dan error.
Cuál es la sintaxis en SQL2005 para forzar un indice???
Gracias
Javier Morales
Hola,
La select sobre cuentas:
Select cliente,cuenta,deposito,fecha
from transacciones
where deposito>=10000
and fecha=’01/01/2007′
and cuenta in (select cuenta
from transacciones
where deposito>=10000 and fecha=’01/01/2007′
group by cuenta
having count(*)>4)
se implementaría así con EXIXTS,
Select cliente,cuenta,deposito,fecha
from transacciones t1
where deposito>=10000
and fecha=’01/01/2007′
and EXISTS (select ‘existe’
from transacciones t2
where deposito>=10000 and fecha=’01/01/2007′
AND t1.cuenta=t2.cuenta
group by cuenta
having count(*)>4)
pero, ya que se trata de un cálculo sobre el total respecto a cada una de las filas, te recomiendo usar funciones analíticas para evitar que Oracle seleccione por cada fila de «cuentas» un select * from cuentas.
El código SQL sería así:
Select cliente, cuenta, deposito, fecha
from (select cliente, cuenta, deposito, fecha,
count(*) over (partition by cuenta) transporcuenta
from transacciones
where deposito>=10000
and fecha=’01/01/2007′)
where transporcuenta>4;
Para el siguiente anónimo que menciona la frase «¿Cabe esperar que los resultados varien dependiendo de la versión de oracle que se utilice?»
Pues sí, Oracle en cada versión puede tener un funcionamiento distinto del optimizador de costes (en principio, ir a mejor, claro). Coméntame tu caso y vemos cómo mejorarlo!
¿SQL2000 y SQL2005 son SQLServer? En ese caso no tengo ni idea. En Oracle, por ejemplo, una consulta «WHERE campo IS NULL» nunca utiliza un índice, ya que los valores nulos no se almacenan en los índices Oracle (salvo en unos índices muy concretos llamados índices bitmap, que se usan principalmente en entornos datawarehouse).
No tengo ninguna experiencia con SQLServer. Lo siento.
Espero haberos ayudado!
Un saludo!
Javier
Ozkar
HOla Javier, es muy bueno tu blog, te queria pregunar como realizo una consulta a una tabla q tiene mas de 3 llaves primarias.
Gracias por tu pronta ayuda.
Slds
Kelvin
Javier Morales
Hola Kelvin,
No entiendo tu pregunta: una tabla sólo puede tener una clave primaria. Anda, échale un vistazo a este post sobre constraints.
Gracias, un saludo,
Javier
Jaime
Hola Javier, felicitaciones me sirvio mucho tu explicacion sobre IN y Exist, esta muy claro.Serias tan amable de publicar sobre,funciones,procedimiento,paquetes y triggers.
Scooby
Ozkar dijo…
HOla Javier, es muy bueno tu blog, te queria pregunar como realizo una consulta a una tabla q tiene mas de 3 llaves primarias.
Gracias por tu pronta ayuda.
Slds
Kelvin
Aunque es un poco tarde para contestarle a kelvin, lo digo por la fecha "3/26/2008 10:59:00 PM" seguramente a lo que se refiere es que tiene una llave primaria compuesta de 3 campos o que 3 atributos son los que conforman la primary key.
Javier Morales
Hola Ozkar, Scooby,
select col1, col2, col3
from tabla
where campo1=valor1
and campo2=valor2
and campo3=valor3;
😛 la primary key es sólo una condición que cumplen los valores de una o más columnas. En este caso, que no hay elementos duplicados ni valores nulos.
Anónimo
Wee! Javier esta excelente la pagina, te pido por favor desde tu optica me hables de los explain plans, ya que usas un lenguaje perfecto para entender, Saludos Gige desde Vzla!
Javier Morales
Hola Gige,
Los planes de ejecución son la herramienta clave para ver qué estrategia utilizará Oracle para resolver una consulta.
Es vital para optimizar SQL.
Visita http://optimizacionsql.blogspot.com donde verás ejemplos de cómo usarlo para "diagnosticar" qué diablos le está pasando a una query que, inexplicablemente, tarda minutos y horas estando formalmente bien escrita!
Un saludo, y gracias!
Javier
ale vazmo
Hola, como puedo hacer un drop constraint chequeando si xiste el constraint
Farid Gámez
Excelente, bien explicado
Anónimo
Hola, como puedo hacer un procedimiento que sustituya al natural join, que riciba 2 tablas y las una. Con pl/sql… De antemano, gracias
Javier Morales
Hola Anónimo:
No deberías implementar ese procedimiento, porque no te aporta nada (más que lentitud e ineficiencia).
Ese procedimiento debería retornar un REF CURSOR, y tendrías que recorrerlo igualmente… definir el cursor y darle el valor de una query dinámica (con las dos tablas que pasases como entrada al procedimiento) y nada te evita hacer el natural join entre ellas. Luego tendrías que recorrerlo…
¿Por qué quieres hacer manualmente con PL/SQL lo que Oracle hace de forma natural con SQL?
No lo hagas, no ganarás nada y perderás (tiempo y eficiencia).
Un saludo,
Javier
Anónimo
Hola buen dia,
espero me puedan apoyar con mi caso es que tengo una consulta en el cual utilizo el exists
pero son alrededor de 190,000 registros y se tarda 2 horas en hacer un update me podria apoyar a ver de que otra manera puedo implementar el update:
UPDATE ra_interface_distributions_all rida
SET rida.interface_line_attribute6 = NULL,
rida.interface_line_attribute8 = 'VALIDADO'
WHERE rida.interface_line_attribute6 = ps_batch_id
AND EXISTS
(SELECT 'X'
FROM ra_interface_lines_all rila
WHERE rila.interface_line_attribute6 = ps_batch_id
AND NVL (rida.interface_line_context, 'XX') =
NVL (rila.interface_line_context, 'XX')
AND NVL (rida.interface_line_attribute1, 'XX') =
NVL (rila.interface_line_attribute1, 'XX')
AND NVL (rida.interface_line_attribute2, 'XX') =
NVL (rila.interface_line_attribute2, 'XX')
AND NVL (rida.interface_line_attribute3, 'XX') =
NVL (rila.interface_line_attribute3, 'XX')
AND NVL (rida.interface_line_attribute4, 'XX') =
NVL (rila.interface_line_attribute4, 'XX')
AND NVL (rida.interface_line_attribute5, 'XX') =
NVL (rila.interface_line_attribute5, 'XX')
AND NVL (rida.interface_line_attribute6, 'XX') =
NVL (rila.interface_line_attribute6, 'XX')
AND NVL (rida.interface_line_attribute7, 'XX') =
NVL (rila.interface_line_attribute7, 'XX'));