初识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 < 1001;
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 < 31;

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