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