Oracle Club

> rem --- УДАЛИМ СТАТИСТИКУ ---
SQL> analyze table DEPT delete statistics;
SQL> analyze table EMP delete statistics;
SQL> select count(*) from emp;

COUNT(*)
---------
   229376

> rem --- ЗАПОЛНЯЕМ БУФЕРА БД ---

SQL> select * from DEPT D 
2    where not exists(select 1 from EMP X
3                     where not exists(select 1 from EMP Y
4                                      where Y.JOB = X.JOB
5                                        and Y.DEPTNO = D.DEPTNO));

   DEPTNO DNAME          LOC
--------- -------------- -------------
       30 SALES          CHICAGO

real: 63440

> rem --- ДЕЙТ ---

SQL> select * from DEPT D 
2    where not exists(select 1 from EMP X
3                     where not exists(select 1 from EMP Y
4                                      where Y.JOB = X.JOB
5                                        and Y.DEPTNO = D.DEPTNO));

DEPTNO    DNAME          LOC
--------- -------------- -------------
       30 SALES          CHICAGO

real: 63330

> rem --- ФИЛИМОНОВ ---

SQL> select * from DEPT where deptno in 
        (select deptno from emp
         group by deptno 
         having count(distinct job)
            =(select count(distinct job) from emp));

DEPTNO    DNAME          LOC
--------- -------------- -------------
       30 SALES          CHICAGO

real: 14880

> rem --- ПЕЧКАРЕВ ---

SQL> select * from DEPT D
     where not exists (
        select JOB from EMP
        minus
        select JOB from EMP E where E.DEPTNO=D.DEPTNO
        );

DEPTNO    DNAME          LOC
--------- -------------- -------------
       30 SALES          CHICAGO

real: 50750

> rem --- ФИЛИМОНОВ - ЮРИНСКИЙ ---

SQL> select * from DEPT
     where exists 
        (select 1 from emp where DEPT.DEPTNO = EMP.DEPTNO
         group by deptno having count(distinct job)
            =(select count(distinct job) from emp) );

DEPTNO    DNAME          LOC
--------- -------------- -------------
       30 SALES          CHICAGO

real: 33060

> rem --- ЮРИНСКИЙ ---

SQL> select * from DEPT, 
        (select COUNT(distinct JOB) as CNTJOB
            from (select distinct JOB,DEPTNO from emp) DEJOB) ALLJOB
     where (DEPTNO,CNTJOB) IN (
        select deptno,COUNT(JOB)
        from (select distinct JOB,DEPTNO from emp) DEJOB
        group by DEPTNO);

DEPTNO    DNAME          LOC           CNTJOB 
--------- -------------- ------------- ---------
       30 SALES          CHICAGO               5

real: 13350

SQL> rem *****************************************
SQL> rem *** ПОВТОРИМ ТО ЖЕ, СОБРАВ СТАТИСТИКУ ***
SQL> rem ***    (ПРЕДЛОЖИЛ ДМИТРИЙ БЕЗРУКОВ)   ***
SQL> rem *****************************************

> rem --- СБОР СТАТИСТИКИ ---

SQL> analyze table DEPT compute statistics;
SQL> analyze table EMP compute statistics;

... ПОВТОРЕНИЕ РЕЗУЛЬТАТОВ ЗАБЕГА (ТО ЖЕ САМОЕ ВРЕМЯ УЧАСТНИКОВ) ...

Желающим проверить все это на своем Oracle7 прилагаю файл FORALL.ZIP

[Oracle Club] [МАНЁВРЫ]