The execution plan of a query is the sequence of operations that Oracle performs to execute a given statement. It is nothing but a tree which contains the order of steps and relationship between them. The Explain Plan statement displays the execution plan chosen by the Oracle Optimizer.
The Explain Plan generates an execution plan and saves it in the PLAN_TABLE. To create a PLAN_TABLE in your schema execute the ORACLE_HOME\rdbms\admin\utlxplan.sql script.
The following are the basic rules of the execution plan tree:
1. An execution plan will contain a root, which has no parent.
2. A parent can have one or more children.
3. A child has only one parent.
Let us create a dept table in our HR schema, add a foreign key to the emp table pointing to the dept table, and generate an execution plan for an SQL statement joining these two tables. This is done as follows:
-- Create DEPT table SQL> CREATE TABLE dept (dept_no NUMBER(3) PRIMARY KEY, dept_name VARCHAR2(30)); -- Alter EMP table to...