17,382
社区成员




SQL> select * from emp e1 left join t1 e2 on e1.empno = e2.empno and e1.deptno = 20;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2147177880
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2436 | 45 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 2436 | 45 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 87 | 3 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
|* 5 | TABLE ACCESS FULL| T1 | 1 | 87 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E1"."DEPTNO"=20)
5 - filter("E1"."EMPNO"="E2"."EMPNO")
Note
-----
- dynamic sampling used for this statement
SQL> select * from emp e1 left join t1 e2 on e1.empno = e2.empno and e2.deptno = 20;
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4277803294
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2436 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 2436 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T1 | 5 | 435 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."EMPNO"="E2"."EMPNO"(+))
3 - filter("E2"."DEPTNO"(+)=20)
Note
-----
- dynamic sampling used for this statement
| 3 | VIEW | | 1 | 87 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e2.deptno = 20;
Execution Plan
----------------------------------------------------------
Plan hash value: 2341341676
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2436 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 14 | 2436 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 14 | 1218 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 5 | 435 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E1"."EMPNO"="E2"."EMPNO"(+))
3 - filter("E2"."DEPTNO"(+)=20)
Note
-----
- dynamic sampling used for this statement
SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
Execution Plan
----------------------------------------------------------
Plan hash value: 3186051241
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 2436 | 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 14 | 2436 | 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | EMP | 14 | 1218 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 87 | 2 (0)| 00:00:01 |
|* 4 | FILTER | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("E1"."DEPTNO"=20)
6 - access("E1"."EMPNO"="E2"."EMPNO")
Note
-----
- dynamic sampling used for this statement
SQL> select * from a;
ID NAME
---------- ----------
1 aaa
2 bbb
3 ccc
SQL> select * from b;
ID NAME
---------- ----------
1 b_aaa
2 b_bbb
3 b_ccc
SQL> set autotrace traceonly
SQL> select * from a left join b on a.id=b.id and a.id = 1;
执行计划
----------------------------------------------------------
Plan hash value: 2608930719
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 120 | 12 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 120 | 12 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 3 | 60 | 3 (0)| 00:00:01 | a表后
| 3 | VIEW | | 1 | 20 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| B | 1 | 20 | 3 (0)| 00:00:01 | b表先运算----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("A"."ID"=1 AND "B"."ID"=1)
SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20 7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from emp e1 left join emp e2 on e1.empno = e2.empno and e2.deptno = 20;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20 7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20 7902 FORD ANALYST 7566 03-DEC-81 3000 20
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7839 KING PRESIDENT 17-NOV-81 5000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7900 JAMES CLERK 7698 03-DEC-81 950 30
14 rows selected.
SQL>
select * from emp e1 left join emp e2 on e1.empno = e2.empno and e1.deptno = 20;
select e1.empno,e2.empno, e1.deptno
from scott.emp e1
left join scott.emp e2 on e1.empno = e2.empno
and e1.deptno = 20;