Explain sql statement : EXPLAIN PLAN « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. EXPLAIN PLAN

SQL>
SQL> create table emp (
  2      EmpNo          NUMBER(10) primary key,
  3      Name           VARCHAR2(40),
  4      DeptNo         NUMBER(2),
  5      Salary         NUMBER(7,2),
  6      Birth_Date     DATE,
  7      Soc_Sec_Num    VARCHAR2(9),
  8      State_Code     CHAR(2)
  9  );
SQL>
SQL> explain plan
  2  set Statement_Id = 'TEST'
  3  for
  4  select * from emp;
SQL>
SQL> select LPAD(' ',2*Level)||Operation||' '||Options||' '||Object_Name Q_Plan
  2  from PLAN_TABLE
  3  where Statement_Id = 'TEST'
  4  connect by prior ID = Parent_ID and Statement_ID = 'TEST'
  5  start with ID=0;
                                                              

Q_PLAN
------------------------------------------------------------------------------------------------------------------------
  SELECT STATEMENT
    TABLE ACCESS FULL EMP
    TABLE ACCESS FULL EMP
  SELECT STATEMENT
    TABLE ACCESS FULL EMP
    TABLE ACCESS FULL EMP
                    
SQL>
SQL> set autotrace off  explain
SQL>
SQL>
SQL> drop table emp;