从两表连接看Oracle sql优化器的效果

时间：2020-12-20 16:32:49
select emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id

-- 不加hint

SQL> select emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id ;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 2666916627

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|   2 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|  3601   (1)| 00:00:44 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

select /*+ leading(emp,dept) use_nl(emp) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

SQL> select /*+ leading(emp,dept) use_nl(emp) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 960397288

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|       |  8558   (1)| 00:01:43 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|    99M|  8558   (1)| 00:01:43 |

|   2 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|       |  3601   (1)| 00:00:44 |

|   3 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

select /*+ leading(dept,emp) use_nldept) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

SQL> select /*+ leading(dept,emp) use_nldept) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 2666916627

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|   2 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|  3601   (1)| 00:00:44 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

select /*+ ordered */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

SQL> select /*+ ordered */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 960397288

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|       |  8558   (1)| 00:01:43 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|    99M|  8558   (1)| 00:01:43 |

|   2 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|       |  3601   (1)| 00:00:44 |

|   3 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |       |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

select /*+ use_hash(dept,emp) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

SQL> select /*+ use_hash(dept,emp) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 2666916627

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|   2 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|  3601   (1)| 00:00:44 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

select /*+ use_hash(emp,dept) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

SQL> select /*+ use_hash(emp,dept) */emp.*,dept.* from tb_emp03 emp,tb_dept03 dept where emp.deptno=dept.id;

已用时间:  00: 00: 00.00

执行计划

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

Plan hash value: 2666916627

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

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

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

|   0 | SELECT STATEMENT   |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|*  1 |  HASH JOIN         |           |  1818K|    97M|  3619   (2)| 00:00:44 |

|   2 |   TABLE ACCESS FULL| TB_DEPT03 |    10 |   160 |     3   (0)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| TB_EMP03  |  2000K|    76M|  3601   (1)| 00:00:44 |

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

Predicate Information (identified by operation id):

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

   1 - access("EMP"."DEPTNO"="DEPT"."ID")

相关文章