初识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区域,形成数据的连接.