Oracle优化——外联结的执行计划

外联结不要使用Oracle自有的方式在where条件中加（+），而要用ANSI的方式（… OUTER JOIN）。Oracle的方式不支持全外联结，而且存在诡异的BUG。左外连接与右外连接时，Oracle会把返回全部记录的表做为驱动表，哪怕这样是违背了ordered提示。可以应用所有的联结方法，效率也与内联结相仿。HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e right outer join departments d on ( e.department_id = d.department_id and employee_id = 200 ) ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    Administration                                               Marketing                                               Purchasing                                               Human Resources                                               Shipping                                               IT                                               Public Relations                                               Sales                                               Executive                                               Finance                                               Accounting                                               Treasury                                               Corporate Tax                                               Control And Credit                                               Shareholder Services                                               Benefits                                               Manufacturing                                               Construction                                               Contracting                                               Operations                                               IT Support                                               NOC                                               IT Helpdesk                                               Government Sales                                               Retail Sales                                               Recruiting                                               Payroll27 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 610636616-----------------------------------------------------------------------------------------------| Id  | Operation                     | Name          | Rows  | Bytes | Cost (%CPU)| Time     |-----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT              |               |    27 |  1026 |     4  (25)| 00:00:01 ||   1 |  MERGE JOIN OUTER             |               |    27 |  1026 |     4  (25)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS   |    27 |   432 |     2   (0)| 00:00:01 ||   3 |    INDEX FULL SCAN            | DEPT_ID_PK    |    27 |       |     1   (0)| 00:00:01 ||*  4 |   SORT JOIN                   |               |     1 |    22 |     2  (50)| 00:00:01 ||   5 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    22 |     1   (0)| 00:00:01 ||*  6 |     INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   4 - access("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")       filter("E"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")   6 - access("EMPLOYEE_ID"(+)=200)Statistics----------------------------------------------------------          0  recursive calls          0  db block gets          8  consistent gets          0  physical reads          0  redo size       1364  bytes sent via SQL*Net to client        534  bytes received via SQL*Net from client          3  SQL*Net roundtrips to/from client          1  sorts (memory)          0  sorts (disk)         27  rows processedHR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e join departments d on ( e.department_id = d.department_id and employee_id = 200 ) ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    AdministrationExecution Plan----------------------------------------------------------Plan hash value: 2782876085----------------------------------------------------------------------------------------------| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |----------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT             |               |     1 |    38 |     2   (0)| 00:00:01 ||   1 |  NESTED LOOPS                |               |     1 |    38 |     2   (0)| 00:00:01 ||   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    22 |     1   (0)| 00:00:01 ||*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 ||   4 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS   |     1 |    16 |     1   (0)| 00:00:01 ||*  5 |    INDEX UNIQUE SCAN         | DEPT_ID_PK    |     1 |       |     0   (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   3 - access("EMPLOYEE_ID"=200)   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads          0  redo size        704  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)1rows processed全外联结则是一种特殊的联结方式HR@ prod> select /*+ ordered */ first_name , last_name , department_name   2  from employees e full outer join departments d on ( e.department_id = d.department_id )   3  where employee_id = 200 ;FIRST_NAME           LAST_NAME                 DEPARTMENT_NAME-------------------- ------------------------- ------------------------------Jennifer             Whalen                    AdministrationExecution Plan----------------------------------------------------------Plan hash value: 2631508678-------------------------------------------------------------------------------------| Id  | Operation             | Name        | Rows  | Bytes | Cost (%CPU)| Time     |-------------------------------------------------------------------------------------|   0 | SELECT STATEMENT      |             |   122 |  6832 |     7  (15)| 00:00:01 ||*  1 |  VIEW                 | VW_FOJ_0    |   122 |  6832 |     7  (15)| 00:00:01 ||*  2 |   HASH JOIN FULL OUTER|             |   122 |  4636 |     7  (15)| 00:00:01 ||   3 |    TABLE ACCESS FULL  | DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 ||   4 |    TABLE ACCESS FULL  | EMPLOYEES   |   107 |  2354 |     3   (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - filter("EMPLOYEE_ID"=200)   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")Statistics----------------------------------------------------------          1  recursive calls          0  db block gets         15  consistent gets          0  physical reads          0  redo size        704  bytes sent via SQL*Net to client        523  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed