过程化到基于集合的思想

 案例：produce a list of all employees who spent the same number of years in each job they held within the company during their employment。

hr@ORCL> select distinct employee_id 
  2  from job_history j1 
  3  where not exists 
  4  (select null 
  5    from job_history j2    where j2.employee_id = j1.employee_id    and round(months_between(j2.start_date,j2.end_date)/12,0) <>      round(months_between(j1.start_date,j1.end_date)/12,0) ) 
  6  /

EMPLOYEE_ID
-----------
        102
        114
        122
        176
        201


Execution Plan
----------------------------------------------------------
Plan hash value: 2149301360

---------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |     7 |   280 |     7  (29)| 00:00:01 |
|   1 |  SORT UNIQUE NOSORT           |                   |     7 |   280 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN ANTI             |                   |    10 |   400 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| JOB_HISTORY       |    10 |   200 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | JHIST_EMPLOYEE_IX |    10 |       |     1   (0)| 00:00:01 |
|*  5 |    FILTER                     |                   |       |       |            |          |
|*  6 |     SORT JOIN                 |                   |    10 |   200 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL        | JOB_HISTORY       |    10 |   200 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("J2"."START_DATE"),INTERNAL_FUNCTION("
              J2"."END_DATE"))/12,0)<>ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("J1"."START_DATE"),INTE
RNAL_

              FUNCTION("J1"."END_DATE"))/12,0))
   6 - access("J2"."EMPLOYEE_ID"="J1"."EMPLOYEE_ID")
       filter("J2"."EMPLOYEE_ID"="J1"."EMPLOYEE_ID")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        612  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          5  rows processed

使用基于集的思想

hr@ORCL> select employee_id 
  2  from job_history 
  3  group by employee_id 
  4  having min(round(months_between(start_date,end_date)/12,0)) = 
  5  max(round(months_between(start_date,end_date)/12,0)) 
  6  /

EMPLOYEE_ID
-----------
        102
        114
        122
        176
        201


Execution Plan
----------------------------------------------------------
Plan hash value: 2858804740

----------------------------------------------------------------------------------------------------
-----

| Id  | Operation                     | Name                    | Rows  | Bytes | Cost (%CPU)| Time
    |

----------------------------------------------------------------------------------------------------
-----

|   0 | SELECT STATEMENT              |                         |     1 |    20 |     2   (0)| 00:00
:01 |

|*  1 |  FILTER                       |                         |       |       |            |
    |

|   2 |   SORT GROUP BY NOSORT        |                         |     1 |    20 |     2   (0)| 00:00
:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| JOB_HISTORY             |    10 |   200 |     2   (0)| 00:00
:01 |

|   4 |     INDEX FULL SCAN           | JHIST_EMP_ID_ST_DATE_PK |    10 |       |     1   (0)| 00:00
:01 |

----------------------------------------------------------------------------------------------------
-----


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(MIN(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("START_DATE"),INTERNAL_FUNCTION("END_DAT
              E"))/12,0))=MAX(ROUND(MONTHS_BETWEEN(INTERNAL_FUNCTION("START_DATE"),INTERNAL_FUNCTION
("END_DATE"

              ))/12,0)))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
        612  bytes sent via SQL*Net to client
        519  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

我们可以看到基于集合的思想，逻辑读明显减少