????15???????????滻EXISTS

???????????￡??????????EXISTS????Ч

????-->??Ч??

SELECT *
FROM   employees e
WHERE  EXISTS
          (SELECT 1
           FROM   departments
           WHERE  department_id = e.department_id AND department_name = 'IT');

????-->??Ч??

????-->???????д??SQLplus?±??????д??????????????????Toad???????????

SELECT *
FROM   employees e INNER JOIN departments d ON d.department_id = e.department_id
WHERE  d.department_name = 'IT';

????16????EXISTS?滻DISTINCT

??????????????????????????粿??????????????????select ????????distinct???????exists???滻

????-->??Ч??

SELECT DISTINCT e.department_id?? d.department_name
FROM   departments d INNER JOIN employees e ON d.department_id = e.department_id;

????-->??Ч??

SELECT d.department_id??department_name
from departments d
WHERE  EXISTS
          (SELECT 1
           FROM   employees e
           WHERE  d.department_id=e.department_id);

????EXISTS ??????????????RDBMS??????齫?????????????????????????????

????-->???????д??SQLplus?±??????д??????????????????Toad???????????

????17????? UNION ALL ?滻 UNION??????п???????

??????SQL??????UNION???????????????????????????????UNION-ALL??????????? ?????????????????????

?????????UNION ALL???UNION?? ??????????????? Ч????????????

???????

????UNION ALL????????е?????????UNION?????????????????????????.??????????????????????????????????????????????

????????Ч??SQL???

????-->?????????????????????v$sqlarea?л???????????????buffer_gets????SQL???

SELECT executions
     ?? disk_reads
     ?? buffer_gets
     ?? ROUND( ( buffer_gets
               - disk_reads )
             / buffer_gets?? 2 )
          hit_ratio
     ?? ROUND( disk_reads / executions?? 2 ) reads_per_run
     ?? sql_text
FROM   v$sqlarea
WHERE      executions > 0
       AND buffer_gets > 0
       AND ( buffer_gets
            - disk_reads )
           / buffer_gets < 0.80
ORDER BY 4 DESC;

????18?????????????ú????????????????? recursive calls

????????????????????????????

??????????????????????????????????????????????????????????????????????????Oracle??????????????????B?????????????????DML??????????????????????????????????????????????????????????????????????????????Ч??

?????????DQL??DML??????SQL???????????????????????????????????????????м?????????????????????optimizer_index_cost_adj?趨?????????????衣????????????????????????????Ч??????????????????primary key???????????

??????????ЩLONG??LONG RAW?????????????????????????е???.?????????????????????????Ч.??????????????????С????????????????????Ч???

?????????????????????Ч????????????????????????洢????????????.?????????д???DML???????????????DML????????????????????????ζ??????????INSERT??DELETE??UPDATE????????4??5 ?ε????I/O?????????????????洢??????????Щ????????????????????????????????

????DML??????????????????????????????????????????????б?????

????1??????????????е????

????where ????е?ν????????????????????????е????????????Ч

????-->??Ч??

SELECT employee_id?? first_name
FROM   employees
WHERE  employee_id + 10 > 150;        -->????????????????????????Ч?????????跽?

????-->??Ч??

SELECT employee_id?? first_name
FROM   employees
WHERE  employee_id > 160;    -->????????Χ??跽?

????????????

??????????????????SQL?е?MIN??MAX????

hr@CNMMBO> SELECT MAX( employee_id ) max_id
  2  FROM   employees
  3  WHERE  employee_id
  4         + 10 > 150;

1 row selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1481384439
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |     4 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |               |     1 |     4 |            |          |
|   2 |   FIRST ROW                 |               |     5 |    20 |     1   (0)| 00:00:01 |
|*  3 |    INDEX FULL SCAN (MIN/MAX)| EMP_EMP_ID_PK |     5 |    20 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------