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