First knowledge of Oracle execution plan

1. Create a test table and data first,
CREATE TABLE EMP
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,500 000), 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,201 0 )),
'Dd-MM-YYYY ') DOB
FROM DUAL
Connect by level <1001;
Create table dept
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,201 0 )),
'Dd-MM-YYYY ') estbdate
FROM DUAL
Connect by level <31;

2. No index. The first SQL statement is executed.
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 cballs
0 db block gets
151 consistent gets
19 physical reads
0 redo size
39139 bytes sent via SQL * Net to client
1107 bytes encoded ed via SQL * Net from client
68 SQL * Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1000 rows processed

Some explanations of this plan:
Plan hash value: 615168685. A hash value is obtained based on the executed SQL text, indicating the address placed in the Shared Pool. If the same SQL statement is executed again, use this execution Plan directly.
| * 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 |
One method for connecting two tables is hash join, where all data in the dept table is scanned first, and each data entry is, calculate a hash value based on dept_id of DEPT and place it in the memory join region represented by the hash value. Then, scan the emp table and calculate the hash value for the dept_id of each emp table, then, place the data in the join area according to the hash value to form a data connection.

1 2 Next Page