Oracle PL/SQL/SQL Plus/explain plan

Execution plan for the specified statement_id

   <source lang="sql">
   

SQL> SQL> UNDEFINE v_statement_id; SQL> SQL> SELECT id ||

 2    DECODE(id, 0, "", LPAD(" ", 2*(level - 1))) || " " ||
 3    operation || " " ||
 4    options || " " ||
 5    object_name || " " ||
 6    object_type || " " ||
 7    DECODE(cost, NULL, "", "Cost = " || position)
 8  AS execution_plan
 9  FROM plan_table
10  CONNECT BY PRIOR id = parent_id
11  AND statement_id = "&&v_statement_id"
12  START WITH id = 0
13  AND statement_id = "&v_statement_id";

Enter value for v_statement_id: old 11: AND statement_id = "&&v_statement_id" new 11: AND statement_id = "" old 13: AND statement_id = "&v_statement_id" new 13: AND statement_id = "" no rows selected SQL>

 </source>
   
  

explain plan for query

   <source lang="sql">
  

SQL> SQL> create table emp(

 2           emp_id            integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,shortZipCode           varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");

1 row created. SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values

 2                      (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");

1 row created.

SQL> explain plan for

 2  select *
 3  from emp
 4  where lastname = "Young";

Explained. SQL> select operation

 2  from plan_table;

SELECT STATEMENT TABLE ACCESS SELECT STATEMENT TABLE ACCESS SELECT STATEMENT TABLE ACCESS 6 rows selected. SQL> explain plan for

 2  select *
 3  from emp
 4  where firstname="Joe";

Explained. SQL> select operation

 2  from plan_table;

SELECT STATEMENT TABLE ACCESS SELECT STATEMENT TABLE ACCESS SELECT STATEMENT TABLE ACCESS SELECT STATEMENT TABLE ACCESS 8 rows selected. SQL> SQL> SQL> set autotrace off SQL> SQL> drop table emp; Table dropped. SQL> SQL>

 </source>
   
  

explain plan for select statement

   <source lang="sql">
  

SQL> create table t as select mod(object_id,10) id, a.* from all_objects a; SQL> SQL> analyze table t compute statistics

 2  for table
 3  for columns id;

SQL> SQL> SQL> SQL> alter session set optimizer_max_permutations=80000; SQL> SQL> explain plan for

 2  select count(*)
 3    from t t1, t t2, t t3, t t4, t t5, t t6
 4   where t1.id = t2.id
 5     and t1.id = t3.id
 6     and t1.id = t4.id
 7     and t1.id = t5.id
 8     and t1.id = t6.id;

SQL> SQL> set autotrace off SQL> SQL> SQL> drop table t;

 </source>
   
  

Explain sql statement

   <source lang="sql">
  

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;

 </source>