sábado, 4 de mayo de 2013

NOT EXISTS Contra NOT IN

Este ejemplo, probado en Oracle 10, muestra que NOT EXISTS pueda ser más eficaz que NOT IN. Para empezar, hay que crear una tabla emp para los empleados desde E1 hasta E9999:

SQL> alter session set nls_language = 'SPANISH'
  2  /

Sesion modificada.

SQL> create table emp
  2 (empno  varchar2(5),
  3  deptno varchar2(5))
  4 /

Tabla creada.

SQL>


Luego, necesitamos una tabla dept para los departamentos desde D1 hasta D9999:

SQL> create table dept
  2  (deptno varchar2(5))
  3  /

Tabla creada.

SQL>

El SQL siguiente pone el empleado E1 en el departamento D1 y el empleado E2 en el departamento D2 etc:

SQL> declare
  2  begin
  3  for ctr in 1..9999 loop
  4  insert into emp values ('E'||ctr,'D'||ctr);
  5  insert into dept values ('D'||ctr);
  6  end loop;
  7  end;
  8  /

Procedimiento PL/SQL terminado correctamente.

SQL>

Ahora creamos un índice y una estadística para la tabla emp:

SQL> create index deptno_index on emp(deptno)
  2  /

Indice creado.

SQL> exec dbms_stats.gather_table_stats -
> (ownname => 'SYSTEM', tabname => 'EMP');

Procedimiento PL/SQL terminado correctamente.

SQL>

Si borramos el empleado E5000, el departamento D5000 será vacío:

SQL> delete from emp
  2  where empno = 'E5000';

1 fila suprimida.

SQL>


Por fin buscaremos el departamento vacío con dos consultas diferentes:

SQL> set timing on
SQL> alter session
  2  set timed_statistics = true
  3  /

Sesion modificada.

Transcurrido: 00:00:00.09

SQL> alter session
  2  set sql_trace = true
  3  /

Sesion modificada.

Transcurrido: 00:00:00.05

SQL>


Una consulta que emplea NOT EXISTS toma mucho menos de un segundo:

SQL> select deptno from dept
  2  where not exists
  3  (select deptno from emp
  4   where emp.deptno = dept.deptno)
  5  /

DEPTN
-----
D5000

Transcurrido: 00:00:00.12

SQL>


Pero una consulta que emplea NOT IN toma siete segundos:

SQL> select deptno from dept
  2  where deptno not in
  3  (select deptno from emp)
  4  /

DEPTN
-----
D5000

Transcurrido: 00:00:07.26

SQL> alter session
  2  set sql_trace = false
  3  /

Sesion modificada.

Transcurrido: 00:00:00.05

SQL>


Tras utilizar tkprof para examinar la fila creada por sql_trace, se puede ver la diferencia entre las dos consultas. La consulta con NOT EXISTS emplea el índice pero la consulta con NOT IN no lo emplea. Es necesario hacer click en las imágenes una vez o dos veces para verlas en sus tamaños originales:



No hay comentarios:

Publicar un comentario