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区域,形成数据的连接.
当前位置: 数据库>oracle
初识Oracle执行计划
来源: 互联网 发布时间:2017-04-21
本文导语: 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,...
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。