SQL> set lines 200
SQL> set pages 200
SQL> alter session set statistics_level = all;
NESTLOOP:嵌套循环
SQL> select e.ename,e.job,d.dname from emp e, dept d where
2 e.deptno=d.deptno and e.sal < 2000;
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK RESEARCH
ALLEN SALESMAN SALES
WARD SALESMAN SALES
MARTIN SALESMAN SALES
TURNER SALESMAN SALES
ADAMS CLERK RESEARCH
JAMES CLERK SALES
MILLER CLERK ACCOUNTING
已选择8行。
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
SQL_ID anvuxahhpxp8g, child number 0
-------------------------------------
select e.ename,e.job,d.dname from emp e, dept d where e.deptno=d.deptno and e.sal < 2000
Plan hash value: 351108634
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS | | 1 | 4 | 8 |00:00:00.01 | 18 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 4 | 8 |00:00:00.01 | 8 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 8 | 1 | 8 |00:00:00.01 | 10 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 8 | 1 | 8 |00:00:00.01 | 2 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("E"."SAL"<2000)
4 - access("E"."DEPTNO"="D"."DEPTNO")
已选择21行。