2009年3月11日星期三

different execution plan for same output

------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  |
------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |    10 |
|   1 |  NESTED LOOPS SEMI           |             |    10 |
|   2 |   TABLE ACCESS FULL          | EMPLOYEES   |   107 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |
|   4 |    INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |
------------------------------------------------------------

select * from employees e where exists (
  select * from departments d
    where d.department_id=e.department_id
       and d.department_name='Sales');


-----------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |    10 |
|   2 |   NESTED LOOPS              |                   |    10 |
|   3 |    TABLE ACCESS FULL        | DEPARTMENTS       |     1 |
|   4 |    INDEX RANGE SCAN         | EMP_DEPARTMENT_IX |    10 |
-----------------------------------------------------------------

select * from employees join departments using (department_id)
  where department_name='Sales';

没有评论: