初识Oracle执行计划

初识Oracle执行计划，表连接的实现方法还有排序合并和嵌套循环方式,在后面做介绍.Predicate Information (identified by operati

1,先建立测试表和数据,
CREATE   TABLE EMP AS
  SELECT LEVEL   EMPL_ID,
             (MOD (ROWNUM, 20)+1) DEPT_ID,
             SUBSTR(DBMS_RANDOM.STRING ('X', DBMS_RANDOM.VALUE (20, 50)),0,10) EMPNAME,
             TRUNC (DBMS_RANDOM.VALUE (1000, 500000), 2)  SALARY,
             DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F')   GENDER,
             TO_DATE (   ROUND (DBMS_RANDOM.VALUE (1, 28))
                      || '-'
                      || ROUND (DBMS_RANDOM.VALUE (1, 12))
                      || '-'
                      || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                      'DD-MM-YYYY')    DOB
        FROM DUAL
  CONNECT BY LEVEL CREATE    TABLE dept AS
  SELECT LEVEL   dept_id,
             SUBSTR(DBMS_RANDOM.STRING ('X', DBMS_RANDOM.VALUE (20, 50)),0,10) manager,
             DECODE (ROUND (DBMS_RANDOM.VALUE (1, 2)), 1, 'M', 2, 'F')   gender,
             TO_DATE (   ROUND (DBMS_RANDOM.VALUE (1, 28))
                      || '-'
                      || ROUND (DBMS_RANDOM.VALUE (1, 12))
                      || '-'
                      || ROUND (DBMS_RANDOM.VALUE (1900, 2010)),
                      'DD-MM-YYYY')    estbdate
        FROM DUAL
  CONNECT BY LEVEL
2,没有索引,第一次执行SQL
SQL> SET AUTOTRACE ON;
SQL> SELECT EMPL_ID,EMPNAME,DEPT.DEPT_ID,MANAGER FROM EMP,DEPT WHERE EMP.DEPT_ID=DEPT.DEPT_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 53000 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1000 | 53000 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    30 |   600 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |  1000 | 33000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."DEPT_ID"="DEPT"."DEPT_ID")
Note
-----
   - dynamic sampling used for this statement
Statistics
----------------------------------------------------------
        504  recursive calls
          0  db block gets
        151  consistent gets
         19  physical reads
          0  redo size
      39139  bytes sent via SQL*Net to client
       1107  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
       1000  rows processed

对这个计划的一些解释:
Plan hash value: 615168685,根据执行的SQL文本得到一个hash值,表示放在共享池中的地址,如果同样的SQL再次执行,直接用这个执行计划.
|*  1 |  HASH JOIN         |      |  1000 | 53000 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |    30 |   600 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |  1000 | 33000 |     4   (0)| 00:00:01 |
两个表做连接的一种方式,这里是hash join,这里是是先将dept表的所有数据做扫描,对每一条数据,根据DEPT 的dept_id算出一个hash值,放在该hash值代表的内存join区域,然后扫描emp表,对每一条emp表的数据的dept_id计算hash值,然后按照hash值放到join区域,形成数据的连接.

，